DATEADD in VBA

[foxy]

Hi,

I am trying to update a date field in a table with a sql dateadd based on another date field in the same table. I have it within a RunSQL statement.
I cannot seem to get the SQL DATEADD function to workl within the RunSQL.

This is what I have currently:

DoCmd.RunSQL "UPDATE tblShields " & _
"SET 6mth_review = Dlookup(""DATEADD(month,6,tblShields.date_applied)"",""tblShields"") " & _
"WHERE tblShields.shield_ID = (SELECT MAX(shield_ID) FROM tblShields)"

Any ideas how I can get this update to work?

Cheers

 

[Kafrin]

An SQL statement should always finish with a semi-colon (, so first try putting one just before your last quote marks, see if that helps.

 

[foxy]

Done that, still getting an 'Unknown' error.

 

[stopher]

What are you trying to do? It looks like you are trying update the field 6mth_review to be 6 months after the field date_applied for the “last” record.

If that is correct or whatever you are trying to do, this isn’t the way to do it. For starters you are storing calculated data. This is a no-no. Don’t store calculated data. You can always calculate the 6mth_review date as required.

I don’t get your Dlookup statement at all. Even if the DateAdd function returned something (which I doubt it would), you are then passing a date value as the field entry for the Dlookup function. That kind of implies you have dates as headings in your table – surely not.

As I said, just calculate the review date when needed (using a query of as a calculation in your form or report)

Hth
Chris

 

[foxy]

What are you trying to do? It looks like you are trying update the field 6mth_review to be 6 months after the field date_applied for the “last” record.

Yea thats exactly what I'm trying to do.

So I'm guessing that a DLookup is not the way to do things, espcially as i seem to have got it completely wrong!

I do need to store this 6mth_review date in a field for auditing purposes. Is there an update statement I can use to insert a date into this field?

Cheers

 

[stopher]

What is it you are looking up? If you are just adding 6 months to date_applied for the required field then hopefully this will work:

DoCmd.RunSQL "UPDATE tblShields SET 6mth_review = DATEADD('m',6,tblShields.date_applied) WHERE tblShields.shield_ID = (SELECT MAX(shield_ID) FROM tblShields)"

You were close

Chris

 

[foxy]

That works, thats brilliant, thanks very much!