Function won't eat Null...

[Steff_DK]

I get type mismatch with the below:

Function(lngAmount) as string

if lngAmount is Null Then
Function = ""
...



Why can't my input be Null? -and how do I solve this?

I get the error in this part: "if lngAmount is Null"

 

[Sergeant]

Try
If IsNull(lngAmount) Then...


"Is Null" syntax only works in query criteria, as far as I know.

 

[WayneRyan]

Steff,

Sarge is right, but if you want to check for Null and/or an
empty string, use the Nz function:

If Nz(SomeField) = "" Then
   MsgBox("The string is EITHER Null or empty.")
End If

Wayne

 

[Steff_DK]

Have tried:

Nz(SomeField) = ""
Nz(SomeField,"") = ""
Nz(SomeField,0) = 0

Nothing works

 

[WayneRyan]

Steff,

Need some more info here. What datatype is it? How do you know that
it's not working? Can you post your code here?

Wayne

 

[Steff_DK]

Data type is Long.

The function is called in a query like:

FormatMins([tblLog].[totalMins]) AS [TotalTime] WHERE...

Function looks like:

FormatMins(lngMin as Long) as String

Dim strHrs as String
Dim strMin as String

strHrs=Int(lngMin/60)
strMin= lngMin Mod 60

FormatMins=strHrs & ":" & Format(StrMin,"00")

End sub

 

[WayneRyan]

Steff,

You still didn't say what is wrong. What does it return when [totalMins] is Null?
Does it throw an error?

This should work:

FormatMins(lngMin as Long) as String

Dim strHrs as String
Dim strMin as String

If Nz(lngMin, 0) = 0 Then
   FormatMins = "SomeStringValue"
   Exit Sub
Else
   strHrs=Int(lngMin/60)
   strMin= lngMin Mod 60
   FormatMins = strHrs & ":" & Format(StrMin,"00")
End If

End sub  <-- Don't you mean function?

Wayne

 

[Steff_DK]

The fields in the query says "#Error" where there are no records matching the WHERE criteria (Null passed to the function).

So I made a form with a textbox and a button that passes whatever contents of the textbox through the function. Then I get the error description "type mismatch"...

Does this work on your machine:
If Nz(lngMin, 0) = 0 Then ???

(I can't attach the db, as it's on the company network... No floppies or anything in the machines for security reasons)

End sub, End function, tomato, tomaaaaaahto

 

[Rich]

FormatMins(lngMin as Long) as Variant ?

 

[RoyVidar]

public function FormatMins(lngMin as variant) as variant

Only variants can be Null, so I don't think the function triggers at all when passed Null. You'll need som trapping of the case where the "lngMin" is Null within the function and perhaps return some string "00"?