SQL Statement Help 1

[jeff1]

I have a SQL Statement, within the statement I have a fairly simple conidtion as follows:

WHERE (((Work.date)>=[Forms]![Monthly QC Report]![BeginningDate] And (Work.date)<=[Forms]![Monthly QC Report]![EndDate]));

This should give me an output of data not only between the the range of dates but include the date as well.

My problem is that the <=[EndDate] does not output the = to date. This works fine for the [beginningdate].

Can anyone help w/ why I cannot get an the output to include records = to the [EndDate] I can modify the statement to only output records = to the [EndDate], and still nothing. I have verified the format of the [Enddate] by outputting the statement to another feild and it appears to match exactly.

Thanks in advance for your time and help.
Jeff

 

[rbowes]

Why don't you try Between...And in your WHERE Clause?

WHERE (((Work.date)Between [Forms]![Monthly QC Report]![BeginningDate] And [Forms]![Monthly QC Report]![EndDate]))

Between...And is inclusive. Check it in a query using the QBE and just to make sure it returns the right records.

Hope that helps.

Bob

 

[jeff1]

Bob,

I have tried that same method and I get exactly what the statement says, I get records between the 2 dates not to include either date. Thats why I am resorting to use the >= and <= conditions. I just can't see anything wrong w/ the statement that I have it seems to make sence logically.

 

[Janoh]

I found it in SQL server, but maybe in access is the same problem.
The field is datetime and if you have no time specified it sets it to 00:00 by default and if you save your date with time too this might be the problem.

i.e. 01.01.2001 21:56 <= 01.01.2001 00:00

Look after it, maybe this might be. Try setting the )<=[Forms]![Monthly QC Report]![EndDate] with time 23:59.

Repeat, I found this in MS-SQLServer, I'm not that familiar with access.

 

[rbowes]

Sorry that I didn't get back to you sooner. I think that Janoh is onto something. Try this:

WHERE CDate(((Work.date))Between [Forms]![Monthly QC Report]![BeginningDate] And [Forms]![Monthly QC Report]![EndDate]))


This assumes that the Work.Date field in the table is a Date/Time field and holds a time value along with the date value.

Hope that helps you out.

Bob