Software

Ask something
Discussion Locked

Date query in Access2000

0 helpful
5 replies

by ahensler

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have table column with dates using the short date format.
When I query the table using

5 replies

by sl-campbell

This may sound strange, but is the column/field a date field or a text field?

The problem here could be that if the field is a text field then you may be comparing a date, (01/12/01), with a date/time, (Now = 01/12/01 08:10:30).

In Code for arecordset object try formatting:
"...WHERE myDateField<" & Format$(Now(),"\#mm\/dd\/yyyy\#")

Or, as the criteria in the Query builder or in code you could try:

by bob sellman

You don't mention if you have created the query with the Access query builder screen or if it is part of VBA code.

For the query builder screen I would suggest using Date() rather than Now(), since you apparently only are interested in the date comparison. In the query builder grid, right click on the criteria cell, select build, then the common expressions and today's date. Sometimes Access gets wierd about recognizing that an expression you just type in is really the expression (i.e., your typed in "Date()" isn't recognized as the function "Date()").

If you are using VBA code to build the where clause, build the where clause with the following syntax:

...."WHERE datecolumn < #" & Date() & "#"

Without the # signs surroundingthe Date() value, the Date() will be treated as text for your criteria.

This discussion is closed to new replies.