General discussion

Locked

Date query in Access2000

By ahensler ·
I have table column with dates using the short date format.
When I query the table using <Now() as my criteria It returns only dates with months 1 - 9. Any date greater than 10/1/yy is not found.

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Date query in Access2000

by sl-campbell In reply to Date query in Access2000

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:

<DateValue(Now())

Or, in code:

"...WHERE DateValue(myDateField)<DateValue(Now())"

Or, again, in the query builder:

Field= ExprDate: DateValue([myDateField])
Criteria= <DateValue(Now())

If this doesn't help, then please give more info in a comment to your question, (with-out rejecting this answer), and I will post a comment back.

Info needed:
Is the field type a true Date field or a text field with a date?

Are you writing a query in code or using the Query builder?

If in code, could you please show the complete criteria?

Is your system's date set to the US format?
(under certain circumstances, if the system is set to a format that's not the US format, esp. if this is done in code, you will need to format the criteria to US format first)

What format is the Short date format in?
(go to the Country Settings in the System panel and look under date.

Collapse -

Date query in Access2000

by ahensler In reply to Date query in Access2000

The question was auto-closed by TechRepublic

Collapse -

Date query in Access2000

by Bob Sellman In reply to Date query in Access2000

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.

Collapse -

Date query in Access2000

by ahensler In reply to Date query in Access2000

The question was auto-closed by TechRepublic

Collapse -

Date query in Access2000

by ahensler In reply to Date query in Access2000

This question was auto closed due to inactivity

Back to Software Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums