date range for report query

[ramaswamynanda]

Hello all,

I have a timesheet details table . I am trying to run the following query against it.

SELECT [timesheetdetails].[Timesheetid], [timesheetdetails].[Totalhours]
FROM timesheetdetails
WHERE Format([date],'dd/mm/yyyy') between '01/01/2007' and '01/12/2007';
 

There is data in the table corresponding to all months. This query returns nothing.

Very strangely, the lower condition works. For example, if I change the query to

SELECT [timesheetdetails].[Timesheetid], [timesheetdetails].[Totalhours]
FROM timesheetdetails
WHERE Format([date],'dd/mm/yyyy') >= '01/01/2007' ;
 

then I get all the records. But the inute I put in an upper boundary , for example

SELECT [timesheetdetails].[Timesheetid], [timesheetdetails].[Totalhours]
FROM timesheetdetails
WHERE Format([date],'dd/mm/yyyy') >= '01/01/2007' and 
Format([date],'dd/mm/yyyy') <= '01/01/2008' ;
 

I get no records.

Please help me. I need this urgently for a report. I have searched this site ad also the web for solutions. the syntax for the query is correct. why doesnt it work?

Thanks in advance
Nanda

[FishVal]

Hello all,

I have a timesheet details table . I am trying to run the following query against it.
SELECT [timesheetdetails].[Timesheetid], [timesheetdetails].[Totalhours]
FROM timesheetdetails
WHERE Format([date],'dd/mm/yyyy') between '01/01/2007' and '01/12/2007';

There is data in the table corresponding to all months. This query returns nothing.

Very strangely, the lower condition works. For example, if I change the query to
SELECT [timesheetdetails].[Timesheetid], [timesheetdetails].[Totalhours]
FROM timesheetdetails
WHERE Format([date],'dd/mm/yyyy') >= '01/01/2007' ;

then I get all the records. But the inute I put in an upper boundary , for example

SELECT [timesheetdetails].[Timesheetid], [timesheetdetails].[Totalhours]
FROM timesheetdetails
WHERE Format([date],'dd/mm/yyyy') >= '01/01/2007' and
Format([date],'dd/mm/yyyy') <= '01/01/2008' ;

I get no records.

Please help me. I need this urgently for a report. I have searched this site ad also the web for solutions. the syntax for the query is correct. why doesnt it work?

Thanks in advance
Nanda

Hi, Nanda.

You try to compare dates as strings but the rules of string comparing are quite different then that for dates.
Example

? "01/01/2007" > "02/01/2001"
False
? #01/01/2007# > #02/01/2001#
True

What's wrong with direct date comparing?

SELECT [timesheetdetails].[Timesheetid], [timesheetdetails].[Totalhours]
FROM timesheetdetails
WHERE [date] between #01/01/2007# and #12/01/2007#;
 

or

SELECT [timesheetdetails].[Timesheetid], [timesheetdetails].[Totalhours]
FROM timesheetdetails
WHERE Year([date]) = 2007;
 

or if you input date somewhere in form/dialog in dd/mm/yyyy format you may need to use DateSerial function to return date value

Anyway particular solution depends on how do you get criteria dates, in what format and how do you pass them to the query.