Date criteria in query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi Everyone,
I have a date field DD/MM/YYYY HH:mm:SS in a table. I want to select rows,
in a query, based on "between" StartDate and EndDate. Frustration!
I've built parameters for the two, and on execution the query prompts me for
Startdate and Enddate parameters but I keep getting "Data Type mismatch"
errors.
I've simplified the query criteria to just = "Startdate" but keep getting
the same problem.
All help appreciated
Pat.
 
Try to convert the paameters to date

Select * From TableName Where FieldName Between CVDate([Start Date]) And
CVDate([End Date])
 
If you open the table in design view, does it say:
Date/Time
in the middle column beside this field?

If so, the query will understand it as a date, so you then need to get the
query to understand the 2 parameters as dates as well. To do that choose
Parameters on the Query menu (in query design view.) Access opens a dialog.
Enter 2 rows:
StartDate Date/Time
EndDate Date/Time

Now it should understand both the field and the parameters as dates, and
there will be no more "mismatch."
 
Hi Allen (and Ofer)
I've tried both your suggestions. I had previously tried the parameters.
To make it easier for this debug I made the criteria just [Startdate] -
Paramater of type date/time. I enter the exact date of records in the table
and it returns 0 records now. The T_When column I am selecting is defined as
date (DD/MM/YYYY HH:mm:SS)
The query runs now but returns no records? Must enter the time portion, as
well?
Any ideas?
Thanks again, Allen and Ofer
 
I don't think the time should make a different, you can try this

Select * From TableName Where DateValue(FieldName) Between CVDate([Start
Date]) And
CVDate([End Date])

The DateValue function will remove the time

--
Good Luck
BS"D


Pat Backowski said:
Hi Allen (and Ofer)
I've tried both your suggestions. I had previously tried the parameters.
To make it easier for this debug I made the criteria just [Startdate] -
Paramater of type date/time. I enter the exact date of records in the table
and it returns 0 records now. The T_When column I am selecting is defined as
date (DD/MM/YYYY HH:mm:SS)
The query runs now but returns no records? Must enter the time portion, as
well?
Any ideas?
Thanks again, Allen and Ofer

Allen Browne said:
If you open the table in design view, does it say:
Date/Time
in the middle column beside this field?

If so, the query will understand it as a date, so you then need to get the
query to understand the 2 parameters as dates as well. To do that choose
Parameters on the Query menu (in query design view.) Access opens a dialog.
Enter 2 rows:
StartDate Date/Time
EndDate Date/Time

Now it should understand both the field and the parameters as dates, and
there will be no more "mismatch."
Click to expand...
Click to expand...
 
Perhaps your date/time field includes a time as well as the date? If so,
searching for just the date would return only those records that are exactly
at midnight.

To return all records for a date, ask Access for everything from the date to
less than the next day:
= [startdate] And < [startdate] + 1
Click to expand...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Pat Backowski said:
Hi Allen (and Ofer)
I've tried both your suggestions. I had previously tried the parameters.
To make it easier for this debug I made the criteria just [Startdate] -
Paramater of type date/time. I enter the exact date of records in the
table
and it returns 0 records now. The T_When column I am selecting is defined
as
date (DD/MM/YYYY HH:mm:SS)
The query runs now but returns no records? Must enter the time portion,
as
well?
Any ideas?
Thanks again, Allen and Ofer

Allen Browne said:
If you open the table in design view, does it say:
Date/Time
in the middle column beside this field?

If so, the query will understand it as a date, so you then need to get
the
query to understand the 2 parameters as dates as well. To do that choose
Parameters on the Query menu (in query design view.) Access opens a
dialog.
Enter 2 rows:
StartDate Date/Time
EndDate Date/Time

Now it should understand both the field and the parameters as dates, and
there will be no more "mismatch."

message
Click to expand...
Click to expand...
 
Hi Allen and Ofer,
All your advice was good and I learned all about selecting dates - really
like the tip about using +1 if hhmmss involved.
The big problem was I was trying to select, with a date filter, rows via a
dbo link to a Sql Server database. Somehow the dates don't work the same.
So I did the other filters first and then the dates aginst the local table
created above.
A lesson learned -worth the price.
Cheers lads,
Pat
 
Back
Top