DateAdd not working properly in update query

[minii]

Hi,

I have an update query whereby if it matches criteria it updates a date using dateadd which looks like the following:

DateAdd("m",1,[ReportDate])

Now for some reason it updates it to 01/07/2013 (ReportDate was set as 01/12/2011) - I have no idea why because the criteria for the update query is using the exact command and comparing it to <Date() and that seems to work fine as it's obviously updating. Anyone have any ideas? I have attached an image for clarification.

Many thanks

 

[vbaInet]

I don't understand what your problem is but the bottom line is you shouldn't be saving those calculated values. They should be calculated on-the-fly in a Select Query.

Here's why:

http://allenbrowne.com/casu-14.html

 

[minii]

Why when I add 1 month to ReportDate does it not go from 1/12/2011 to 1/1/2012 instead it jumps to 1/7/2013 - that's ultimately what I'm asking.

 

[vbaInet]

No idea because I can't see the query in action.

Test it out in the Immediate Window to see the effect. It shouldn't add as you describe.

 

[minii]

I'll have a play. Thanks for your help!

 

[vbaInet]

And maybe you should test it out with only one record in the actual table.

Let us know how you get on.

 

[minii]

All sorted. I actually resolved this while working on another DB. It's just DateAdd not outputting things as a date, I don't know why... So although probably not the "proper" way for it to be done I just put it into a field of it's own and then that obviously formats it properly this then results in it working fine. Thanks for your help again!

 

[vbaInet]

If you give DateAdd() a date and you don't have any other formatting going on, it will spew out a Date.

Glad that's sorted.