DatePart problem with query

J

Jay

Need help

I have tried datePart in several ways put can't
get it to work right.

I have used the date format in my table field
and this criteria fails to work. It works with
text format but not with short date format

<DatePart("m",Date())


What I am trying to do is select all dates that
are less than the current month.
 
M

Marshall Barton

Jay said:
I have tried datePart in several ways put can't
get it to work right.

I have used the date format in my table field
and this criteria fails to work. It works with
text format but not with short date format

<DatePart("m",Date())

What I am trying to do is select all dates that
are less than the current month.
Click to expand...


You can't realistically compare a date value to a number
between 1 and 12.

Try using the criteria:

< DateSerial(Year(Date()), Month(Date()), 1)
 
J

Jay

Marshall said:
You can't realistically compare a date value to a number
between 1 and 12.

Try using the criteria:

< DateSerial(Year(Date()), Month(Date()), 1)
Click to expand...

Thank you. It worked.
 
J

John Vinson

Need help

I have tried datePart in several ways put can't
get it to work right.

I have used the date format in my table field
and this criteria fails to work. It works with
text format but not with short date format

<DatePart("m",Date())


What I am trying to do is select all dates that
are less than the current month.
Click to expand...

DatePart returns an *INTEGER* - 1 for January, 12 for December, and so
on - not a date. Date fields are stored as a Double Float number, a
count of days since midnight, December 30, 1899; your criterion will
select records where the date is prior to January 11, 1900 if run in
December.

To get all dates prior to the beginning of the current month, use a
criterion of

< DateSerial(Year(Date()), Month(Date()), 1)

John W. Vinson[MVP]
 
Top