Instead of using SQL why dont you use the filter method for geting specific records from the database. i use filter method it is best and i do not have to write such long SQl codes for geting the records.
Ritesh --- Roy Forkner <[EMAIL PROTECTED]> wrote: > Ok, as a last effort try this: > > Copy the select statement to sql query and run it, > after it returns a > record. Copy the query and , in your code after the > line that says > strsql = > > Put strsql = and paste from the sql query design. Of > course put " around > the paste. > > > > Change your code to : > > > > If Not rst.EOF Then > > Rst.movefirst > > GetLateFeesCharged = rst!LateFeesCharged > > Else > > GetLateFeesCharged = 0 > > End If > > > > Place a breakpoint on the if statement and step > through the code and see > what happens. > > I am leaving but will look forward to what your > results are. > > Roy > > > > ________________________________ > > From: [email protected] > [mailto:[EMAIL PROTECTED] On Behalf > Of Bryant > Sent: Monday, July 18, 2005 4:57 PM > To: [email protected] > Subject: Re: [Access VBA Central] Different results > from DAO and ADO > recordsets > > > > With changing spacing as directed, the SQL string is > now: > > SELECT LateFeesCharged FROM > qselTransactions_LateFeesCharged WHERE > CompanyID = 'GMDC' AND TenantID = 'BABET01' > > This does not return rows in ADO, but returns > correct data in DAO and > in SQL query design mode. > > Adding a semicolon (per someone's suggestion > earlier) does not work, > either. > > Bryant > > --- In [email protected], "Roy > Forkner" <[EMAIL PROTECTED]> > wrote: > > In your ado, notice that there is no space between > =' > > > > > > > > Copy the following and have strSQL = (the > following) and run and see > if > > it works. If so, you will know that the format of > the sql statement > was > > in error. Or you can copy what the result of > strSQL and put it in the > > sql design and see have it return a record. Then > copy from the SQL > > design and (in your code after the current strSQL > = put this in > > > > > > > > Strsql = "SELECT LateFeesCharged FROM > qselTransactions_LateFeesCharged > > WHERE CompanyID = 'GMDC' AND TenantID = 'BABET01'" > > > > > > > > I hope I made myself clear. To expound further, I > think that the > spacing > > in your query is incorrect. When you put it in SQL > design, the spacing > > is corrected and returns a value. DAO is not as > picky as ADO. > > > > > > > > HTH, > > > > Roy > > > > > > > > > > > > ________________________________ > > > > From: [email protected] > > [mailto:[EMAIL PROTECTED] On > Behalf Of Bryant > > Sent: Monday, July 18, 2005 4:13 PM > > To: [email protected] > > Subject: Re: [Access VBA Central] Different > results from DAO and ADO > > recordsets > > > > > > > > SQL generated in Debug.Print, when using DAO: > > > > SELECT LateFeesCharged FROM > qselTransactions_LateFeesCharged > > WHERE CompanyID ='GMDC' AND TenantID ='BABET01' > > > > Pasting that into SQL design view of a query > returns a row with the > > proper LateFeesCharged amount. > > > > SQL generated in Debug.Print, when using ADO: > > > > SELECT LateFeesCharged FROM > qselTransactions_LateFeesCharged > > WHERE CompanyID ='GMDC' AND TenantID ='BABET01' > > > > Pasting that into SQL design view of a query also > returns a row with > > the proper LateFeesCharged amount. > > > > Connection string in Debug.Print > (cnn.ConnectionString): > > > > Provider=Microsoft.Jet.OLEDB.4.0;User > ID=Admin;Data > > Source=C:\tis\gmdc_tenants.mdb;Mode=Share Deny > None;Extended > > Properties="";Jet OLEDB:System > > > database=C:\PROGRA~1\COMMON~1\System\SYSTEM.MDW;Jet > OLEDB:Registry > > > Path=Software\Microsoft\Office\11.0\Access\Jet\4.0;Jet > OLEDB:Database > > Password="";Jet OLEDB:Engine Type=5;Jet > OLEDB:Database Locking > > Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet > OLEDB:Global Bulk > > Transactions=1;Jet OLEDB:New Database > Password="";Jet OLEDB:Create > > System Database=False;Jet OLEDB:Encrypt > Database=False;Jet OLEDB:Don't > > Copy Locale on Compact=False;Jet OLEDB:Compact > Without Replica > > Repair=False;Jet OLEDB:SFP=False > > > > Connection string from Immediate Window, when > entering > > ?CurrentProject.Connection: > > > > Provider=Microsoft.Jet.OLEDB.4.0;User > ID=Admin;Data > > Source=C:\tis\gmdc_tenants.mdb;Mode=Share Deny > None;Extended > > Properties="";Jet OLEDB:System > > > database=C:\PROGRA~1\COMMON~1\System\SYSTEM.MDW;Jet > OLEDB:Registry > > > Path=Software\Microsoft\Office\11.0\Access\Jet\4.0;Jet > OLEDB:Database > > Password="";Jet OLEDB:Engine Type=5;Jet > OLEDB:Database Locking > > Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet > OLEDB:Global Bulk > > Transactions=1;Jet OLEDB:New Database > Password="";Jet OLEDB:Create > > System Database=False;Jet OLEDB:Encrypt > Database=False;Jet === message truncated === ____________________________________________________ Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/AccessVBACentral/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
