It is not relevant for what I am doing, but thank you for the suggestion. Bryant
--- In [email protected], Ritesh Agrawal <[EMAIL PROTECTED]> wrote: > > 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/
