Date criteria not inclusive 1

[Trudye]

I added the following criteria to a date field within a query. I am getting all of the dates I requested except for the last day.

Rpt_start_date = 09/01/2005
enddate = 03/31/2006

I am getting all recs up to and including 03/30/2006. Any ideas why I am not getting 03/31/2006 and how I can fix it?
I put this same criteria in a prev query and it worked just fine (same date field was queried).

>=[tblMOR_Dates].[Rpt_start_date] And <=[tblMOR_Dates].[enddate]

Thanx
Trudye

 

[abaldwin]

Can you post your entire sql statement please?

Are rpt_start....variables in code or fields in a table?

More info please.

Thanks


Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[Golom]

Most likely the field is being populated with Now() and not with Date(). That means that the fractional part (i.e. the time) is non-zero. Your BETWEEN dates expand into values with "00:00:00" as the time (i.e. midnight). If you change your query to

>=[tblMOR_Dates].[Rpt_start_date] And [b][COLOR=red]<[/color][/b][tblMOR_Dates].[enddate] [COLOR=red]+ 1[/color]

then it will pick up any dates that are before midnight of the following day including those non-zero times.

[small]On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. (Charles Babbage)[/small]

 

[Trudye]

Thanks so much Guys for responding so quickly.

abaldwin: Both dates (rRpt_start_date and enddate) reside on a table.

Golom: I considered adding +1 to the enddate but was afraid the query would start working properly (as the other query does where I use the same criteria against enddate) at a later date and I would pull in the an extra day.

I guess I'll have to take that chance.

Thanks again,
Trudye

 

[abaldwin]

Okay, So can you post your entire sql statement.

Thanks


Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[Golom]

Trudye

No it won't start picking up an extra day. Note that I not only added a day to the test ... I also changed <= (less than or equal) to < (less than) so the meaning changes from "on or before that date" to just "before that date".

[small]On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. (Charles Babbage)[/small]

 

[Trudye]

It is working GREAT now! Thanks to much to both of you.