Query returning value in Access query builder but null in VBA

[iheartvba]

Hi I am running the following query in access query builder. The name of the query is "qryTempRecEFTB ankedMaxDate":

SELECT Max(qryTempRecEFTBanked.Dt) AS MaxOfDt
FROM qryTempRecEFTBanked;
 

it returns a date which exactly what I wanted, but when I run this code in VBA:

strSqlMaxDate = "qryTempRecEFTBankedMaxDate"
    rst.Open strSqlMaxDate, cnn, adOpenDynamic, adLockOptimistic
    With rst
    If rst.EOF And rst.BOF Then MsgBox "The recordset is blank"
   dtBankMax = !MaxOfDt
    End With

!MaxOfDt returns as Null, even though rst.EOF = false and also rst.BOF = False. I don't know whether it helps but "qryTempRecEFTB ankedMaxDate" is based on the query "qryTempRecEFTB anked" the Sql for "qryTempRecEFTB anked" is as follows:

SELECT Sum(tblBank.Amount) AS SumOfAmount, tblBank.Dt, Val(Right(nz([SerialID],0),1)) AS ConsID, nZ([RecID],0) AS ReconID, tblBank.Desc
FROM tblBank LEFT JOIN tblReconciled ON tblBank.BankID = tblReconciled.BankID
GROUP BY tblBank.Dt, Val(Right(nz([SerialID],0),1)), nZ([RecID],0), tblBank.Desc
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*"))
ORDER BY tblBank.Dt;
 

Many Thanks

[NeoPa]

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).

[ADezii]

@iheartvba
Try:

Dim strSqlMazdate As String
Dim rst As ADODB.Recordset
Dim cnn As ADODB.Connection
 
Set rst = New ADODB.Recordset
Set cnn = CurrentProject.Connection
 
strSqlMaxDate = "qryTempRecEFTBankedMaxDate"
 
With rst
  .Open strSqlMaxDate, cnn, adOpenDynamic, adLockOptimistic
    If .BOF And .EOF Then
      MsgBox "The recordset is blank"
    Else
      MsgBox !MaxOfDt
  End If
End With
 
rst.Close
Set rst = Nothing

[iheartvba]

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)

Public cnn As ADODB.Connection
Public rst As New ADODB.Recordset
Private Sub cmdMaxDate_Click()
Set cnn = CurrentProject.Connection
Dim strSqlMaxDate As String
strSqlMaxDate = "qryTempRecEFTBankedMaxDate"
    rst.Open strSqlMaxDate, cnn, adOpenDynamic, adLockOptimistic
    With rst
    If rst.EOF And rst.BOF Then MsgBox "Empty Recordset"
    dtBankMax = !maxofdt
    End With
End Sub
 

[ADezii]

@iheartvba

If rst.EOF And rst.BOF Then MsgBox "Empty Recordset"
  'You are attempting to retrieve a Field from a Field in an
  'Empty Recordset, move to Else Clause
Else
  'You've assigned the Value of !maxofdt to dtBankMax, but where
  'is it Declared, and how is it displayed/utilized?
  dtBankMax = !maxofdt
    MsgBox dtBankMax        'Verify?
End If

[FishVal]

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.

[NeoPa]

@NeoPa
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?

[iheartvba]

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 "qryTempRecEFTB ankedMaxDate" is based on. The name of the query I used is "qryTempRecEFTB anked" (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:

Public cnn As ADODB.Connection
Public rst As New ADODB.Recordset
Private Sub cmdMaxDate_Click()
 strSqlMaxDate = "SELECT Sum(tblBank.Amount) AS SumOfAmount, tblBank.Dt, " & _
"Val(Right(nz([SerialID],0),1)) AS ConsID, nZ([RecID],0) AS ReconID, tblBank.Desc " & _
"FROM tblBank LEFT JOIN tblReconciled ON tblBank.BankID=tblReconciled.BankID " & _
"GROUP BY tblBank.Dt, Val(Right(nz([SerialID],0),1)), nZ([RecID],0), tblBank.Desc " & _
"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 *'))) " & _
"ORDER BY tblBank.Dt;"
    rst.Open strSqlMaxDate, cnn, adOpenDynamic, adLockOptimistic
    With rst
        If rst.EOF And rst.BOF Then
        MsgBox "empty recordset"
        Else
        dtBankMax = !Dt
        MsgBox dtBankMax
        End If
    End With
End Sub
 

[ADezii]

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.

[iheartvba]

yes whats your e-mail address?