RE: [MS_AccessPros] Date Logic in a Query.

Art,

I would start by adding a column [MthsBetween] to tlkpTrainingCode. Enter 36 for First Aid and 24 for AED. Then in your text box, enter something like:

=IIf(IsNull(TrainingDate),"Initial Training Not Taken",Format(DateAdd("m",MthsBetween,TrainingDate),"mm dd yyyy") )

IMO, hard-coding values is not the correct solution. Data belongs in tables, not in code or expressions.

Duane Hookom
MS Access MVP

To: MS_Access_Professionals@yahoogroups.com
From: dbalorenzini@yahoo.com
Date: Wed, 14 Sep 2011 17:20:58 +0000
Subject: [MS_AccessPros] Date Logic in a Query.

I have the following query:
SELECT qryEmployeesExtended.[Employee Name], tblEmployeeTraining.TrainingDate, tlkpTrainingCode.TrainingCodeName
FROM qryEmployeesExtended INNER JOIN ((tblEmployeeTraining INNER JOIN tblTrainingGroup ON tblEmployeeTraining.TrainingGroupID = tblTrainingGroup.TrainingGroupID) INNER JOIN tlkpTrainingCode ON tblTrainingGroup.TrainingCodeID = tlkpTrainingCode.TrainingCodeID) ON qryEmployeesExtended.EmployeeID = tblEmployeeTraining.EmployeeID
WHERE (((tlkpTrainingCode.TrainingCodeID) In (17,30)));

This returns all employees that have a training code of 17 (First Aid) or 30 (AED).

What I need to is add a unbound textbox to a report and calulate the if the Training Date is blank then have a message such as Initial Training Not Taken, or if there is a training date then I need to calculate the date if the Training code is (17)First Aid have the date dropped in that is 3 years after the TrainingDate or if the Training Code is 30 (AED) have the date calculated that is 2 years later than the training date.

Any ideas?

Thank you

Art Lorenzini
Sioux Falls, Sd

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join
(Yahoo! ID required)

<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com
MS_Access_Professionals-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/