Nested IIf Statements w/Dates

[BrokenBiker]

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

 

[WayneRyan]

BB,

I'd use a Public Function in a module:

Public Function fnGetDateStatus(Freq_CrsList  As Integer, DateCompleted As Variant, TngDue As Date) As String

If IsNull(DateCompleted) Then
   fnGetDateStatus = "UNQUAL"
   Exit Sub
End If

'
' I won't write all of the code here, but it is much easier to write in VBA than trying to 
' write a nested IIf statement.
'

End Function

Call in a query by:

DateStatus: fnGetDateStatus([Freq_CrsList], [DateCompleted], [TngDue])

Wayne

 

[BrokenBiker]

OK. I'm getting an error. I've only used a module in a query once...and I can't seem to find an old copy of the db to use as reference. I've started the code in the module as below:

Public Function TngStatusFx(mdlStatus_Tng As Date) As String
If IsNull(DateCompleted_Tng) = True Then
    mdlStatus_Tng = "UNQUAL"
    Exit Function
ElseIf (DateCompleted_Tng) <> "" And (Freq_CrsList) = 0 Then
    mdlStatus_Tng = "QUAL"
    Exit Function
End If
End Function

In my query, I added another field:

y: TngStatusFx([Status_Tng])

When I run the query, that field is nothing but #Errors. Am I even close to the right format on this?

 

[SOS]

When you created the module, you didn't name it the same as the function did you? It really needs to be named something different.

 

[BrokenBiker]

I don't think I did. I'll double check tomorrow. (The file is on my computer at work.)

Also, I could probably give y'all some more information. The field "DateCompleted_Tng" is from a query. The table the query comes from has a field called "Status_Tng". I figure I can either use the module to UPDATE the table, or simply use a query (w/help from the module) to show the value of a field in the query to show the status. The "Status_Tng" field in the original table does not actually need to be stored. That was just a design necessity based on the Excel file that had the original info.

 

[BrokenBiker]

OK...Here's what I have for the VBA:

Public Function fnGetDateStatus(Freq_CrsList As Integer, DateCompleted_Tng As Variant, TngDue As Date) As String

'If no date in field, then training not accomplish--"UNQUAL"
If IsNull(DateCompleted_Tng) = True Then
    fnGetDateStatus = "UNQUAL"
    Exit Function
'If date exists in field and there's no re-currence in the frequency of training, then "QUAL"
ElseIf IsNull(DateCompleted_Tng) = False And (Freq_CrsList) = 0 Then
    fnGetDateStatus = "QUAL"
    Exit Function
    
'--"TngDue" is a calculated field from "Query1"

'If training due date is earlier than current month, then "OVDUE"
ElseIf TngDue < Year(Date) And Month(Date) Then
    fnGetDateStatus = "OVDUE"
'If training due date is equal to current month, then "AWACT"
ElseIf TngDue = Year(Date) And Month(Date) Then
    fnGetDateStatus = "AWACT"
'If training due date is due by next month, then "AWACT"
ElseIf TngDue = (Year(Date) And Month(Date)) + 1 Then
    fnGetDateStatus = "AWACT"
Else: fnGetDateStatus = "other"
End If

End Function

...and here's what I have for the query:

DateStatus: fnGetDateStatus([Freq_CrsList],[DateCompleted_Tng],[TngDue])

However, when I run the query I either get "other" or "#Error". I've done a lot of searching, but am pretty much stuck. The VBA looks about right. I don't understand why the query isn't returning any values...?

A couple of questions: Are the variants/dates/etc from the VBA supposed to be named different than in the query? Do I need to first open a recordset in VBA (dim db as database; dim rs as recordset; etc.)?

 

[SOS]

Your main problem is that you are trying to compare a date field (TngDue) with a non date criteria. Criteria such as Year(Date()) and Month(Date()) is not going to compare to a date. You would need to do something like:

(Year(TngDue) = Year(Date())) And (Month(TngDue)=Month(Date()))

 

[BrokenBiker]

I was thinking there might be a problem w/'dates' vs 'strings'. However, I think there might be another problem, because I should at least get a response for this portion:

If IsNull(DateCompleted_Tng) = True Then
    fnGetDateStatus = "UNQUAL"
    Exit Function

But even that doesn't show up. That's a pretty simple criteria--either the field is filled in or it isn't. Instead of getting approximately 20 "UNQUAL" results, they just show "#Error"

I'll try out your suggestion for the TngDue criteria and post the results.

Thanks for the speedy response.

 

[mcalex]

If IsNull(DateCompleted_Tng) = True Then

Shouldn't it just be
If (IsNull(DateCompleted_Tng)) Then

Dunno if syntax is the problem, coz looking harder I can't see that the extra bits are wrong, but I've always opened brackets immediately after the 'If' and don't compare to 'True' if the field (or function) IS True

hth

 

[BrokenBiker]

Some progress. I was able to get the date criteria to work with the following:

'If training due date is equal to current month, then "AWACT"
If (Year(TngDue) = Year(Date)) And (Month(TngDue) = Month(Date)) Then
    fnGetDateStatus = "AWACT"
    Exit Function
'If training due date is due by next month, then "AWACT"
ElseIf (Year(TngDue) = Year(Date)) And (Month(TngDue) = (Month(Date) + 1)) Then
    fnGetDateStatus = "AWACT2"
    Exit Function
ElseIf (Year(TngDue) = (Year(Date) + 1) And (Month(thgdue) = (Month(Date) + 1))) Then
    fnGetDateStatus = "AWACT3"
    Exit Function
'If training due date is earlier than current month, then "OVDUE"
ElseIf (Year(TngDue) < Year(Date)) Then
    fnGetDateStatus = "OVDUE"
    Exit Function
ElseIf (Year(TngDue) = Year(Date)) And (Month(TngDue) < Month(Date)) Then
    fnGetDateStatus = "OVUDE2"
    Exit Function

However, I still get nothing but errors when trying to account for a null entry. It should be as simple as

ElseIf (IsNull(DateCompleted_Tng)) Then
    fnGetDateStatus = "UNQUAL"
    Exit Function

But that, and several other attempts give me nothing but "#Error". Any ideas?

 

[SOS]

Instead of Null try this:

ElseIf Len(DateCompleted_Tng & "") = 0 Then

 

[BrokenBiker]

Sorry, SOS. Your help on the date formatting was very helpful, but the Len function didn't work either. I'm still getting the same errors.

 

[JANR]

Perhaps this quote from Brian will cast some light on your problem:

I assume that you are getting #Error returned from your function?
You need to check in the function change the start to

Public Function AgeGroup(DOB As Variant) As String
Dim intAge As Integer
If Not Isdate(DOB) then exit function
Or you might want to return some string which can be filtered out in a query

If Not IsDate(DOB) Then
AgeGroup = "Invalid Dob"
Exit Function
End If

The change of the declaration of DOB from Date to Variant allows incorrect dates to be handled by your code.

Brian

Taken from this tread:
http://www.access-programmers.co.uk/forums/showthread.php?t=191426


JR

 

[BrokenBiker]

I tried that out too, but had the same results. However, there is good news. I was able to overcome all the #Errors!

I used a combo of the public function and IIf statements in the query. Here's how it turned out:

Public Function fnGetDateStatus(Freq_CrsList As Integer, DateCompleted_Tng As Variant, TngDue As Date) As String
'--"TngDue" is a calculated field from "Query1"
'If training due date is equal to current month, then "AWACT"
If (Year(TngDue) = Year(Date)) And (Month(TngDue) = Month(Date)) Then
    fnGetDateStatus = "AWACT"
    Exit Function
'If training due date is due by next month, then "AWACT"
ElseIf (Year(TngDue) = Year(Date)) And (Month(TngDue) = (Month(Date) + 1)) Then
    fnGetDateStatus = "AWACT2"
    Exit Function
'If current month is Dec and training due date is Jan of the next year, then "AWACT"
ElseIf (Year(TngDue) = (Year(Date) + 1)) And (Month(TngDue) = 1) And (Month(Date) = 12) Then
    fnGetDateStatus = "AWACT3"
    Exit Function
'If training due date is earlier than current month, then "OVDUE"
ElseIf (Year(TngDue) < Year(Date)) Then
    fnGetDateStatus = "OVDUE"
    Exit Function
ElseIf (Year(TngDue) = Year(Date)) And (Month(TngDue) < Month(Date)) Then
    fnGetDateStatus = "OVUDE2"
    Exit Function
Else: fnGetDateStatus = "QUAL3"
    Exit Function
End If
End Function

And then in the query, I have:

TngDue: IIf([Freq_CrsList]=0,"",IIf(IsNull([DateCompleted_Tng]),"",DateAdd("m",[Freq_CrsList],[DateCompleted_Tng])))

and:

DateStatus: IIf(IsNull([DateCompleted_Tng]),"UNQUAL",IIf([Freq_CrsList]=0,"QUAL",fnGetDateStatus([Freq_CrsList],[DateCompleted_Tng],[TngDue])))

Since the public f(x) is basically dealing in strings as opposed to date, I had to rig up a new requirement, specific for the Dec-Jan transition. Basically, I made a requirement that says if the year in the TngDue field is = next year, and the month in the TngDue field is Jan...and...the current month is Dec, then show the status as "AWACT." Without this, I would've been in big trouble come December.


Thanks for all the help!
-BB

Tags: IIf, date vs. string, module, query, month, year, training tracker

 

[SOS]

Glad you got it working.