Date Field and Number Field

[goslincm]

I have a date field in the format 00/00/0000 and a number field that represents a year value. Is there a way to have my number field value add to the year only of my date field?

 

[genomon]

Sure. Save your mm/dd/yyyy field to a date datatype (datDate for now)
Then DATEADD ( datepart , number, date )

Or in this case DATEADD("year", YourNumberField, datDate)

If you are using Access(VBA) I think you have to use # delims on the string var:

Dim datDate as Date
datDate = "#mm/dd/yyyy#"

I have great faith in fools; self-confidence my friends call it.
-Poe

 

[goslincm]

Alright, I have my [EffDate] in my table as a date type.
I also have my [ReviewDue] field saved as a date type, while the [Frequency] is a number field.

However, I've not used the DATEADD function before and cannot pull it all together to have the new values saved into the [ReviewDue] field.

 

[Golom]

You should not store computed values in a table. To generate a query showing the date the review is due

Select [EffDate]
     , [Frequency]
     , ... Other Fields ...
     , DateAdd("yyyy", [Frequency], [EffDate]) As [ReviewDue]

From myTable

If you must put them in the table

UPDATE myTable

SET ReviewDue = DateAdd("yyyy", [Frequency], [EffDate])

 

[PHV]

You wanted something like this (SQL code) ?
UPDATE yourTable
SET ReviewDue = DateAdd('yyyy', [Frequency], [EffDate])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[goslincm]

Thanks for the help. My question regarding using this in a query instead of a table is in querying for those reviews that are past due.