General Date in query

  • Thread starter Thread starter ladybug via AccessMonster.com
  • Start date Start date
L

ladybug via AccessMonster.com

Can you not set an open criteria in a query for a field set to a General Date?

When I run my query with no criteria, entries are returned.
When I put [Enter Date] in the Criteria nothing is returned. I have checked
the date to make sure it was just not my error, but the date is correct.
I have also tried Like "*" &[Enter Date]& "*"
This did not work either. Any thoughts of what I am doing wrong or if I
cannot search this way?
 
POST your SQL. (View: SQL in design view)

Try
CDate([Enter Date])

Access may not be understanding your input as a date. It could be changing
11/07/2007 to a small fraction (11 divided by 7 divided by 2007) which the
date field will then see as a date and time on December 20, 1899.

Another way to handle this is to declare the parameter

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2

Now Access will know that [Enter Date] is a datetime and not a string or a
expression to be calculated.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
On top of what John said, it might be converting your date into American date
format - are you trying to put in 11 July 2007, or 7 November 2007?

Of course, if you're in America then that's not the problem :)


John Spencer said:
POST your SQL. (View: SQL in design view)

Try
CDate([Enter Date])

Access may not be understanding your input as a date. It could be changing
11/07/2007 to a small fraction (11 divided by 7 divided by 2007) which the
date field will then see as a date and time on December 20, 1899.

Another way to handle this is to declare the parameter

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2

Now Access will know that [Enter Date] is a datetime and not a string or a
expression to be calculated.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

ladybug via AccessMonster.com said:
Can you not set an open criteria in a query for a field set to a General
Date?

When I run my query with no criteria, entries are returned.
When I put [Enter Date] in the Criteria nothing is returned. I have
checked
the date to make sure it was just not my error, but the date is correct.
I have also tried Like "*" &[Enter Date]& "*"
This did not work either. Any thoughts of what I am doing wrong or if I
cannot search this way?
Click to expand...
Click to expand...
 
Back
Top