Query using DateAdd

[minii]

Hey,

So I'm basically creating a query that I want to do a Date Add function in. I'm trying to calculate a date based on a field in the query, while using another field that contains the amount of years that I want to increase it by. Brief description being:

Warranty: e.g. 4
InstallDate: e.g. 10/4/2005

Then I have DateAdd("yyyy", [Warranty], [InstallDate]) but when I run the query I'm getting data type missmatch. Before I actually used this in the database I setup a quick test database to try it out, in essence the table and query this is based on was setup exactly the same and it worked fine Any help appreciated.

 

[vbaInet]

Is the datatype of Warranty Number or Text?

It might also be returning Null so in the meantime try this:

DateAdd("yyyy", Nz([Warranty], 0), [InstallDate])

 

[minii]

It is set as a number. I have just checked and the test one I did was set as text. Perhaps changing it would sort it out. Before I do that I'll try what you pasted and post back.

 

[vbaInet]

What I pasted only handles Nulls so you may not need it, but probably worth adding. So yes it needs to be in Number format for it to work.

 

[minii]

What you have posted seems to have done the trick. Thank you very much

Perhaps it worked due to some of the warranty fields being blank?

 

[vbaInet]

You're welcome!

But... if it is returning Null then maybe you want to sort that out instead of relying on Nz(). This is because if it contains Null then it will add 0 to the Year and you don't want that.

 

[minii]

Yeah, I believe the problem lies with some old data that isn't up to date and doesn't need to actually be in the database. It's on my to do list to remove it but for now the Null value issue shouldn't be too much of a problem (hopefully).

I think I've sorted a workaround anyway by inputting 0 into the blanks and telling the query to not use 0's. Now by using the original DateAdd I posted, the query works again. So again, cheers. You have pointed out my fatal flaw!

 

[vbaInet]

Good job on the workaround. It's certainly beneficial having data in there than using the function.

Happy to help!