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 OLEDB:Don't > Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica > Repair=False;Jet OLEDB:SFP=False > > HTH, > Bryant > > > --- In [email protected], "Roy Forkner" <[EMAIL PROTECTED]> > wrote: > > What I am saying is when you do the debug,print command and copy and > > paste to SQL design view, the wizard may be correcting space problems. > > > > If you posted directly from the debug.print I did not see it. > > > > I assume the connection is correct or you would most likely get an > > error. That said, I would do a debug.print on the cnn.connectiontext > and > > make sure it is correct. > > > > From my time (in hell) working with Access, I found that ADO was much > > easier to use and always returned exactly what it asked for. If the > > query is not formed correctly you will not get the correct result. > > > > You need to make sure that there are spaces, in the query, where there > > should be spaces. Etc. > > > > HTH, > > > > Roy > > > > > > > > ________________________________ > > > > From: [email protected] > > [mailto:[EMAIL PROTECTED] On Behalf Of Bryant > > Sent: Monday, July 18, 2005 3:32 PM > > To: [email protected] > > Subject: Re: [Access VBA Central] Different results from DAO and ADO > > recordsets > > > > > > > > My connection is set to CurrentProject.Connection: > > > > Set cnn = CurrentProject.Connection > > > > I'm not sure how that could be an issue, as no string is being > defined? > > > > Also not sure what you're wanting, in terms of seeing SQL, beyond what > > I've already posted? I have entered a Debug.Print command, and > > reviewed the SQL that is produced by concatening strSQL, and verified > > that the SQL portion is identical when executed by DAO or ADO, and > > that when pasted into SQL design view of a query, it works. Is this > > what you referred to? > > > > FWIW I'm not doing ADO and DAO together. I'm moving my database code > > to ADO, and it only makes sense to compare whether or not identical > > outcomes are being produced by each, before giving my users a live > > front-end that doesn't work the same. At present I'm making an ADO > > version of existing DAO functions, comparing outcomes, and if it works > > properly commenting out DAO. In 90% of the occasions, it's worked > > fantastic. > > > > Thanks to all who are contributing! > > Bryant > > > > > > --- In [email protected], "Roy Forkner" <[EMAIL PROTECTED]> > > wrote: > > > If there is a record in DAO and not in ADO then there can only be a > > > couple of things wrong. > > > > > > I would define your connection string. Chances are it is not > correct. > > > > > > I would put a breakpoint and check what strSQL has as a value. Check > > for > > > proper spacing, etc. In fact, can't you drop the strSQL in the > command > > > window and see the real values of strSQL? Post it and maybe someone > > will > > > see what is wrong. > > > > > > Finally, why are you doing ADO and DAO together? > > > > > > Roy > > > > > > > > > > > > > > > > > > ________________________________ > > > > > > From: [email protected] > > > [mailto:[EMAIL PROTECTED] On Behalf Of Bryant > > > Sent: Monday, July 18, 2005 2:43 PM > > > To: [email protected] > > > Subject: Re: [Access VBA Central] Different results from DAO and ADO > > > recordsets > > > > > > > > > > > > This gives a "No current record" error -- cannot move first/last > when > > > there is no record retrieved. > > > > > > The data, when correctly queried, produces a single record. DAO > > > produces this record, as does executing the query manually. ADO does > > > not. > > > > > > Bryant > > > > > > --- In [email protected], "Roy Forkner" > <[EMAIL PROTECTED]> > > > wrote: > > > > Try : > > > > > > > > Rst.movelast > > > > > > > > Rst.movefirst > > > > > > > > > > > > > > > > The same as the someone's suggestion except use ADO instead of DAO > > > > > > > > > > > > > > > > ________________________________ > > > > > > > > From: [email protected] > > > > [mailto:[EMAIL PROTECTED] On Behalf Of Bryant > > > > Sent: Monday, July 18, 2005 2:24 PM > > > > To: [email protected] > > > > Subject: Re: [Access VBA Central] Different results from DAO and > ADO > > > > recordsets > > > > > > > > > > > > > > > > Thanks for the semicolon tip -- it didn't work but that is > something > > I > > > > be careful to put in my code to avoid it being an issue. > > > > > > > > The query runs fine in SQL view - and in DAO via code. That's what > > > > makes it puzzling - lucky I have no hair to pull out :) > > > > > > > > Bryant > > > > > > > > --- In [email protected], Dennis Jensen > > > > <[EMAIL PROTECTED]> wrote: > > > > > For ADO try adjusting the last line as such... > > > > > > > > > > & "AND TenantID ='" & strTenantID & "';" > > > > > > > > > > Not sure if that will fix it but I have found that not > > > > > including the ";" at the in when query an Access > > > > > database can cause the query to fail. > > > > > > > > > > The next best thing is to take a copy of the actual > > > > > query being submitted via code and put that in a > > > > > Queries SQL view and see if you can execute it. If it > > > > > fails it might say why. > > > > > > > > > > > > > > > --- Bryant <[EMAIL PROTECTED]> wrote: > > > > > > > > > > > I get two different results when querying the same > > > > > > data, depending on > > > > > > whether I use DAO 3.6 or ADO 2.1 recordset ... any > > > > > > ideas where I'm > > > > > > going astray? > > > > > > > > > > > > The ADO recordset retrieves zero rows each time. The > > > > > > SQL string is > > > > > > valid, since results are produced via DAO. > > > > > > > > > > > > Thanks in advance! > > > > > > > > > > > > -- Begin ADO -- > > > > > > Dim rst As New ADODB.Recordset > > > > > > Dim cnn As New ADODB.Connection > > > > > > Dim strSQL As String > > > > > > > > > > > > Set cnn = CurrentProject.Connection > > > > > > > > > > > > strSQL = "SELECT LateFeesCharged FROM > > > > > > qselTransactions_LateFeesCharged " _ > > > > > > & "WHERE CompanyID ='" & strCompanyID & "' " _ > > > > > > & "AND TenantID ='" & strTenantID & "'" > > > > > > > > > > > > rst.Open strSQL, cnn, adOpenStatic, adLockReadOnly > > > > > > > > > > > > If Not rst.EOF Then > > > > > > GetLateFeesCharged = rst!LateFeesCharged > > > > > > Else > > > > > > GetLateFeesCharged = 0 > > > > > > End If > > > > > > > > > > > > rst.Close > > > > > > -- End ADO -- > > > > > > > > > > > > -- Begin DAO -- > > > > > > Dim rst As DAO.Recordset > > > > > > Dim strSQL As String > > > > > > > > > > > > strSQL = "SELECT LateFeesCharged FROM > > > > > > qselTransactions_LateFeesCharged " _ > > > > > > & "WHERE CompanyID ='" & strCompanyID & "' " _ > > > > > > & "AND TenantID ='" & strTenantID & "'" > > > > > > > > > > > > Set rst = CurrentDb.OpenRecordset(strSQL, > > > > > > dbOpenDynaset, dbReadOnly) > > > > > > > > > > > > If Not rst.EOF Then > > > > > > GetLateFeesCharged_DAO = rst!LateFeesCharged > > > > > > Else > > > > > > GetLateFeesCharged_DAO = 0 > > > > > > End If > > > > > > > > > > > > rst.Close > > > > > > > > > > > > -- End DAO -- > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > ____________________________________________________ > > > > > Start your day with Yahoo! - make it your home page > > > > > http://www.yahoo.com/r/hs > > > > > > > > > > > > > > > > > > > > > > > > > > > > ________________________________ > > > > > > > > YAHOO! GROUPS LINKS > > > > > > > > > > > > > > > > * Visit your group "AccessVBACentral > > > > <http://groups.yahoo.com/group/AccessVBACentral> " on the web. > > > > > > > > * To unsubscribe from this group, send an email to: > > > > [EMAIL PROTECTED] > > > > > > > > > > <mailto:[EMAIL PROTECTED] > > > > > > > > > > > > > * Your use of Yahoo! Groups is subject to the Yahoo! Terms of > > > > Service <http://docs.yahoo.com/info/terms/> . > > > > > > > > > > > > > > > > ________________________________ > > > > > > > > > > > > > > > > [Non-text portions of this message have been removed] > > > > > > > > > > > > > > > > > > SPONSORED LINKS > > > > > > Microsoft access database > > > > > > <http://groups.yahoo.com/gads?t=ms&k=Microsoft+access+database&w1=Micros > > > > > > oft+access+database&w2=Microsoft+access&w3=Microsoft+access+database+des > > > > > > ign&w4=Microsoft+access+database+training&c=4&s=131&.sig=_NMBmgHqxR8S8tj > > > yGl-Tnw> > > > > > > Microsoft access > > > > > > <http://groups.yahoo.com/gads?t=ms&k=Microsoft+access&w1=Microsoft+acces > > > > > > s+database&w2=Microsoft+access&w3=Microsoft+access+database+design&w4=Mi > > > > > > crosoft+access+database+training&c=4&s=131&.sig=-5Xf2KTU4AmC58h30Cq3wg> > > > > > > > > > Microsoft access database design > > > > > > <http://groups.yahoo.com/gads?t=ms&k=Microsoft+access+database+design&w1 > > > > > > =Microsoft+access+database&w2=Microsoft+access&w3=Microsoft+access+datab > > > > > > ase+design&w4=Microsoft+access+database+training&c=4&s=131&.sig=rEvcpxWQ > > > YfjON1btwfI3Aw> > > > > > > Microsoft access database training > > > > > > <http://groups.yahoo.com/gads?t=ms&k=Microsoft+access+database+training&; > > > > > > w1=Microsoft+access+database&w2=Microsoft+access&w3=Microsoft+access+dat > > > > > > abase+design&w4=Microsoft+access+database+training&c=4&s=131&.sig=pILo23 > > > -rKYDuxPwf_IbrRQ> > > > > > > > > > > > > > > > > > > > > > > > > ________________________________ > > > > > > YAHOO! GROUPS LINKS > > > > > > > > > > > > * Visit your group "AccessVBACentral > > > <http://groups.yahoo.com/group/AccessVBACentral> " on the web. > > > > > > * To unsubscribe from this group, send an email to: > > > [EMAIL PROTECTED] > > > > > > <mailto:[EMAIL PROTECTED] > > > > > > > > > > * Your use of Yahoo! Groups is subject to the Yahoo! Terms of > > > Service <http://docs.yahoo.com/info/terms/> . > > > > > > > > > > > > ________________________________ > > > > > > > > > > > > [Non-text portions of this message have been removed] > > > > > > > > > > > > ________________________________ > > > > YAHOO! GROUPS LINKS > > > > > > > > * Visit your group "AccessVBACentral > > <http://groups.yahoo.com/group/AccessVBACentral> " on the web. > > > > * To unsubscribe from this group, send an email to: > > [EMAIL PROTECTED] > > > <mailto:[EMAIL PROTECTED] > > > > > > > * Your use of Yahoo! Groups is subject to the Yahoo! Terms of > > Service <http://docs.yahoo.com/info/terms/> . > > > > > > > > ________________________________ > > > > > > > > [Non-text portions of this message have been removed] > > > > > > SPONSORED LINKS > > Microsoft access database > <http://groups.yahoo.com/gads?t=ms&k=Microsoft+access+database&w1=Micros > oft+access+database&w2=Microsoft+access&w3=Microsoft+access+database+des > ign&w4=Microsoft+access+database+training&c=4&s=131&.sig=_NMBmgHqxR8S8tj > yGl-Tnw> > > Microsoft access > <http://groups.yahoo.com/gads?t=ms&k=Microsoft+access&w1=Microsoft+acces > s+database&w2=Microsoft+access&w3=Microsoft+access+database+design&w4=Mi > crosoft+access+database+training&c=4&s=131&.sig=-5Xf2KTU4AmC58h30Cq3wg> > > > Microsoft access database design > <http://groups.yahoo.com/gads?t=ms&k=Microsoft+access+database+design&w1 > =Microsoft+access+database&w2=Microsoft+access&w3=Microsoft+access+datab > ase+design&w4=Microsoft+access+database+training&c=4&s=131&.sig=rEvcpxWQ > YfjON1btwfI3Aw> > > Microsoft access database training > <http://groups.yahoo.com/gads?t=ms&k=Microsoft+access+database+training&; > w1=Microsoft+access+database&w2=Microsoft+access&w3=Microsoft+access+dat > abase+design&w4=Microsoft+access+database+training&c=4&s=131&.sig=pILo23 > -rKYDuxPwf_IbrRQ> > > > > > > > > ________________________________ > > YAHOO! GROUPS LINKS > > > > * Visit your group "AccessVBACentral > <http://groups.yahoo.com/group/AccessVBACentral> " on the web. > > * To unsubscribe from this group, send an email to: > [EMAIL PROTECTED] > <mailto:[EMAIL PROTECTED] > > > > * Your use of Yahoo! Groups is subject to the Yahoo! Terms of > Service <http://docs.yahoo.com/info/terms/> . > > > > ________________________________ > > > > [Non-text portions of this message have been removed] ________________________________ YAHOO! GROUPS LINKS * Visit your group "AccessVBACentral <http://groups.yahoo.com/group/AccessVBACentral> " on the web. * To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED] > * Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service <http://docs.yahoo.com/info/terms/> . ________________________________ [Non-text portions of this message have been removed] 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/
