Date format in a query

J

Jacob

I have a query that is opening only certain records. I now would like to
see it only open records with in the current year. Or dates entered for a
specific year. The criteria I am use is this..... Year(Date()) but it
returns no records. I am running Access 2k and am not sure why it is not
working. Any help would be greatly appreciated.
 
D

Douglas J. Steele

You say that the criteria is Year(Date()), but what's it the criteria
against? If it's against a date field, change it to BETWEEN
DateSerial(Year(Date()), 1, 1) AND DateSerial(Year(Date()), 12, 31), or else
add a computed field to your query Year([MyDateField]), and put the criteria
against that field, rather than against [MyDateField]

(The former should be more efficient, as it won't have to run the Year
function against each row in the table)
 
L

Lynn Trapp

SELECT Field1, Field2, YourDateField
FROM YourTable
WHERE Year(YourDateField) = Year(Date());
 
J

Jacob

Doug, this did it. You are da man!!!

Douglas J. Steele said:
You say that the criteria is Year(Date()), but what's it the criteria
against? If it's against a date field, change it to BETWEEN
DateSerial(Year(Date()), 1, 1) AND DateSerial(Year(Date()), 12, 31), or else
add a computed field to your query Year([MyDateField]), and put the criteria
against that field, rather than against [MyDateField]

(The former should be more efficient, as it won't have to run the Year
function against each row in the table)

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


Jacob said:
I have a query that is opening only certain records. I now would
Click to expand...
like
to
see it only open records with in the current year. Or dates entered for a
specific year. The criteria I am use is this..... Year(Date()) but it
returns no records. I am running Access 2k and am not sure why it is not
working. Any help would be greatly appreciated.
Click to expand...
Click to expand...
 
Top