Confused

[racdata]

I've got a message box asking me for start date and end date
When I enter start date 01-08-03 to 31-08-2003 it display 115 records from 01-08-2003 to 30-8-2003. If I count manually the records for August there are 126 records.
It only display all records for August if I enter 01-8-2003 to 01-09-2003.

On the report header it display "01-08-2003" (Date From) to "31-08-2003" (Date To) and it confuse me because the report give me the records from 01-08-2003 to 30-08-2003 The dates I enter in the message box

It leaves out the records for the last date you enter in the message box. To get all records for August I enter 01-08-2003 to 01-09-2003 and on the report header it display the same dates 01-08-2003 to 01-09-2003

 

[pcEars]

Probably due to the date being stored as both date and time (which Access does) when the field uses the Now() function.

If you change it to Date() it should clear up. If that's not an option, then try using the Format() funtion in your query to reformat the output to return just what you want.

hope I've given you enough to go on. If not, I'm sure one of the more experience folks on board will clarify

 

[neileg]

PCEars is half way there.

Access does indeed store date and time even if you don't ask it to. The problem with your end date can be illustrated as follows.

Access stores dates as whole numbers and times as decimals. So 31/8/2003 is 37864, but midday on that date would be 37864.5
So if your record has a date and time stamp of midday on 31/8/2003, it will fail the comparison because 37864.5 is after 37864.

So you need to do something to get around this. If you are using Now() to timestamp records, you could change this to Date(). Or you could allow the user to input the criterion as now, but change it behind the scenes so that your comparison changes from <=31/8/2003 to <1/9/2003.

 

[racdata]

We are using Oracle / Empac database and it was set by the Oracle programmers I do not know if they used Now() or Date()

I thought I could do it in Access to get the dates correct

Thanx anyway for your help - Neil

 

[Pat Hartman]

The real problem is that your windows short date format is set to day/month/year. You of course think this is normal. However, on the other side of the pond we think that month/day/year is normal and bad luck for you, Micorsoft is on our side (actually maybe it's bad luck for us )

See my answer to this poster for a solution.

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=54145

 

[pcEars]

WEll I guess Half-way is better than the wrong way...

...at least I THINK we're saying the same thing.