Date Variable not working in vba code

[PNGBill]

This procedure works. - criteria is Date() (today)

Public Function PortfolioBalance() As Currency          'Calculate Portfolio Balance as at today
    
    Dim dbs As DAO.Database, rst As DAO.Recordset
    Dim sqlString As String
    Dim PortfolioSum As Currency    'Hold the Portfolio Balance as calculated
    
    PortfolioSum = 0                ' set variable to Zero to start
    
        'sql query to Sum TBLTRANS.TRNDR for Interest, Late fee and Legal Fees only up to and including today's date
            ' Add Sum TBLTRANS.TRNPR for Principal, fee Process and fee Aplic
            ' Deduct Sum tblMemberRepayments.PaymentAmt for all paymets made
    sqlString = "SELECT TOP 1 Nz((SELECT Sum(TRNDR) AS SumOfTRNDR " & vbCrLf & _
         " FROM TBLTRANS " & vbCrLf & _
         " WHERE TRNACTDTE<=Date()  " & vbCrLf & _
         " AND TRNTYP In (""Interest"",""Late fee"", ""Legal Fees"")  ),0)+Nz((SELECT Sum(TBLTRANS.TRNPR) AS SumOfTRNPR " & vbCrLf & _
         " FROM TBLTRANS  ),0)-Nz((SELECT Sum(tblMemberRepayments.PaymentAmt) AS SumOfPaymentAmt " & vbCrLf & _
         " FROM tblMemberRepayments  ),0) AS PortBalance " & vbCrLf & _
         "FROM MSysObjects;"
    
         'Open Recordset
    Set dbs = CurrentDb()
    Set rst = dbs.OpenRecordset(sqlString)
    
    PortfolioSum = rst!PortBalance   'put result of sqlString as variable PortfolioSum
 
    PortfolioBalance = PortfolioSum     'Return Variable to Function PortfolioBalance
   
    'Close database variables
    rst.Close
    dbs.Close
  
End Function

I want to get the date as a variable but this attempt doesn't work.

Public Function PortfolioAnyDate(AnyDate As Date) As Currency      'Calculate Portfolio Balance for any date

    Dim dbs As DAO.Database, rst As DAO.Recordset
    Dim sqlString As String
    Dim PortfolioSum As Currency    'Hold the Portfolio Balance as calculated
    
    PortfolioSum = 0                ' set variable to Zero to start
    
        'sql query to Sum TBLTRANS.TRNDR for Interest, Late fee and Legal Fees only up to and including today's date
            ' Add Sum TBLTRANS.TRNPR for Principal, fee Process and fee Aplic
            ' Deduct Sum tblMemberRepayments.PaymentAmt for all paymets made
    sqlString = "SELECT TOP 1 Nz((SELECT Sum(TRNDR) AS SumOfTRNDR " & vbCrLf & _
         " FROM TBLTRANS " & vbCrLf & _
         " WHERE TRNACTDTE<=#AnyDate#  " & vbCrLf & _
         " AND TRNTYP In (""Interest"",""Late fee"", ""Legal Fees"")  ),0)+Nz((SELECT Sum(TBLTRANS.TRNPR) AS SumOfTRNPR " & vbCrLf & _
         " FROM TBLTRANS  ),0)-Nz((SELECT Sum(tblMemberRepayments.PaymentAmt) AS SumOfPaymentAmt " & vbCrLf & _
         " FROM tblMemberRepayments  ),0) AS PortBalance " & vbCrLf & _
         "FROM MSysObjects;"
    
         'Open Recordset
    Set dbs = CurrentDb()
    Set rst = dbs.OpenRecordset(sqlString)
    
    PortfolioSum = rst!PortBalance   'put result of sqlString as variable PortfolioSum
 
    PortfolioAnyDate = PortfolioSum     'Return Variable to Function PortfolioBalance
   
    'Close database variables
    rst.Close
    dbs.Close

End Function

I assume the problem is related to how I do the variable date. Also, not using USA date system, if this is an issue.

Appreciate any advice

 

[pbaldy]

As with any variable, you have to concatenate it:

" WHERE TRNACTDTE<=#" & AnyDate & "# " & vbCrLf & _

yes, the US date is an issue:

http://support.microsoft.com/default.aspx?scid=kb;en-us;210069

http://www.mvps.org/access/datetime/date0005.htm

I've used the second, with a date only format.

 

[pbaldy]

By the way, the vbCrLf in the SQL are not necessary. They won't cause a problem, but I wouldn't include them. They're just clutter.

 

[PNGBill]

By the way, the vbCrLf in the SQL are not necessary. They won't cause a problem, but I wouldn't include them. They're just clutter.

They are produced by Allen Browne's conversion form.

I guess I should edit the form but trust I won't turn it into "mush"

 

[pbaldy]

Like I said, they won't hurt anything, so I guess I'd leave them. I thought you were typing them in.

 

[PNGBill]

Thanks Paul, this works.

I tested with today's date "23/07/2010" and it gave me the same result as the earlier Function which uses Date().
Can I take this to mean no US/World date issue?

Public Function PortfolioAnyDate(AnyDate As Date) As Currency      'Calculate Portfolio Balance for any date

    Dim dbs As DAO.Database, rst As DAO.Recordset
    Dim sqlString As String
    Dim PortfolioSum As Currency    'Hold the Portfolio Balance as calculated
    
    PortfolioSum = 0                ' set variable to Zero to start
    
        'sql query to Sum TBLTRANS.TRNDR for Interest, Late fee and Legal Fees only up to and including today's date
            ' Add Sum TBLTRANS.TRNPR for Principal, fee Process and fee Aplic
            ' Deduct Sum tblMemberRepayments.PaymentAmt for all paymets made
    sqlString = "SELECT TOP 1 Nz((SELECT Sum(TRNDR) AS SumOfTRNDR " & vbCrLf & _
         " FROM TBLTRANS " & vbCrLf & _
         " WHERE TRNACTDTE<=#" & AnyDate & "#" & vbCrLf & _
         " AND TRNTYP In (""Interest"",""Late fee"", ""Legal Fees"")  ),0)+Nz((SELECT Sum(TBLTRANS.TRNPR) AS SumOfTRNPR " & vbCrLf & _
         " FROM TBLTRANS  ),0)-Nz((SELECT Sum(tblMemberRepayments.PaymentAmt) AS SumOfPaymentAmt " & vbCrLf & _
         " FROM tblMemberRepayments  ),0) AS PortBalance " & vbCrLf & _
         "FROM MSysObjects;"
    
         'Open Recordset
    Set dbs = CurrentDb()
    Set rst = dbs.OpenRecordset(sqlString)
    
    PortfolioSum = rst!PortBalance   'put result of sqlString as variable PortfolioSum
 
    PortfolioAnyDate = PortfolioSum     'Return Variable to Function PortfolioBalance
   
    'Close database variables
    rst.Close
    dbs.Close

End Function

 

[pbaldy]

I wouldn't necessarily say that. It may have worked because that date can only be interpreted one way. Try with something like 7/8/2010 and see what happens. It will probably interpret it as July 8. I'd take all doubt out and use one of those techniques.