Can you do an If Then expresion in query criteria"? If so How can I do what I need?

[scooteman]

Can you do an If Then expresion in query criteria"? If so How can I do what I need?

I am building a Training Records Database I have a query setup (actually several for differnt reports) that shows if an Individual's training is past due. The query works fine on the original criteria of the dates for Once, Annual and 3 years training cycles. But I added two new training typs based on a meeting with the State concerning a new Lead law that is in place. Now I have a training item that has a 2 Year Certification cycle and an annual Lead refresher. My question is, how do I keep an annual refresher with a date that is older than Lead Certification Date from show up? I want a Lead refresher date to show up if the current date is more than one year from the date of the refresher course, but only if the Lead Certification date is older. I just don't know how to write this in the critiria of the Date field. Any help would be greatly appreciated.

Thanks

 

[WayneRyan]

Re: Can you do an If Then expresion in query criteria"? If so How can I do what I nee

scooteman,

Anytime I'm faced with a logically complex criteria, I use a function.
To me, any IIf (or nested IIf function) is completely unreadable after
a few weeks.

I'd make a Public Function (put it on the module Tab):

Public Function CheckDate (TrainingType As String, TrainingDate As Date) As Boolean
   '
   ' I don't know your exact logic, but in this example the function is passed
   ' the training date and type and will return either a True/False
   '
   ' The VBA code should be much easier and maintainable.
   '
End Function

Then in your query, add a new column passing in ALL relevant fields:

NewField: CheckDate([TrainingType], [TrainingDate])

And in the Criteria section, just put True or -1

It's more of an issue of maintainability for me.

Wayne

 

[scooteman]

Re: Can you do an If Then expresion in query criteria"? If so How can I do what I nee

That didn't seam to work for me. Is there a way in the criteria of the query to exlude data if certain other date is found. In my training_Name field, It would work for me if the query results showed training was due for the "Lead 8 hour Renovator" that the "Lead Refersher" would not show even if the date field criteria say it is due.

I have 13 other training items that are included in this query.