Simple formula!

[TheresaD]

Hi, I'm fairly new to building databases and have a basic question: why
won't my table take a simple formula that is to calculate [DATE ASSIGNED] and
add 85 days to it? "=DateAdd("y",+85,[Date Assigned])" is not working in the
default value or validation properties...it says the [DATE ASSIGNED] can't be
recognized....thanks!

 

[ghetto_banjo]

so you are doing this in a query?

to add days to a date, you want the "d" instead of "y". you also
don't need a plus sign in front of the 85.

is [DATE ASSIGNED] have the type of date/time?

=DateAdd("d", 85, [Date Assigned[)



On a side note, you do not want to store values in a table that are
calculated from other fields. If [Date Assigned] ever gets updated,
then this new date would be storing an incorrect value. Instead, just
calculate the value whenever you need it via a query or a report,
which Access can do very quickly. Your database will then be storing
correct information and will be a smaller size.

 

[Duane Hookom]

You should be able to use the table validation rather than the field
validation. You can use a default for a control on a form that references
another field's value.

I also think you should use:
DateAdd("d",85,[Date Assigned])

 

[Hans Up]

Hi, I'm fairly new to building databases and have a basic question: why
won't my table take a simple formula that is to calculate [DATE ASSIGNED] and
add 85 days to it? "=DateAdd("y",+85,[Date Assigned])" is not working in the
default value or validation properties...it says the [DATE ASSIGNED] can't be
recognized....thanks!

So you're in table design mode and attempting to set a default value for
a field named DATE ASSIGNED?

At the time the database engine attempts to apply the default value for
DATE ASSIGNED, DATE ASSIGNED has no value.

So what you're asking it to do is the equivalent of this:

DateAdd("y",85,Null)

That expression will return Null.

If your intention is to make the default value 85 days from the date the
record is created, try this:

DateAdd("d",85,Date())