By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,400 Members | 981 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,400 IT Pros & Developers. It's quick & easy.

date range for report query

P: 32
Hello all,

I have a timesheet details table . I am trying to run the following query against it.
Expand|Select|Wrap|Line Numbers
  1. SELECT [timesheetdetails].[Timesheetid], [timesheetdetails].[Totalhours]
  2. FROM timesheetdetails
  3. WHERE Format([date],'dd/mm/yyyy') between '01/01/2007' and '01/12/2007';
  4.  
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
Expand|Select|Wrap|Line Numbers
  1. SELECT [timesheetdetails].[Timesheetid], [timesheetdetails].[Totalhours]
  2. FROM timesheetdetails
  3. WHERE Format([date],'dd/mm/yyyy') >= '01/01/2007' ;
  4.  
then I get all the records. But the inute I put in an upper boundary , for example

Expand|Select|Wrap|Line Numbers
  1. SELECT [timesheetdetails].[Timesheetid], [timesheetdetails].[Totalhours]
  2. FROM timesheetdetails
  3. WHERE Format([date],'dd/mm/yyyy') >= '01/01/2007' and 
  4. Format([date],'dd/mm/yyyy') <= '01/01/2008' ;
  5.  
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
Aug 14 '07 #1
Share this Question
Share on Google+
1 Reply


FishVal
Expert 2.5K+
P: 2,653
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?

Expand|Select|Wrap|Line Numbers
  1. SELECT [timesheetdetails].[Timesheetid], [timesheetdetails].[Totalhours]
  2. FROM timesheetdetails
  3. WHERE [date] between #01/01/2007# and #12/01/2007#;
  4.  
or

Expand|Select|Wrap|Line Numbers
  1. SELECT [timesheetdetails].[Timesheetid], [timesheetdetails].[Totalhours]
  2. FROM timesheetdetails
  3. WHERE Year([date]) = 2007;
  4.  
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.
Aug 14 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.