DateAdd - Date Additions - Very odd results

[Dmak]

I'm using the DateAdd function and also Date + 14 etc and DateAddW (user defined, provided by MS)

The problem is as soon as I try to add more than a few days the results I get are very odd. For example if I add 10 days I get a result that is months in the future, if I add say 14 days I get a result that is months in the past. Whichever methods I use I get these strange results when going beyond a few days.

I'm using the result in an SQL insert statement to schedule a task.

If anyone can shed some light on what's occurring it would be greatly appreciated.

 

[CJ_London]

can you give some examples of your code at the result

 

[Brianwarnock]

It sounds like you are caught up in that old problem that hard coded dates in SQL and VBA have to be in US format, more detail might be required to see if that is the cae bit in the SQL string the date must be in US format, use print.debug to print the string to see what is happening.


Brian

 

[Brianwarnock]

I notice that the above post has not registered on the threads post count, the site is weird today

 

[Dmak]

Thanks for your responses guys, much appreciated.

I have another problem now. I have a query to feed a report and I set the criteria to today " Date() " on the DateAdded field and it returns nothing.

The field is displaying a time as well as a date. Even if I format it, it returns nothing. Clear the criteria from the field and everything is returned.

This program is truly evil sometimes, actually quite a lot of the time! :/

If anyone can offer any pointers, that would be a great help.

 

[gemma-the-husky]

if the field includes a time as well as date, then your test will fail.

you are testing to see whether a value that includes a date and time is equal to a date. clearly it isn't.

you need to extract just the date fromthat date/time, first!

 

[Brianwarnock]

Datevalue(yourfield) will return just the date

Brian

 

[Galaxiom]

if the field includes a time as well as date, then your test will fail.

you need to extract just the date fromthat date/time, first!

Better performance can be had by using a range in the criteria.

WHERE datefield BETWEEN Date() AND DateAdd("s", -1, Date()+1)

This is because the calculation only needs to be applied once in the criteria instead of doing a calculation on every record to extract the date from the date/time.