Access Date() & DateAdd functions apparent error in Vista

[Guest]

I have a table which contains sequential dates that are one day apart from
each other, in order to see entries for the last five days, I have used
(succesfully) the criteria:

Date() -5

from within MS XP SP2, having migrated my: MS Enterprise office programs to
MS Vista I find that this no longer works - only two records are shown - more
worrying is the fact that the "DateAdd" function appears to have gone the
same way when the criteria:

= DateAdd("d",-5,[Date Entered])

is used.

Anyone any ideas? I am presently using the criteria:

Date() -105

which returns the last eight days of records, and whilst this is a plausible
workaround it is hardly correct procedure.
Colin

 

[Allen Browne]

Colin, I'm using 5 versions of Access under Windows Vista, and have not seen
it behave the way you describe, so something else must be going on here.

What is Date Entered? Is it:
(a) A table field?
(b) A calculated query field?
(c) A parameter?

Where do you have:
= DateAdd("d",-5,[Date Entered])
In the control source of a text box?

I'm guessing that Access is misunderstanding the data type of something here
somewhere. If it's a table field, make sure it is a date/time field. If it's
a calculated field, wrap the calculation in CVDate(). If it's a parameter,
declare it. If it's a text box expression, set the Format property so Access
knows the intended data type.

More info:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html
or:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a table which contains sequential dates that are one day apart from
each other, in order to see entries for the last five days, I have used
(succesfully) the criteria:

Date() -5

from within MS XP SP2, having migrated my: MS Enterprise office programs
to
MS Vista I find that this no longer works - only two records are shown -
more
worrying is the fact that the "DateAdd" function appears to have gone the
same way when the criteria:

= DateAdd("d",-5,[Date Entered])

is used.

Anyone any ideas? I am presently using the criteria:

Date() -105

which returns the last eight days of records, and whilst this is a
plausible
workaround it is hardly correct procedure.
Colin

 

[Guest]

Allen
thanks for the reply.
In answer to your questions:

1. "Date Entered" is the name given to the field which stores the date of
each record entered, and it is: A Table field.

2. DateAdd("d",-5,[Date Entered] was I suspect a flawed attempt by me to
get a result, after trying the more conventional: >= DateAdd("d",-5,Date())
This code in both cases was placed inthe criteria grid of the query design
page.

3. The field "Date Entered" is correctly recorded in my table as a date
field.

Regards Colin

Colin, I'm using 5 versions of Access under Windows Vista, and have not seen
it behave the way you describe, so something else must be going on here.

What is Date Entered? Is it:
(a) A table field?
(b) A calculated query field?
(c) A parameter?

Where do you have:
= DateAdd("d",-5,[Date Entered])
In the control source of a text box?

I'm guessing that Access is misunderstanding the data type of something here
somewhere. If it's a table field, make sure it is a date/time field. If it's
a calculated field, wrap the calculation in CVDate(). If it's a parameter,
declare it. If it's a text box expression, set the Format property so Access
knows the intended data type.

More info:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html
or:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a table which contains sequential dates that are one day apart from
each other, in order to see entries for the last five days, I have used
(succesfully) the criteria:

Date() -5

from within MS XP SP2, having migrated my: MS Enterprise office programs
to
MS Vista I find that this no longer works - only two records are shown -
more
worrying is the fact that the "DateAdd" function appears to have gone the
same way when the criteria:

= DateAdd("d",-5,[Date Entered])

is used.

Anyone any ideas? I am presently using the criteria:

Date() -105

which returns the last eight days of records, and whilst this is a
plausible
workaround it is hardly correct procedure.
Colin

 

[Allen Browne]

Okay, so [Date Entered] is a Date/Time field in your table, and in the
Criteria row of your query under this field, you have:

= DateAdd("d",-5,Date())

That's perfectly logical, and I don't see how Access could misunderstand
your expression. It should show records for the last 5 days.

If it does not, you might try a compact/repair (Database Utilities on Tools
menu), just in case an index has gone bad.

As always, it's worth ensuring that the Name AutoCorrect boxes are unchecked
under:
Tools | Options | General

Other than that, you could try removing the critiera and typing an
expression like this into the Field row:
([Date Entered] >= DateAdd("d",-5,Date()))
See if Access returns True (-1) or False (0), and try to determine if there
is some logic to where it gets it wrong.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen
thanks for the reply.
In answer to your questions:

1. "Date Entered" is the name given to the field which stores the date of
each record entered, and it is: A Table field.

2. DateAdd("d",-5,[Date Entered] was I suspect a flawed attempt by me to
get a result, after trying the more conventional: >=
DateAdd("d",-5,Date())
This code in both cases was placed inthe criteria grid of the query design
page.

3. The field "Date Entered" is correctly recorded in my table as a date
field.

Regards Colin

Colin, I'm using 5 versions of Access under Windows Vista, and have not
seen
it behave the way you describe, so something else must be going on here.

What is Date Entered? Is it:
(a) A table field?
(b) A calculated query field?
(c) A parameter?

Where do you have:
= DateAdd("d",-5,[Date Entered])
In the control source of a text box?

I'm guessing that Access is misunderstanding the data type of something
here
somewhere. If it's a table field, make sure it is a date/time field. If
it's
a calculated field, wrap the calculation in CVDate(). If it's a
parameter,
declare it. If it's a text box expression, set the Format property so
Access
knows the intended data type.

More info:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html
or:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a table which contains sequential dates that are one day apart
from
each other, in order to see entries for the last five days, I have used
(succesfully) the criteria:

Date() -5

from within MS XP SP2, having migrated my: MS Enterprise office
programs
to
MS Vista I find that this no longer works - only two records are
shown -
more
worrying is the fact that the "DateAdd" function appears to have gone
the
same way when the criteria:

= DateAdd("d",-5,[Date Entered])

is used.

Anyone any ideas? I am presently using the criteria:

Date() -105

which returns the last eight days of records, and whilst this is a
plausible
workaround it is hardly correct procedure.
Colin

 

[Guest]

Hi Allen
I have compacted/repaired the database and - as far as I can tell - all is
as we would expect it to be. Thanks for your input.
Regards Colin

Okay, so [Date Entered] is a Date/Time field in your table, and in the
Criteria row of your query under this field, you have:

= DateAdd("d",-5,Date())

That's perfectly logical, and I don't see how Access could misunderstand
your expression. It should show records for the last 5 days.

If it does not, you might try a compact/repair (Database Utilities on Tools
menu), just in case an index has gone bad.

As always, it's worth ensuring that the Name AutoCorrect boxes are unchecked
under:
Tools | Options | General

Other than that, you could try removing the critiera and typing an
expression like this into the Field row:
([Date Entered] >= DateAdd("d",-5,Date()))
See if Access returns True (-1) or False (0), and try to determine if there
is some logic to where it gets it wrong.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen
thanks for the reply.
In answer to your questions:

1. "Date Entered" is the name given to the field which stores the date of
each record entered, and it is: A Table field.

2. DateAdd("d",-5,[Date Entered] was I suspect a flawed attempt by me to
get a result, after trying the more conventional: >=
DateAdd("d",-5,Date())
This code in both cases was placed inthe criteria grid of the query design
page.

3. The field "Date Entered" is correctly recorded in my table as a date
field.

Regards Colin

Colin, I'm using 5 versions of Access under Windows Vista, and have not
seen
it behave the way you describe, so something else must be going on here.

What is Date Entered? Is it:
(a) A table field?
(b) A calculated query field?
(c) A parameter?

Where do you have:
= DateAdd("d",-5,[Date Entered])
In the control source of a text box?

I'm guessing that Access is misunderstanding the data type of something
here
somewhere. If it's a table field, make sure it is a date/time field. If
it's
a calculated field, wrap the calculation in CVDate(). If it's a
parameter,
declare it. If it's a text box expression, set the Format property so
Access
knows the intended data type.

More info:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html
or:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a table which contains sequential dates that are one day apart
from
each other, in order to see entries for the last five days, I have used
(succesfully) the criteria:

Date() -5

from within MS XP SP2, having migrated my: MS Enterprise office
programs
to
MS Vista I find that this no longer works - only two records are
shown -
more
worrying is the fact that the "DateAdd" function appears to have gone
the
same way when the criteria:

= DateAdd("d",-5,[Date Entered])

is used.

Anyone any ideas? I am presently using the criteria:

Date() -105

which returns the last eight days of records, and whilst this is a
plausible
workaround it is hardly correct procedure.
Colin

 

[David W. Fenton]

Okay, so [Date Entered] is a Date/Time field in your table, and in
the Criteria row of your query under this field, you have:

= DateAdd("d",-5,Date())

That's perfectly logical, and I don't see how Access could
misunderstand your expression. It should show records for the last
5 days.

Isn't it easier to just use:

Date() - 5

as the criterion, since the integer value of dates is the number of
days? The only time I'd use DateAdd() is when I am caculating with
something other than days.

 

[Allen Browne]

That's fine too, David.

An argument could be made that subtracting 5 in JET is better than a VBA
function call.

I'm a little leary of performing direct math on date/time fields. The
results are not identical for dates before 1900 that have a tie component as
well, so I don't recommend it.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Okay, so [Date Entered] is a Date/Time field in your table, and in
the Criteria row of your query under this field, you have:

= DateAdd("d",-5,Date())

That's perfectly logical, and I don't see how Access could
misunderstand your expression. It should show records for the last
5 days.

Isn't it easier to just use:

Date() - 5

as the criterion, since the integer value of dates is the number of
days? The only time I'd use DateAdd() is when I am caculating with
something other than days.

 

[David W. Fenton]

I'm a little leary of performing direct math on date/time fields.
The results are not identical for dates before 1900 that have a
tie component as well, so I don't recommend it.

What do you mean by "tie component"?

 

[Allen Browne]

Sorry: time component.

For example, you get a different day of December from:
#1/1/1800 6:00am# - 5.5
compared to:
#1/1/2000 6:00am# - 5.5

 

[David W. Fenton]

Sorry: time component.

For example, you get a different day of December from:
#1/1/1800 6:00am# - 5.5
compared to:
#1/1/2000 6:00am# - 5.5

Ah. I'd never do non-integer math on dates, because of the rounding
inaccuracies. But given that the days are the integer part of the
actual date value stored, I think it's completely safe to add and
subtract days.