I'm relatively niave regarding things Access. I have a form with a calculated date field that draws its source date from another field in the same table as the calculated field + a certain number of months (taken from a field in another table). I used the formula supplied by MS Access's help system:

=DateSerial(Year(tClasses_Person!LastTaken),Month(tClasses_Person!LastTaken)+ [tClassInfo]![CertLength] ,Day(tClasses_Person!LastTaken))

However, it yields the '#Name?' error. All the table/field names are correct (I got them from the Expression Builder lists.) What am I doing wrong?

--Allen


Edited by: lodestone on Tue Nov 15 18:41:44 EST 2005.

Can you please explain in a little more detail exactly what you want done? Do you just need the CertLength (Is this value in Months?) added to your date??

If so, you could use:

=DateAdd("m", [tClassInfo]![CertLength], [tClasses_Person]![LastTaken])

Yes. I am simply trying to add the date stored in LastTaken to the months stored in CertLength to indicate when a person will need to take the class again.

I tried the DateAdd() function just like suggested but I continue to get the #Name? error message.

--Allen

What are the datatype of the fields CertLength and LastTaken?

If you want to add 30 days to a date in your table that is bound to that form then your calculated field is equal to:



The DateAdd function will add the number of days to LastTaken assuming that date is entered in a textbox called txtLastTaken.

You need some way of relating which class you're talking about in one table is related to which CertLength in another. I used a class code. You may have something else. Use that.

The DLookUp function will look up the value of CertLength given that class codes are the same.

The Nz function will give you a "default" of 30 days in case that class code is not found in table tClassInfo.

CertLength is a long integer expressing the length of time, in months, until the class must be retaken & LastTaken is a short date.

As to relating the tables, the form is bound to the table tClasses_Person, in which lives the field LastTaken, & references the Classes_PersonID primary key for that table. The primary key for the tClassInfo in which CertLength lives is the field ClassInfoID. Would I be guessing correctly that the field I need is Classes_PersonID, not ClassInfoID?

I finally solved the problem by writing a query, doing the calculation there then basing the form on that query. The formula I used ended up looking like this: RenewDate: DateAdd("m",tClassInfo!CertLength,tClasses_Person!LastTaken). Why on earth did MS have to have such odd syntax variations? Sometimes it needs a dot, sometimes the exclamation mark. Confusing! (or should that be " . " )

--Allen