parameter/wildcard w/dates

CrazyKillerMan

Registered User.
Local time
Today, 15:37
Joined
Nov 26, 2002
Messages
23
Hello there...

Searching through the forum I have come close, but not totally correct yet as to the syntax I need to use.

tbl_1 has dates stored in medium date.
I have a query set up to do many aggregate functions on the results, but before I do that, I want to specify a date range.
The date range I need to specify is yearly.
ie: from 1-1-2002 to 31-12-2002
Now, I need it to dynamicaly update the year, but not the month and day.
This is what I have used that gives me a data type mismatch:
Between '1-1-' & Format('yy',Now()) And '31-12-' &(Format('yy',Now()))

Is there any way to do this more elegant?

I've tried:

Between Format('1-1-yy',Now()) And Format('12-31-yy',Now()))) and that does not work either.

Any ideas? Thank-y much
 
Try this setting in a column in the query grid, using the correct date field name:

Field: Year([DateFieldName])
Show: uncheck
Criteria: Year(Date())
 
Kick ass!


Between '1-1-' & Year(Date()) And '12-31-' & Year(Date())

Jon K - thanks!!!

One question: Why is it that Year(Date()) works and not Format('yy', Now()) ?
 
Last edited:
You placed the two arguments in a wrong order in the Format() function. To return the year in two digits, you should have used
Format(Now(),"yy")

The Year() function returns the year in 4 digits. Now() returns the system date and time. Date() returns only the date.
 

Users who are viewing this thread

Back
Top Bottom