Can I use a variable for the number value in AddDate?

[Janet]

I'm developing (what I thought would be) a fairly simple database to
track employee training. I would like the query to give me the
expiration date of a particular training based upon the previous
completion date and the number of months before expiration. Different
types of training are good for different amounts of time (i.e. First
Aid is 12 months, CPR is 36 months).

I want to use the field ExpireMos as the interval rather than a fixed
number, but I'm getting an error. Here's the SQL for the query:

SELECT tblAttendance.Complete, DateAdd("m",[tblClasses.ExpireMos],
[tblAttendance.Complete]) AS ExpireDate, tblAttendance.*, tblClasses.*
FROM tblEmployees INNER JOIN (tblClasses INNER JOIN tblAttendance ON
tblClasses.ClassID = tblAttendance.ClassID) ON tblEmployees.EmployeeID
= tblAttendance.EmployeeID;

Many thanks in advance for any assistance...

 

[Douglas J. Steele]

There's no reason you shouldn't be able to use a variable.

See whether referring to the fields correctly makes a difference:

SELECT tblAttendance.Complete, DateAdd("m",[tblClasses].[ExpireMos],
[tblAttendance].[Complete]) AS ExpireDate, tblAttendance.*, tblClasses.*
FROM tblEmployees INNER JOIN (tblClasses INNER JOIN tblAttendance ON
tblClasses.ClassID = tblAttendance.ClassID) ON tblEmployees.EmployeeID
= tblAttendance.EmployeeID;

If that doesn't work, what's the error you're getting?

 

[Janet]

There's no reason you shouldn't be able to use a variable.

See whether referring to the fields correctly makes a difference:

SELECT tblAttendance.Complete, DateAdd("m",[tblClasses].[ExpireMos],
[tblAttendance].[Complete]) AS ExpireDate, tblAttendance.*, tblClasses.*
FROM tblEmployees INNER JOIN (tblClasses INNER JOIN tblAttendance ON
tblClasses.ClassID = tblAttendance.ClassID) ON tblEmployees.EmployeeID
= tblAttendance.EmployeeID;

If that doesn't work, what's the error you're getting?

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)

I copied the above into my query with no success. The error shows in the ExpireDate field when I run the query as "#Error".

 

[Janet]

My testing of the DateAdd function errored if either variable held an empty
string. If date was Null then returned Null, if number was Null, errored.
Following returned 3/10/2010=:
x = 5
y = "10/10/2009"
DateAdd("m", x, y)
So I say variables okay, data may be issue. A control with no value wouldbe
an empty string. But you seem to be referring to recordset fields. Do you
allow empty strings in tables? I always set this table property to no. Nulls
are easier to handle with IsNull and Nz functions.

Where does this query reside - VBA, saved, RecordSource?

I have month amounts in all five classes records (i.e., 12, 24) and
dates in all of the attendance records. I will set the table
properties of both fields to required, which is what I assume you mean
by not allowing empty strings in tables, and see what happens. I did
try the AddDate with using just a number for the interval, but I got
the same "#Error" in the field when I ran the query. Maybe the
problem has to do with the date field I'm using--it's formatted as a
short date in the table.

I'm sorry, but I don't understand your question about where the query
resides--I created it using Query Design in Access 2007, and the SQL
view is what I copied into my posting. (I understand basic Access
principles, but hardly anything about the programming side of things.)

 

[Douglas J. Steele]

There's no reason you shouldn't be able to use a variable.

See whether referring to the fields correctly makes a difference:

SELECT tblAttendance.Complete, DateAdd("m",[tblClasses].[ExpireMos],
[tblAttendance].[Complete]) AS ExpireDate, tblAttendance.*, tblClasses.*
FROM tblEmployees INNER JOIN (tblClasses INNER JOIN tblAttendance ON
tblClasses.ClassID = tblAttendance.ClassID) ON tblEmployees.EmployeeID
= tblAttendance.EmployeeID;

.If that doesn't work, what's the error you're getting?

I copied the above into my query with no success. The error shows in the
ExpireDate field when I run the query as "#Error".

No offense, but you're sure that the fields with which you need to deal are
ExpireMos and Complete? Just to be certain, try running

SELECT tblAttendance.Complete, DateAdd("m",[tblClasses].[ExpireMos],
[tblAttendance].[Complete]) AS ExpireDate, [tblClasses].[ExpireMos] As
ExpireMonths,
[tblAttendance].[Complete] AS CompletedDate, tblAttendance.*, tblClasses.*
FROM tblEmployees INNER JOIN (tblClasses INNER JOIN tblAttendance ON
tblClasses.ClassID = tblAttendance.ClassID) ON tblEmployees.EmployeeID
= tblAttendance.EmployeeID;

and make sure that ExpireMonths is an integer and CompletedDate is a date.

 

[Janet]

I finally figured it out--my subform was actually adding new records
to the Classes table instead of to the Attendance (junction) table,
thereby creating records with no ExpireMos data. I created a lookup
combo box in my subform which now saves my selection to the junction
table, and now the AddDate works! Thank you so much for your
assistance.

Jan