Full Version: Using a function that returns a date as criteria for a query
UtterAccess Forums > Microsoft® Access > Access Date + Time
On my database I have two forms that allow the user to choose a fiscal year and a given month inside of that fiscal year. From this I am trying to run a query that is based on the selected month.
THere is a function I've placed in a module:
Public Function GetAccountingEndDate() As Date
    Dim Month, Year As Integer
    Month = GetAccountingMonth
    If (Month > 6) Then
        Year = Left(Forms.Main.lstYear, 4)
        Year = Right(Forms.Main.lstYear, 4)
    End If
    If (Month = 12) Then
        Year = Year + 1
    End If
    Month = (Month Mod 12) + 1
    MsgBox CStr(Month)
    MsgBox GetMonth(Month) & " 1, " & Year
    GetAccountingDate = CDate(GetMonth(Month) & " 1, " & Year)
End Function

You can ignore the vast majority of this function.. I'm fairly certain it works perfectly. The second MsgBox to be displayed shows me dates such as "October 1, 2004" and this is the correct date given the user selection. I also am under the impression that this is a suitable date format for use with CDate(), and I've also tried forms like "10/1/2004" and "10/1/2004 00:00:00"
My problem comes when I try to use this function in a query:
SELECT sem_ytd_earnings.ewu_id, account_id, Sum(gross_pay)
FROM sem_ytd_earnings
WHERE sem_ytd_earnings.academic_year=GetYear() And sem_ytd_earnings.account_id Like '%2830' And sem_ytd_earnings.affected_earnings_date < GetAccountingEndDate()
GROUP BY sem_ytd_earnings.ewu_id, account_id;
This query simply returns no results (no errors), but if I remove the 'sem_ytd_earnings.affected_earnings_date < GetAccountingEndDate()' criteria then the query returns results.
Like I said, I'm almost positive that the Date value returns the correct date. Also, I checked that Access is seeing the affected_earnings_date field as Date/Time.
Am I using the wrong data type in the VB code (Date)?
Jon C.
I'm not certain, but I don't think that you can compare date values as if they were numeric values, because of the way Access (and Microsoft) keeps track of dates. Instead, I might make use of the DateDiff function.
My guess is that if you replace the expression:
sem_ytd_earnings.affected_earnings_date < GetAccountingEndDate()
DateDiff("d", sem_ytd_earnings.affected_earnings_date, GetAccountingEndDate()) > 0
... it should work as expected.thumbup.gif
Thanks for the reply.. it didn't quite work but I think I am on the right track. After your suggestion didn't work (even though the query ran w/ no errors), I decided to remove the criteria, put the DateDiff as a selected field and the results were all very large negative numbers. That got me curious and I decided to put GetAccountingEndDate() also as a requested field. When the query runs, GetAccountingEndDate displays as "12:00:00 AM" (not even any date part).
I've modified the end of GetAccountingEndDate() to the following:

    Result = CDate(Month & "/01/" & Year & " 01:00:00")
    MsgBox CStr(Result)
    GetAccountingDate = Result

The message box displays things like "10/1/2005 01:00:00" (note that 01 has changed to 1) so I am still fairly certain that when the function returns the result it contains a valid Date. The query doesn't seem to even be pulling the time portion from GetAccountingEndDate(), because I've set that to 1:00am but the query just shows 12:00am.
I hope this info helps.
I've been forgetting to mention my exact software...
ccess version is 2002
Underlying database is MySQL, w/ MySQL ODBC 3.51
Hmm. Although the message box contains something that looks like a valid date, it may not be.
few things to note - sort of off the track:
  • The Access function IsDate() will return a True/False value, based on whether the string enclosed is a valid date or not.
  • The statement "Dim Month, Year As Integer" should be rewritten as "Dim Month as Integer, Year as Integer"
  • You may also want to consider renaming those variables - I think that Month and Year are Access reserved words, and can cause unexpected results if used as variable names.

Also, in looking back at the origional post I had a few more questions:
In the original code, the line Month = GetAccountingMonth didn't compile, because the variable GetAccountingMonth was not defined. Is this a variable, or a function call that is missing it's parenthesis?
Also, the function GetMonth()... not sure what it does to the end results... the error may be in there someplace.
Is it possible to post an example of this problem in action? It may help to trouble shoot the problem?
Now that I'm not pulling my hair out and looking over the query with reddened eyes I discovered a simple coding error that naturally was not caught by the VB compiler. In the last line of the function, instead of setting "GetAccountingEndDate" which is the name of the function, I was accidentally setting "GetAccountingDate" frown.gif
evertheless, thank you Steve Ryan. I might not have discovered this without performing the query changes you suggested and subsequently seeing that GetAccountingEndDate() was returning some sort of empty value.
Your quite welcome. Glad I could help... grin.gif
Good luck in future projects!
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.