Help with adding to a date type as default field

[ssturges]

Hey guys, I'm really new to access and I'm not sure if what I am asking is even possible.



I have a table, and I want to add a date, to an integer to get a new date. In excel, I would just use Date(year(cell),Month(cell) + etc), I'm not sure what the syntax for Access is for this task.

I want one of my fields to be equal to Date of one field, plus number of months to add to that date to get a new Date.

I've been looking around, I see that this is normally done with queries. I am trying to to do with one of my table.

Thanks for the help.

[June7]

You can just add a number to a date value. The default unit is day so: Date() + 4 will add 4 days to current date. Use DateAdd function to specify calculation for other units.

Calculations can be done in table only in a Calculated datatype field and it appears that DateAdd function is not available. You could do: [your field name] + 60 to add 60 days to the date. Or DateSerial is available.

Can use DateAdd function in a query or textbox ControlSource on form or report.

Here is a reference for functions available in Access, see the DateTime category http://office.microsoft.com/en-us/ac...959.aspx?CTT=3

[ssturges]

So I tried: [my field name] + [another field name] and access keeps giving me the error "The database engine does not recognize either the field 'my field name' in a validation expression, or the default value in the table 'My table name'.

The only function that seems to work is the: Date() + (some constant).
for example: I have a starting date, and I have a integer months, and I want to add them to get the end date.
is this even possible with using the default value property?

[June7]

Sorry, I missed the point that you wanted this in DefaultValue property. Why would you want a calculation of two fields there? Saving calculated data is usually a bad idea. This calculated data should not be saved to table. Save the raw data and do the calculation 'on-the-fly' when needed. Also, DefaultValue only works when record is initiated, so if either term's value is not there when the record is initiated, the default value would not calculate.

[ssturges]

Ah, you're right. (That's the Excel part of me trying to do everything one place)So instead of trying to calculate the date, in the table field, I think it would be a better ideal to do this in a Query. I will play more around with this until I get stuck again. Thanks for your help

[June7]

Probably why Access 2010 introduced the Calculated field so that some calculations can be shown in table without having to use query or textbox ControlSource expression. These calculated fields can be used just like any native raw data field and is what I initially thought you were trying to do.

However, use of this datatype field can make the db incompatible with Access 2007 and more difficult to convert to even older versions.