Date query and previous year

G

Guest

Hello all,

I have this query which works fine when the start date is 1/1/2007 and end
date of 7/6/2007. When I use a start date of 12/31/2006 and end date of
7/6/2007, it returns no records.

The Received field is coming from a linked exchange file/inbox with the date
and time in the field. I tried to parse out just the date with the
Format(..... stuff.

What is wrong with my query?

SELECT qry_Call_Routing_Team_All.*, tbl_Status.*
FROM tbl_Status RIGHT JOIN qry_Call_Routing_Team_All ON tbl_Status.UniqueID
= qry_Call_Routing_Team_All.UniqueID
WHERE (((Format([Received],"mm/dd/yyyy")) Between
Format([Forms]![frm_Report_Menu].[txtStartDate],"mm/dd/yyyy") And
Format([Forms]![frm_Report_Menu].[txtEndDate],"mm/dd/yyyy")));

Any insight you can provide is appreciated.
 
G

Guest

Try this --
SELECT qry_Call_Routing_Team_All.*, tbl_Status.*
FROM tbl_Status RIGHT JOIN qry_Call_Routing_Team_All ON tbl_Status.UniqueID
= qry_Call_Routing_Team_All.UniqueID
WHERE [Received] Between CVDate([Forms]![frm_Report_Menu]![txtStartDate])
And CVDate([Forms]![frm_Report_Menu]![txtEndDate]);
 
J

John Spencer

DON'T FORMAT THE DATE. When you do that you turn the date into a text
string.


SELECT qry_Call_Routing_Team_All.*, tbl_Status.*
FROM tbl_Status RIGHT JOIN qry_Call_Routing_Team_All ON tbl_Status.UniqueID
= qry_Call_Routing_Team_All.UniqueID
WHERE [Received] Between
[Forms]![frm_Report_Menu]![txtStartDate] And
[Forms]![frm_Report_Menu]![txtEndDate]

If that fails you can use CDate around the parameters or declare the
parameters at the beginning of the query to ensure they are understood
as dates.

Parameters Forms]![frm_Report_Menu]![txtStartDate] DateTime,
[Forms]![frm_Report_Menu]![txtEndDate] DateTime;
SELECT qry_Call_Routing_Team_All.*, tbl_Status.*
FROM tbl_Status RIGHT JOIN qry_Call_Routing_Team_All ON tbl_Status.UniqueID
= qry_Call_Routing_Team_All.UniqueID
WHERE [Received] Between
[Forms]![frm_Report_Menu]![txtStartDate] And
[Forms]![frm_Report_Menu]![txtEndDate]

Or as an alternative

SELECT qry_Call_Routing_Team_All.*, tbl_Status.*
FROM tbl_Status RIGHT JOIN qry_Call_Routing_Team_All ON tbl_Status.UniqueID
= qry_Call_Routing_Team_All.UniqueID
WHERE [Received] Between
CDate([Forms]![frm_Report_Menu]![txtStartDate]) And
CDate([Forms]![frm_Report_Menu]![txtEndDate])


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

Guest

Karl,
Thank you for your response. It worked and resolved my orginal issue,
however something else has come up. The query does not select records where
[Received] equals [Forms]![frm_Report_Menu]![txtEndDate]

Value details
Field [Received] is a Date and Time
field [Forms]![frm_Report_Menu]![txtStartDate] is a Date
field [Forms]![frm_Report_Menu]![txtEndDate] is a Date

If the field [Forms]![frm_Report_Menu]![txtStartDate] value is yesterday and

[Forms]![frm_Report_Menu]![txtEndDate] value is today...

the query only returns the records where [Received] is yesterday.


Here is the query I'm using

SELECT qry_Call_Routing_Team_All.*, tbl_Status.*
FROM tbl_Status RIGHT JOIN qry_Call_Routing_Team_All ON tbl_Status.UniqueID
= qry_Call_Routing_Team_All.UniqueID
WHERE [Received] Between CVDate([Forms]![frm_Report_Menu]![txtStartDate])
And CVDate([Forms]![frm_Report_Menu]![txtEndDate]);

I am trying to get it to select the records where [Received] = startdate and
[Received] = enddate... and everything inbetween.

Any toughts about how to re-work the query or should I just put a hidden
text box called txtEndDate2 and have it be txtEndDate + 1 day, and query from
the txtEndDate2 field?

Thank you for your assistance.

David






KARL DEWEY said:
Try this --
SELECT qry_Call_Routing_Team_All.*, tbl_Status.*
FROM tbl_Status RIGHT JOIN qry_Call_Routing_Team_All ON tbl_Status.UniqueID
= qry_Call_Routing_Team_All.UniqueID
WHERE [Received] Between CVDate([Forms]![frm_Report_Menu]![txtStartDate])
And CVDate([Forms]![frm_Report_Menu]![txtEndDate]);

--
KARL DEWEY
Build a little - Test a little


David said:
Hello all,

I have this query which works fine when the start date is 1/1/2007 and end
date of 7/6/2007. When I use a start date of 12/31/2006 and end date of
7/6/2007, it returns no records.

The Received field is coming from a linked exchange file/inbox with the date
and time in the field. I tried to parse out just the date with the
Format(..... stuff.

What is wrong with my query?

SELECT qry_Call_Routing_Team_All.*, tbl_Status.*
FROM tbl_Status RIGHT JOIN qry_Call_Routing_Team_All ON tbl_Status.UniqueID
= qry_Call_Routing_Team_All.UniqueID
WHERE (((Format([Received],"mm/dd/yyyy")) Between
Format([Forms]![frm_Report_Menu].[txtStartDate],"mm/dd/yyyy") And
Format([Forms]![frm_Report_Menu].[txtEndDate],"mm/dd/yyyy")));

Any insight you can provide is appreciated.
Click to expand...
Click to expand...
 
G

Guest

This strips the decimal time from the date.

SELECT qry_Call_Routing_Team_All.*, tbl_Status.*
FROM tbl_Status RIGHT JOIN qry_Call_Routing_Team_All ON tbl_Status.UniqueID
= qry_Call_Routing_Team_All.UniqueID
WHERE CVDate(Int([Received])) Between
CVDate([Forms]![frm_Report_Menu]![txtStartDate]) And
CVDate([Forms]![frm_Report_Menu]![txtEndDate]);

--
KARL DEWEY
Build a little - Test a little


David said:
Karl,
Thank you for your response. It worked and resolved my orginal issue,
however something else has come up. The query does not select records where
[Received] equals [Forms]![frm_Report_Menu]![txtEndDate]

Value details
Field [Received] is a Date and Time
field [Forms]![frm_Report_Menu]![txtStartDate] is a Date
field [Forms]![frm_Report_Menu]![txtEndDate] is a Date

If the field [Forms]![frm_Report_Menu]![txtStartDate] value is yesterday and

[Forms]![frm_Report_Menu]![txtEndDate] value is today...

the query only returns the records where [Received] is yesterday.


Here is the query I'm using

SELECT qry_Call_Routing_Team_All.*, tbl_Status.*
FROM tbl_Status RIGHT JOIN qry_Call_Routing_Team_All ON tbl_Status.UniqueID
= qry_Call_Routing_Team_All.UniqueID
WHERE [Received] Between CVDate([Forms]![frm_Report_Menu]![txtStartDate])
And CVDate([Forms]![frm_Report_Menu]![txtEndDate]);

I am trying to get it to select the records where [Received] = startdate and
[Received] = enddate... and everything inbetween.

Any toughts about how to re-work the query or should I just put a hidden
text box called txtEndDate2 and have it be txtEndDate + 1 day, and query from
the txtEndDate2 field?

Thank you for your assistance.

David






KARL DEWEY said:
Try this --
SELECT qry_Call_Routing_Team_All.*, tbl_Status.*
FROM tbl_Status RIGHT JOIN qry_Call_Routing_Team_All ON tbl_Status.UniqueID
= qry_Call_Routing_Team_All.UniqueID
WHERE [Received] Between CVDate([Forms]![frm_Report_Menu]![txtStartDate])
And CVDate([Forms]![frm_Report_Menu]![txtEndDate]);

--
KARL DEWEY
Build a little - Test a little


David said:
Hello all,

I have this query which works fine when the start date is 1/1/2007 and end
date of 7/6/2007. When I use a start date of 12/31/2006 and end date of
7/6/2007, it returns no records.

The Received field is coming from a linked exchange file/inbox with the date
and time in the field. I tried to parse out just the date with the
Format(..... stuff.

What is wrong with my query?

SELECT qry_Call_Routing_Team_All.*, tbl_Status.*
FROM tbl_Status RIGHT JOIN qry_Call_Routing_Team_All ON tbl_Status.UniqueID
= qry_Call_Routing_Team_All.UniqueID
WHERE (((Format([Received],"mm/dd/yyyy")) Between
Format([Forms]![frm_Report_Menu].[txtStartDate],"mm/dd/yyyy") And
Format([Forms]![frm_Report_Menu].[txtEndDate],"mm/dd/yyyy")));

Any insight you can provide is appreciated.
Click to expand...
Click to expand...
Click to expand...
 
J

Jamie Collins

This strips thedecimaltime from the date.

SELECT qry_Call_Routing_Team_All.*, tbl_Status.*
FROM tbl_Status RIGHT JOIN qry_Call_Routing_Team_All ON tbl_Status.UniqueID
= qry_Call_Routing_Team_All.UniqueID
WHERE CVDate(Int([Received])) Between
CVDate([Forms]![frm_Report_Menu]![txtStartDate]) And
CVDate([Forms]![frm_Report_Menu]![txtEndDate]);
Click to expand...

If you are using BETWEEN then I suggest you 'round' your end date
values to the last time granule e.g.

WHERE Received BETWEEN
DATEADD('D', DATEDIFF('D', #1990-01-01 00:00:00#, [Forms]!
[frm_Report_Menu]![txtStartDate]), #1990-01-01 00:00:00#)
AND
DATEADD('D', DATEDIFF('D', #1990-01-01 00:00:00#, [Forms]!
[frm_Report_Menu]![txtEndDate]), #1990-01-01 23:59:59#);

Jamie.

--
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Formatting date gives bad results in query 2
Date format in query export. 4
query on date 1
Date Calculation 1
Delete Query 6
Convert Date 3
Date Calculation 1
convert a date 4

Top