Module Causing Data Type Mismatch

[Guest]

I'm using Access 2002. I have made a function to create a field value based
on the values of other fields. All of these fields are text fields, so I
declare them as strings, but whenever I run the query I am getting a data
mismatch error. Here's the code. Any thoughts would be much appreciated.

Public Function ConcTitle(strDistTitle As String, strSubtitle As String, _
strTitlePrefix As String, strTitle As String)

If IsNull(strDistTitle) Then
ConcTitle = strTitle & ", " & strTitlePrefix 'No Distinct Title
ElseIf IsNull(strSubtitle) Then
ConcTitle = strDistTitle 'No Subtitle
Else
ConcTitle = strDisTitle & ": " & strSubtitle 'Distinct Title with Subtitle
End If

End Function

 

[Duane Hookom]

I expect that you may be attempting to send Null values into the function.
You may need to change the "As String" to "As Variant" to handle Nulls.
Also, you should add "As String" at the end of your Public Function line.

 

[tina]

if i recall correctly, a string variable can't be Null, it can only be a
zero-length string ("") or a string with one or more characters. when you
call the function, try adding an Nz() to the function arguments, as

ConcTitle(Nz(MyDistTitle, ""), Nz(MySubtitle, ""), _
Nz(MyTitlePrefix, ""), Nz(MyTitle, ""))

substitute the correct field or control names, of course.

change your If statement to

If strDistTitle = "" Then
' etc, etc
ElseIf strSubtitle = "" Then

or you can change the function variables to data type Variant, and leave
your function call(s) and the If statement as is.

hth