Connecting Tech Pros Worldwide Help | Site Map
bytes > topic > access > answers

Query returning value in Access query builder but null in VBA

  #1  
Old January 6th, 2009, 11:26 PM
Familiar Sight
  
Join Date: Apr 2007
Posts: 162
Hi I am running the following query in access query builder. The name of the query is "qryTempRecEFTBankedMaxDate":

Expand|Select|Wrap|Line Numbers
  1. SELECT Max(qryTempRecEFTBanked.Dt) AS MaxOfDt
  2. FROM qryTempRecEFTBanked;
  3.  
it returns a date which exactly what I wanted, but when I run this code in VBA:

Expand|Select|Wrap|Line Numbers
  1. strSqlMaxDate = "qryTempRecEFTBankedMaxDate"
  2.     rst.Open strSqlMaxDate, cnn, adOpenDynamic, adLockOptimistic
  3.     With rst
  4.     If rst.EOF And rst.BOF Then MsgBox "The recordset is blank"
  5.    dtBankMax = !MaxOfDt
  6.     End With
!MaxOfDt returns as Null, even though rst.EOF = false and also rst.BOF = False. I don't know whether it helps but "qryTempRecEFTBankedMaxDate" is based on the query "qryTempRecEFTBanked" the Sql for "qryTempRecEFTBanked" is as follows:
Expand|Select|Wrap|Line Numbers
  1. SELECT Sum(tblBank.Amount) AS SumOfAmount, tblBank.Dt, Val(Right(nz([SerialID],0),1)) AS ConsID, nZ([RecID],0) AS ReconID, tblBank.Desc
  2. FROM tblBank LEFT JOIN tblReconciled ON tblBank.BankID = tblReconciled.BankID
  3. GROUP BY tblBank.Dt, Val(Right(nz([SerialID],0),1)), nZ([RecID],0), tblBank.Desc
  4. HAVING (((Sum(tblBank.Amount))<>0) AND ((tblBank.Dt)>#6/30/2008#) AND ((Val(Right(nz([SerialID],0),1)))=4 Or (Val(Right(nz([SerialID],0),1)))=5 Or (Val(Right(nz([SerialID],0),1)))=7) AND ((nZ([RecID],0))=0) AND ((tblBank.Desc) Like "*HANDYWAY*"))
  5. ORDER BY tblBank.Dt;
  6.  

Many Thanks
  #2  
Old January 7th, 2009, 01:00 AM
NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 15,450
Provided Answers: 56

re: Query returning value in Access query builder but null in VBA

Can't see much wrong there :(

Check out the rst.Open (line #2) for correct parameters, and make sure that rst is defined as DAO.Recordset if you need that or ADODB.Recordset otherwise.

It could simply be the .Open line not working as expected. Check it out in the debugger (Debugging in VBA).
  #3  
Old January 7th, 2009, 02:55 AM
ADezii's Avatar
Expert
  
Join Date: Apr 2006
Location: Philadelphia
Posts: 5,168
Provided Answers: 19

re: Query returning value in Access query builder but null in VBA

Quote:
Originally Posted by iheartvba View Post
Hi I am running the following query in access query builder. The name of the query is "qryTempRecEFTBankedMaxDate":

Expand|Select|Wrap|Line Numbers
  1. SELECT Max(qryTempRecEFTBanked.Dt) AS MaxOfDt
  2. FROM qryTempRecEFTBanked;
  3.  
it returns a date which exactly what I wanted, but when I run this code in VBA:

Expand|Select|Wrap|Line Numbers
  1. strSqlMaxDate = "qryTempRecEFTBankedMaxDate"
  2.     rst.Open strSqlMaxDate, cnn, adOpenDynamic, adLockOptimistic
  3.     With rst
  4.     If rst.EOF And rst.BOF Then MsgBox "The recordset is blank"
  5.    dtBankMax = !MaxOfDt
  6.     End With
!MaxOfDt returns as Null, even though rst.EOF = false and also rst.BOF = False. I don't know whether it helps but "qryTempRecEFTBankedMaxDate" is based on the query "qryTempRecEFTBanked" the Sql for "qryTempRecEFTBanked" is as follows:
Expand|Select|Wrap|Line Numbers
  1. SELECT Sum(tblBank.Amount) AS SumOfAmount, tblBank.Dt, Val(Right(nz([SerialID],0),1)) AS ConsID, nZ([RecID],0) AS ReconID, tblBank.Desc
  2. FROM tblBank LEFT JOIN tblReconciled ON tblBank.BankID = tblReconciled.BankID
  3. GROUP BY tblBank.Dt, Val(Right(nz([SerialID],0),1)), nZ([RecID],0), tblBank.Desc
  4. HAVING (((Sum(tblBank.Amount))<>0) AND ((tblBank.Dt)>#6/30/2008#) AND ((Val(Right(nz([SerialID],0),1)))=4 Or (Val(Right(nz([SerialID],0),1)))=5 Or (Val(Right(nz([SerialID],0),1)))=7) AND ((nZ([RecID],0))=0) AND ((tblBank.Desc) Like "*HANDYWAY*"))
  5. ORDER BY tblBank.Dt;
  6.  

Many Thanks
Try:
Expand|Select|Wrap|Line Numbers
  1. Dim strSqlMazdate As String
  2. Dim rst As ADODB.Recordset
  3. Dim cnn As ADODB.Connection
  4.  
  5. Set rst = New ADODB.Recordset
  6. Set cnn = CurrentProject.Connection
  7.  
  8. strSqlMaxDate = "qryTempRecEFTBankedMaxDate"
  9.  
  10. With rst
  11.   .Open strSqlMaxDate, cnn, adOpenDynamic, adLockOptimistic
  12.     If .BOF And .EOF Then
  13.       MsgBox "The recordset is blank"
  14.     Else
  15.       MsgBox !MaxOfDt
  16.   End If
  17. End With
  18.  
  19. rst.Close
  20. Set rst = Nothing
  #4  
Old January 7th, 2009, 03:50 AM
Familiar Sight
  
Join Date: Apr 2007
Posts: 162

re: Query returning value in Access query builder but null in VBA

I have tried all of the above suggetions. Here are some snipets of my code (if the following code is run in my opinion the code should still work)

Expand|Select|Wrap|Line Numbers
  1. Public cnn As ADODB.Connection
  2. Public rst As New ADODB.Recordset
  3. Private Sub cmdMaxDate_Click()
  4. Set cnn = CurrentProject.Connection
  5. Dim strSqlMaxDate As String
  6. strSqlMaxDate = "qryTempRecEFTBankedMaxDate"
  7.     rst.Open strSqlMaxDate, cnn, adOpenDynamic, adLockOptimistic
  8.     With rst
  9.     If rst.EOF And rst.BOF Then MsgBox "Empty Recordset"
  10.     dtBankMax = !maxofdt
  11.     End With
  12. End Sub
  13.  
Last edited by iheartvba; January 7th, 2009 at 03:51 AM. Reason: Add End Sub
  #5  
Old January 7th, 2009, 11:59 AM
ADezii's Avatar
Expert
  
Join Date: Apr 2006
Location: Philadelphia
Posts: 5,168
Provided Answers: 19

re: Query returning value in Access query builder but null in VBA

Quote:
Originally Posted by iheartvba View Post
I have tried all of the above suggetions. Here are some snipets of my code (if the following code is run in my opinion the code should still work)

Expand|Select|Wrap|Line Numbers
  1. Public cnn As ADODB.Connection
  2. Public rst As New ADODB.Recordset
  3. Private Sub cmdMaxDate_Click()
  4. Set cnn = CurrentProject.Connection
  5. Dim strSqlMaxDate As String
  6. strSqlMaxDate = "qryTempRecEFTBankedMaxDate"
  7.     rst.Open strSqlMaxDate, cnn, adOpenDynamic, adLockOptimistic
  8.     With rst
  9.     If rst.EOF And rst.BOF Then MsgBox "Empty Recordset"
  10.     dtBankMax = !maxofdt
  11.     End With
  12. End Sub
  13.  
Expand|Select|Wrap|Line Numbers
  1. If rst.EOF And rst.BOF Then MsgBox "Empty Recordset"
  2.   'You are attempting to retrieve a Field from a Field in an
  3.   'Empty Recordset, move to Else Clause
  4. Else
  5.   'You've assigned the Value of !maxofdt to dtBankMax, but where
  6.   'is it Declared, and how is it displayed/utilized?
  7.   dtBankMax = !maxofdt
  8.     MsgBox dtBankMax        'Verify?
  9. End If
  #6  
Old January 7th, 2009, 01:35 PM
FishVal's Avatar
Expert
  
Join Date: Jun 2007
Location: Israel
Posts: 2,585
Provided Answers: 8

re: Query returning value in Access query builder but null in VBA

Just a thought.

Could it be synchronization problem?
Did you try to use MoveLast method to force record fetch in recordset object?
Did you try to break the code execution after recordset has been opened and:
a) run code in step mode?
b) inspect rst variable in Watch window?

Regards,
Fish.
  #7  
Old January 7th, 2009, 01:45 PM
NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 15,450
Provided Answers: 56

re: Query returning value in Access query builder but null in VBA

Quote:
Originally Posted by NeoPa View Post
Can't see much wrong there :(

Check out the rst.Open (line #2) for correct parameters, and make sure that rst is defined as DAO.Recordset if you need that or ADODB.Recordset otherwise.

It could simply be the .Open line not working as expected. Check it out in the debugger (Debugging in VBA).
I don't know if you overlooked my earlier post, but checking up from work (where I now have easier access to the information required) it seems that the Open method is a specifically ADODB.Recordset method. This means that unless your rst variable has been defined as ADODB.Recordset (We can't tell as you haven't shared this information) this code will not work (as Access uses DAO generally by default - at least in the older versions). DAO & ADODB do behave differently. ADODB will have different characters for wildcards for instance. This will behave in a different way from the way you would expect Access to behave (For the standard Access ways use DAO).

When I checked the Help system for the Open method of an ADODB recordset the first parameter (Source) did not list a QueryDef as a possible value. Do you have any reason to suppose this is working as you intend?
  #8  
Old January 7th, 2009, 10:17 PM
Familiar Sight
  
Join Date: Apr 2007
Posts: 162

re: Query returning value in Access query builder but null in VBA

Hi
Okay there are 2 posts I would like to attention Post 7 and Post 6:

Post 7:
"...This means that unless your rst variable has been defined as ADODB.Recordset (We can't tell as you haven't shared this information)..."

A: Please see line 2 of the code in Post 4, it shows that the rst variable has been defined as New ADODB.Recordset.

Post 6:
Q:Could it be synchronization problem?
Did you try to use MoveLast method to force record fetch in recordset object?

A: I used the query which "qryTempRecEFTBankedMaxDate" is based on. The name of the query I used is "qryTempRecEFTBanked" (see post 1 for the Sql) but even before I could use the .movelast function, it was coming up as an empty recordset I.E. rst.bof =True and rst.eof =True.

Then I tried to use the actual Sql code but it is giving me the error "Query does not include xxx as part of an aggregate function"
My code is as follows:
Expand|Select|Wrap|Line Numbers
  1. Public cnn As ADODB.Connection
  2. Public rst As New ADODB.Recordset
  3. Private Sub cmdMaxDate_Click()
  4.  strSqlMaxDate = "SELECT Sum(tblBank.Amount) AS SumOfAmount, tblBank.Dt, " & _
  5. "Val(Right(nz([SerialID],0),1)) AS ConsID, nZ([RecID],0) AS ReconID, tblBank.Desc " & _
  6. "FROM tblBank LEFT JOIN tblReconciled ON tblBank.BankID=tblReconciled.BankID " & _
  7. "GROUP BY tblBank.Dt, Val(Right(nz([SerialID],0),1)), nZ([RecID],0), tblBank.Desc " & _
  8. "HAVING ((((Sum(tblBank.Amount)) <> 0) And ((tblBank.Dt) > #6/30/2008#) And " & _ 
  9. "((Val(Right(nZ([SerialID], 0), 1))) = 4 Or (Val(Right(nZ([SerialID], 0), 1))) = 5 " & _
  10. "Or (Val(Right(nZ([SerialID], 0), 1))) = 7) And ((nZ([RecID], 0))=0) And ((tblBank.Desc) Like '* HANDYWAY *'))) " & _
  11. "ORDER BY tblBank.Dt;"
  12.     rst.Open strSqlMaxDate, cnn, adOpenDynamic, adLockOptimistic
  13.     With rst
  14.         If rst.EOF And rst.BOF Then
  15.         MsgBox "empty recordset"
  16.         Else
  17.         dtBankMax = !Dt
  18.         MsgBox dtBankMax
  19.         End If
  20.     End With
  21. End Sub
  22.  
  #9  
Old January 7th, 2009, 11:34 PM
ADezii's Avatar
Expert
  
Join Date: Apr 2006
Location: Philadelphia
Posts: 5,168
Provided Answers: 19

re: Query returning value in Access query builder but null in VBA

Would it be possible to E-Mail a subset of the Database as an Attachment where we can visually see what is going on? The data would not have to be real, just representative.
  #10  
Old January 8th, 2009, 12:32 AM
Familiar Sight
  
Join Date: Apr 2007
Posts: 162

re: Query returning value in Access query builder but null in VBA

yes whats your e-mail address?
  #11  
Old January 8th, 2009, 03:41 AM
Familiar Sight
  
Join Date: Apr 2007
Posts: 162

re: Query returning value in Access query builder but null in VBA

I have found the problem. It doesn't like it when I filter Desc by Like "*HandyWay*". Thats it, everything else is fine. But I need to have that Filter. :S
  #12  
Old January 8th, 2009, 02:02 PM
NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 15,450
Provided Answers: 56

re: Query returning value in Access query builder but null in VBA

Quote:
Originally Posted by iheartvba View Post
Hi
Okay there are 2 posts I would like to attention Post 7 and Post 6:

Post 7:
"...This means that unless your rst variable has been defined as ADODB.Recordset (We can't tell as you haven't shared this information)..."
A: Please see line 2 of the code in Post 4, it shows that the rst variable has been defined as New ADODB.Recordset.
I apologise. Clearly it was there by the time I posted if I'd read through your post more carefully. I'd seen that you hadn't replied to my post and skimmed quickly through the other posts. It's actually quite time-consuming trying to work in the absence of direct responses as it's never clear where you are.

You may have picked up in my earlier response (post #7) also that one of the things to watch out for when using ADODB (rather than DAO) is the difference of the wildcard characters (which appears to be at the heart of your problem). Check out ANSI Standards in String Comparisons for help with that.
  #13  
Old January 8th, 2009, 11:30 PM
Familiar Sight
  
Join Date: Apr 2007
Posts: 162

re: Query returning value in Access query builder but null in VBA

Sorry about not replying directly NeoPa, I now understand how it can cause some confustion. I will try to be more carefull next time.

You were correct in Post 7 about the wildcards, excuse me for missing that. The Correct code is as follows:

Expand|Select|Wrap|Line Numbers
  1. Public cnn As ADODB.Connection
  2. Public rst As New ADODB.Recordset
  3. Private Sub cmdMaxDate_Click() 
  4. Dim strSqlMaxDate As String
  5. Dim strSqlBank As String
  6. strSqlBank = "(SELECT Sum(tblBank.Amount) AS BankedAmount, tblBank.Dt, " & _
  7. "Val(Right(nz([SerialID],0),1)) AS ConsID, nZ([RecID],0) AS ReconID, tblBank.Desc " & _
  8. "FROM tblBank LEFT JOIN tblReconciled ON tblBank.BankID=tblReconciled.BankID " & _
  9. "GROUP BY tblBank.Dt, Val(Right(nz([SerialID],0),1)), nZ([RecID],0), tblBank.Desc, tblBank.Amount)"
  10. strSqlMaxDate = " SELECT BankedAmount, Dt, ConsID, ReconID, Desc " & _
  11. "FROM " & strSqlBank & " " & _
  12. "WHERE BankedAmount <>0 And Dt > #6/30/2008# And ConsID = 4 Or ConsID = 5 or ConsID = 7 " & _
  13. "And ReconID = 0 And Desc Like '% HANDYWAY %' " & _
  14. "ORDER BY Dt"
  15. rst.Open strSqlMaxDate, cnn, adOpenDynamic, adLockOptimistic
  16.     With rst
  17.         dtBankMax = !Dt
  18.     End With
  19. End Sub
  20.  
  #14  
Old January 8th, 2009, 11:56 PM
NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 15,450
Provided Answers: 56

re: Query returning value in Access query builder but null in VBA

No worries :)

I'm pleased that you got it sorted, and we always appreciate that you post your solutions when you find them. It makes the whole process work so much better for any others that come along later with similar problems. Good for you.

PS. I assume that going from "*HANDYWAY*" (No spaces) to "% HANDYWAY %" (Spaces) was a deliberate choice on your part. If not I'm sure that you'll appreciate it's a little different that way.
  #15  
Old January 9th, 2009, 03:30 AM
Familiar Sight
  
Join Date: Apr 2007
Posts: 162

re: Query returning value in Access query builder but null in VBA

No that is by accident, I't won't make a difference, but thanks for the pick up.
Last edited by iheartvba; January 9th, 2009 at 03:31 AM. Reason: add text
Reply

« previous question | next question »

/bytes/development

/bytes/it

/bytes/about

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 225,662 network members.

dev questions:
algorithms | asp | asp.net | c/c++ | coldfusion | c# | flash | html/css | java | javascript |
mobile dev | .net | perl | php | python | ruby | software dev | vb.net | visual basic | xml
it questions:
access | apache | careers | db2 | iis | macosx | mysql | networking | oracle | postgresql | sql server | unix | windows

About Bytes | Copyright 1995-2009 BYTES. All rights Reserved