# Thread: Date funtion as crieria problem (Acc 97 sr2 on 95b)

1. ## Date funtion as crieria problem (Acc 97 sr2 on 95b)

I am trying to build a query that shows records dated in the current month

SELECT tblCosts.CatID, Sum(tblCosts.Amount) AS SumOfAmount
FROM tblCosts
WHERE (((tblCosts.OrderDate)>=CurrentMonth()))
GROUP BY tblCosts.CatID;

where

Function CurrentMonth()
Dim MDate As Date
MDate = CDate("01/" & Month(Date) & "/" & Year(Date))
End Function

But as long as the criteria has a > sign in it, I get all records regardless of date. If I use = or < I get none at all.

OBVIOUSLY I have no idea of how to use funtions in criteria properly.

Could someone kindly point me in the right direction and switch the light one for me please. <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

2. ## Re: Date funtion as crieria problem (Acc 97 sr2 on 95b)

Try this - it doesn't need a user-defined function:

SELECT tblCosts.CatID, Sum(tblCosts.Amount) AS SumOfAmount
FROM tblCosts
WHERE Year(tblCosts.OrderDate) = Year(Date()) And Month(tblCosts.OrderDate) = Month(Date())
GROUP BY tblCosts.CatID;

3. ## Re: Date funtion as crieria problem (Acc 97 sr2 on 95b)

Note, just FYI: your never set the return value of your function. You must always assign a value to the function name:

Function MyFunc(ArgsHere)
...
MyFunc = ...
End Function

The CurrentMonth function (which is not needed for your query) could look like:

Function CurrentMonth() As Date
CurrentMonth = DateSerial(Year(Date()), Month(Date()), 1)
End Function

4. ## Re: Date funtion as crieria problem (Acc 97 sr2 on 95b)

Hans,

Thanks for the "Quick Fix" and also for the explanation. I had wondered how the function value was returned.

As my function call was not returning a value, was I actually asking for all values greater than nothing?
This would explain why = and < did not return any records.

5. ## Re: Date funtion as crieria problem (Acc 97 sr2 on 95b)

SQL probably evaluates your function as 0, which as a date is equivalent to December 30, 1899.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•