Null Value in Function Call

[p7eggyc]

Good morning! I am working on a report and I am setting the data source for the report by creating a SQL statement in the code and then assigning that string to the datasource property. My problem comes in when I attempt to call a function I created in a module. The function takes a string found in the database and converts it to a string with the verbal equivelant. That is working fine unless there is a null value for the string in the database. I have stepped through the code and the function never even runs if there is no value in the database. I tried to make the parameter optional with a default but that didn't work.
Any other suggestions? Here is the SQL code I wrote and the particular function I am looking at right now is the HumanCodeConvert but I suspect the same problem is going to exist for all of the functions:

sqlcode = "SELECT "
sqlcode = sqlcode + "Human_Profile.State_ID AS [State ID], "
sqlcode = sqlcode + "Human_Clinical.Week_MMWR AS [MMWR Week], "
sqlcode = sqlcode + "Lookup_Common_County.County_Name AS County, "
sqlcode = sqlcode + "Mid(Human_Profile.DOB,5,2)+ ""/"" + Mid(Human_Profile.DOB,7,2)+ ""/"" + Mid(Human_Profile.DOB,1,4) AS [Date Of Birth], "
sqlcode = sqlcode + "Human_Profile.AGE + "" - "" + "
sqlcode = sqlcode + "AgeTypeCodeConvert(Human_Profile.AgeType) AS [Age Type], "
sqlcode = sqlcode + "GenderCodeConvert(Human_Profile.SEX) as [Sex], "
sqlcode = sqlcode + "Human_Profile.MD_First_Name + "" "" + Human_Profile.MD_Last_Name AS [MD Name], "
sqlcode = sqlcode + "Human_Profile.MD_Phone AS [MD Phone], "
sqlcode = sqlcode + "Human_Clinical.Vital_Status AS [Vital Status], "
sqlcode = sqlcode + "nz(Mid(Human_Clinical.Death_Date,5,2)+ ""/"" + Mid(Human_Clinical.Death_Date,7,2)+ ""/"" + Mid(Human_Clinical.Death_Date,1,4),""n/a"&quot AS [Date of death], "
sqlcode = sqlcode + "HumanCodeConvert(Human_Clinical.Case_Status) AS [Case Status], "
sqlcode = sqlcode + "Mid(Human_Clinical.Onset_Date,5,2)+ ""/"" + Mid(Human_Clinical.Onset_Date,7,2)+ ""/"" + Mid(Human_Clinical.Onset_Date,1,4) AS [OnSet Date], "
sqlcode = sqlcode + "YesNoCodeConvert(Human_Clinical.Meningitis) as [Meningitis], "
sqlcode = sqlcode + "YesNoCodeConvert(Human_Clinical.Encephalitis) as [Encephalitis] "
sqlcode = sqlcode + "FROM (Human_Profile "
sqlcode = sqlcode + "INNER JOIN Human_Clinical ON Human_Profile.Case_ID = Human_Clinical.Case_ID) "
sqlcode = sqlcode + "INNER JOIN Lookup_Common_County ON Human_Profile.COUNTY = Lookup_Common_County.County_Code "
sqlcode = sqlcode + "IN """ + wnvDatabase + """"
sqlcode = sqlcode + "WHERE "
sqlcode = sqlcode + "Human_Profile.WNV = True AND "
sqlcode = sqlcode + "Left([Human_Clinical]![OnSet_Date],4)= [forms]![frmChooseVirusHuman]![cmbYearSelector] "
sqlcode = sqlcode + "AND Lookup_Common_County.County_State_Code_Fips=""" & ProfileStateCodeFips & """;"


And here is the function call:

Public Function HumanCodeConvert(strHumanCode As String) As String
Select Case strHumanCode
Case "1"
HumanCodeConvert = "WNV Confirmed"
Case "2"
HumanCodeConvert = "WNV Probable"
Case "3"
HumanCodeConvert = "WNV Suspect"
etc.

End Function

 

[jebry]

Hi!

Make this change:

Public Function HumanCodeConvert(strHumanCode As Variant) As String

Select Case strHumanCode
Case "1"
HumanCodeConvert = "WNV Confirmed"
Case "2"
HumanCodeConvert = "WNV Probable"
Case "3"
HumanCodeConvert = "WNV Suspect"
etc.

End Function

If you make strHumanCode a variant it will accept null values. One more thing, you will now need to handle null values in your code, possibly by adding a Case Else statement and return and empty string.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[p7eggyc]

Thanks Jebry! That did it!

Peggy