query brings up wrong dates

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

Guest

I have a query that is ran by selecting the beginning date and ending date:
=CDate([Start Date(mm/dd/yyyy)]) And <=CDate([End Date(mm/dd/yyyy)])
Click to expand...

Say I enter start date: 12/27/2005 and end date: 12/31/2005 my query selects
all records between those date but also pulls up 12/27/2004 - 12/31/2004.
How do I get it to not pull up the 2004 records?
 
Hi,
try:

BETWEEN CDate([Start Date(mm/dd/yyyy)]) AND CDate([End Date(mm/dd/yyyy)])

HTH
Good luck
--
Oliver
Admin Specialist & Computer Science Major @ UMD - Go Terps - :)


MMM said:
I have a query that is ran by selecting the beginning date and ending date:
=CDate([Start Date(mm/dd/yyyy)]) And <=CDate([End Date(mm/dd/yyyy)])
Click to expand...

Say I enter start date: 12/27/2005 and end date: 12/31/2005 my query selects
all records between those date but also pulls up 12/27/2004 - 12/31/2004.
How do I get it to not pull up the 2004 records?
Click to expand...
 
Is the field that this criteria is for defined as a Date/Time data type in
the table? Does the data in the field include the full date (month, day, and
year)? Will you post the SQL view of your query? Have you defined the
parameters as Dates? By this, I don't mean the CDate() function, but
instead, with the query open in design view go to Query|Parameters... on the
tool bar. Under parameter, enter each parameter just as you have then in
your statement and under Data Type, choose Date/Time. If you do this, you'll
probably find that you don't need the CDate() functions. Doing this will add
the following line to the top of the SQL for your query:

PARAMETERS [Start Date(mm/dd/yyyy)] DateTime, [End Date(mm/dd/yyyy)]
DateTime;
SELECT etc.
FROM etc.
WHERE etc.;

--
Wayne Morgan
MS Access MVP


MMM said:
I have a query that is ran by selecting the beginning date and ending date:
=CDate([Start Date(mm/dd/yyyy)]) And <=CDate([End Date(mm/dd/yyyy)])
Click to expand...

Say I enter start date: 12/27/2005 and end date: 12/31/2005 my query
selects
all records between those date but also pulls up 12/27/2004 - 12/31/2004.
How do I get it to not pull up the 2004 records?
Click to expand...
 
The data type was not Date/time. Thank you it works now. I can't believe it
was that simple. Thanks again.

Wayne Morgan said:
Is the field that this criteria is for defined as a Date/Time data type in
the table? Does the data in the field include the full date (month, day, and
year)? Will you post the SQL view of your query? Have you defined the
parameters as Dates? By this, I don't mean the CDate() function, but
instead, with the query open in design view go to Query|Parameters... on the
tool bar. Under parameter, enter each parameter just as you have then in
your statement and under Data Type, choose Date/Time. If you do this, you'll
probably find that you don't need the CDate() functions. Doing this will add
the following line to the top of the SQL for your query:

PARAMETERS [Start Date(mm/dd/yyyy)] DateTime, [End Date(mm/dd/yyyy)]
DateTime;
SELECT etc.
FROM etc.
WHERE etc.;

--
Wayne Morgan
MS Access MVP


MMM said:
I have a query that is ran by selecting the beginning date and ending date:
=CDate([Start Date(mm/dd/yyyy)]) And <=CDate([End Date(mm/dd/yyyy)])
Click to expand...

Say I enter start date: 12/27/2005 and end date: 12/31/2005 my query
selects
all records between those date but also pulls up 12/27/2004 - 12/31/2004.
How do I get it to not pull up the 2004 records?
Click to expand...
Click to expand...
 
I have a query that is ran by selecting the beginning date and ending date:
=CDate([Start Date(mm/dd/yyyy)]) And <=CDate([End Date(mm/dd/yyyy)])
Click to expand...

Say I enter start date: 12/27/2005 and end date: 12/31/2005 my query selects
all records between those date but also pulls up 12/27/2004 - 12/31/2004.
How do I get it to not pull up the 2004 records?
Click to expand...

That's VERY peculiar. Is the field that you're searching in fact a
Date/Time field or is it - as I'm guessing - a Text field? As *text
strings*, "12/30/1844" is in fact greater than "12/27/2004" - because
the first character different is 3 vs. 2.

Check the datatype of the table field.

John W. Vinson[MVP]
 
Back
Top