MS Access Module for Status Query 1

[iuianj07]

Hello, I am trying to create a query to track the status of our project. and I think I need to make a module instead of a switch statement to have the status column correct.

Basically, we track the status if the loan (job) has been assigned to anyone, and by the dates to see on what stage is it currently at right now. So to give a glimpse on how it is being tracked:

so first a loan will be assigned to someone -- then the first step is to Index the loan-- then someone will QC (Quality Control) the Indexed Loan, after the loan has been indexed, it will be redacted, then redacted QC.

So the module I need to make is that:

If Indexing_Entry_Assigned_To is Null Then the Status column should say "Not Assigned"

If Indexing_QC_Completed is not null AND Redaction_QC_Date_Completed is null THEN the Status Column should say "Indexing QC Completed"

and IF Redaction_QC_Date_Complete Is Not Null THEN the Status Column should say "Redaction QC Completed"


I created this Module but for some reason it returns "#Error" on the Status column of the query. Below is the code...


Option Compare Database

Public Function Status(Index_QC As Date, Redaction_QC As Date, Index_Assigned As String)


Dim iIndex_assigned As String
Dim iIndex_QC As Date
Dim iRedaction_QC As Date



Select Case True

Case IsNull(Index_Assigned) = True
Status2 = "Not Assigned"
Case IsNull(Index_QC) = False And IsNull(Redaction_QC) = True
Status2 = "Indexing QC Completed"
Case IsNull(Redaction_QC) = False
Status = "Redaction QC Complete"
End Select

Status = Status2


End Function


Then on my query I entered this on the Status Column:

Status: Status([Indexing.Indexing_QC_Date_Complete],[Redaction_QC_Date_Complete],[Indexing_Entry_Assigned_To])


Please help me debug, and point out to me the code or logic I did wrong...

Any help is greatly appreciated.


Thanks

 

[iuianj07]

correction: I re-wrote the module to this:

Option Compare Database

Public Function Status(Index_QC As Date, Redaction_QC As Date, Index_Assigned As String)


Dim Status2 as String


Select Case True

Case IsNull(Index_Assigned) = True
Status2 = "Not Assigned"
Case IsNull(Index_QC) = False And IsNull(Redaction_QC) = True
Status2 = "Indexing QC Completed"
Case IsNull(Redaction_QC) = False
Status = "Redaction QC Complete"
End Select

Status = Status2


End Function



I am very sorry for the confusion, as I am trying to debug my own code the same time as I am posting it here... thank you again

 

[MajP]

Public Function getStatus(Index_QC As Variant, Redaction_QC As Variant, Index_Assigned As Variant) As String
 'strings an dates can not contain null only variants. isnull fails on non variant data types
 'If used in a query always pass a variant data type. Because there will always be the time
 'when some field is empty and the whole thing will bomb.
 'Also I think you may want to check that the values are actually dates as well
 
 If IsNull(Index_Assigned) Then
    getStatus = "Not Assigned"
 ElseIf IsDate(Index_QC) And Not IsDate(Redaction_QC) Then
    getStatus = "Indexing QC Completed"
 ElseIf IsDate(Redaction_QC) Then
    getStatus = "Redaction QC Complete"
 End If

End Function
[code]

 

[iuianj07]

thanks MajP...

It is working correctly,

I appreciate your help! Thank you again!