BrokenBiker
ManicMechanic
- Local time
- Today, 00:50
- Joined
- Mar 22, 2006
- Messages
- 128
I'm working on a training database. It lists people and course codes, and everything's worked out except for the last--and most important--part.
I'm in the middle of designing the main query ("qry_TrainingMain"--pretty original, huh?) and it includes a lot of nested IIf statements. I basically need to list the date training was completed (straight from the tbl_TraininMain), and show when the training is due and its current status.
Training due (TngDue) is only calculated if the course frequency (Freq_CrsList) is anything other than '0'.
Training status (which will be an expression in the query) will end up being one of the following: QUAL, UNQUAL, AWACT, or OVDUE.
So, let me spell out the criteria--'cuz there's quite a few.
TngDue: If Freq_CrsList = 0, or If DateCompleted_Tng IsNull Then the field is blank--otherwise DateAdd("m", Freq_CrsList, DateCompleted)
TngStatus/Test: If DateCompleted IsNull Then "UNQUAL"; If TngDue = current month (yyyymm) or next month (yyyymm) Then "AWACT"; If TngDue < current month(yyyymm) Then "OVDUE"; otherwise "QUAL"
Oh, yeah...I need to ensure another IsNull statement is included for those records w/o a TngDue date. For instance, a DateCompleted w/a Freq_CrsList of '0' should be "QUAL"
That's about it. I'm r-e-a-l-l-y close to getting the query all worked out, but one of the problems I'm having is the "OVDUE" status shows up for a handful of future--but not all future--due dates.
Thanks for the help,
-BB
I'm in the middle of designing the main query ("qry_TrainingMain"--pretty original, huh?) and it includes a lot of nested IIf statements. I basically need to list the date training was completed (straight from the tbl_TraininMain), and show when the training is due and its current status.
Training due (TngDue) is only calculated if the course frequency (Freq_CrsList) is anything other than '0'.
Training status (which will be an expression in the query) will end up being one of the following: QUAL, UNQUAL, AWACT, or OVDUE.
So, let me spell out the criteria--'cuz there's quite a few.
TngDue: If Freq_CrsList = 0, or If DateCompleted_Tng IsNull Then the field is blank--otherwise DateAdd("m", Freq_CrsList, DateCompleted)
TngStatus/Test: If DateCompleted IsNull Then "UNQUAL"; If TngDue = current month (yyyymm) or next month (yyyymm) Then "AWACT"; If TngDue < current month(yyyymm) Then "OVDUE"; otherwise "QUAL"
Oh, yeah...I need to ensure another IsNull statement is included for those records w/o a TngDue date. For instance, a DateCompleted w/a Freq_CrsList of '0' should be "QUAL"
That's about it. I'm r-e-a-l-l-y close to getting the query all worked out, but one of the problems I'm having is the "OVDUE" status shows up for a handful of future--but not all future--due dates.
Thanks for the help,
-BB
