Between "" And "" is truncating last date

[mamasonflowa]

In the criteria of a query I have the following listed on my Date column:

Between [Start Date] and [End Date]

The table I'm running off of has 1060 records in the month of January. But when I run the Query and put in 01/01/2011 then 01/31/2011, it truncates the last 60 records that are all 01/31/2011 records. Why?

 

[jdraw]

How are Start Date and End Date defined?
What is the full SQL of the query?

Why do you have spaces/blanks in your field names?

 

[mamasonflowa]

I have one field named CDate. The table that the query is running off of has 1900 records in it spanning November, December and January. I only want the month of January in my report. But the Between statement keeps truncating the last day of each month. I tried it on December and it did the same thing.

 

[jdraw]

Please show the query that you're using .

You must use # to delimit dates in Access

Between #1/1/2011# AND #1/31/2011#

 

[boblarson]

Sounds like you aren't just capturing the date but also time. If you do that then you have to add a day to the end to get all records between midnight of the last day and midnight of the day after.

 

[mamasonflowa]

Bob, my excel spreadsheet that I'm importing all the data into the table from, does have the date/time version. So I reformatted it and loaded it in to the table. I got the same thing, no 01/31/2011 dates.

jDraw, when you ask for my query, do you me to attach something here?

 

[ChrisO]

>>So I reformatted it and loaded it in to the table.<<

That perhaps needs some explanation.

Try thinking carefully along these lines…
If you reformat a date/time field to remove the time component and then decide to reformat the date/time field to include the time component; where does the time component come from?

Chris.

 

[mamasonflowa]

Chris, using the right terminology has been difficult for me. I'm a pretty new user of Access and finding out that words like "Link" mean something different than it does in Excel, has been rough.

What I meant on the reformat is I reformatted the cell in Excel to mm/dd/yyyy. Then imported the excel spreadsheet back into the dBase. The properties for that field in the table show "ShortDate" under the format. However it is still truncating the last day of each month that I pull in.

 

[Brianwarnock]

As has been explained the format does not remove the Time elememt, remember the date/time is stored as a number.
If you don't believe me go into the table and type in the short date field a date and time, when you view it you only see the date but put your cursor in the the field and lo and behold you can see the time, so either do as Bob said or add the more correct 23:59:59

or use Datevalue on the field.

Brian

 

[mamasonflowa]

OMG, you are right, it is still there. Wow! Okay, let me try. Thank you!

 

[boblarson]

One way is:

Between [Start Date] + #12:00:00 AM# And [End Date] + #11:59:59#

 

[mamasonflowa]

Bob, Here is the error I just got after typing in the last expression

The espression is typed incorrectly or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplfying the expression by assigning part of the expression to variables.

 

[Brianwarnock]

Just do

Between [Start Date] And [End Date] + #23:59:59#

as the time will default to 00:00:00 when not entered

 

[mamasonflowa]

Just do

Between [Start Date] And [End Date] + #23:59:59#

as the time will default to 00:00:00 when not entered

I got the same error:

"The espression is typed incorrectly or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplfying the expression by assigning part of the expression to variables."

 

[mamasonflowa]

Hallelujah! It got all my data. I changed the properties in the table and the query on the CDate field to General Date. Then I changed the properties in the report to Short and SHAZAM! I did have to still put in the 02/01/2011 date instead of the last day of January. Puzzling and maybe there is another reason. I would like to get it fixed (be 24karat) before I have to run the report for February's data. But for now, I'm 14karat.

 

[boblarson]

I got the same error:

"The espression is typed incorrectly or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplfying the expression by assigning part of the expression to variables."

The question I would have to that is how are you entering the dates when the parameter prompt appeared?

And if you still had to use 2/1/2011 then there is still a time element in place. Once you have dates in a date/time field you can't just change it to Short Date as the existing records will still have the time element included. So, you would have to do an update query to remove it.

 

[Brianwarnock]

The question I would have to that is how are you entering the dates when the parameter prompt appeared?

.

I always forget about this as I use forms to drive my queries, if using prompts for the parameters, then in query design click on query - parameters and then enter the parameter name and select the format.

Brian

 

[DCrake]

as a postscript to this post using CDate as the name of a field is bad news. CDate() is an Access function and could raise issues later.

 

[Brianwarnock]

Good catch David.

Brian