Select Query Fails on Date

[Rickinrsm]

I have a simple select query that runs perfectly when I place a date range like this in the criteria row of closeddate field; 11/??/2005. This returns all of the records for November 2005. It's great!

When I place a bracketed message in the criteria field ie. [Enter Date Range] and then place the exact date parameter shown above I get an error message that "The expression is incorrect or to complex."

Any ideas why it works the first way and not the second way?

Thanks much . . .

Rick ~ Access Newbie

 

[lupins46]

There isn't really any explanation.
It just doesn't work.
You cannot use wildcards in a parameter entry box.

Maybe you could use a date range in your query.

 

[dhookom]

I assume your field is a date/time field. You shouldn't treat it like a text field by using "Like 11/??/2005". Use various date and formatting functions to compare explicit values.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Rickinrsm]

Yes, a Date/Time field Short Format. mm/dd/yyyy.

It works perfectly if I place the same 11/??/2005 in the date field but it fails with the above mentioned error when I incorporate the [Enter Date Range] in the Criteria field.

I think I had it working once tonight.

This would be kewl if I could make it work again.

Thanks Much . . .

Rick ~ Access Newbie

 

[dhookom]

Consider explicitly converting your date values to a string using something like:

WHERE Format([DateField],"yyyymm") = Format([Enter a Date in the month],"yyyymm")

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[raskew]

Try this in the criteria cell:

If your date field does not contain times:
Between DateValue([enter mm/yyyy]) And DateAdd("m",1,DateValue([enter mm/yyyy]))-1

If your date field does contain times:
Between DateValue([enter mm/yyyy]) And DateAdd("m",1,DateValue([enter mm/yyyy]))

When prompted, enter 11/2005. Should return all Nov 05 records.

HTH - Bob