Function call from qry - what if Null???

[Steff_DK]

Hi all!

I have a query that returns some fields with "#Error" ...

The reason is that I call a function in the query, and the function chokes if there are no matches in my query criteria and therefore no argument is sent to the function.

I tried putting this in the function:

var = Iif(IsNull(var) = True, 0, var)

-but I still get "#Error" when there are no matching records.

I presume this is because not even Null is given to the function as an arg.
Should I make the arg optional or how can I solve this??

Thanks

 

[MStef]

Try with NZ function.

 

[Steff_DK]

Still no joy ...

var = Nz(var)

Should return 0 if var is Null ... Still I get #Error

 

[MStef]

Look at Access help Nz.
Sum(Nz([Freight],0))

 

[Rich]

Is var declared as a Variant?

 

[reclusivemonkey]

Hi all!

I have a query that returns some fields with "#Error" ...

var = Iif(IsNull(var) = True, 0, var)

-but I still get "#Error" when there are no matching records.

Is this an IF statement in VBA? If so, don't use IIF, thats only for query functions.

 

[Steff_DK]

The vars are actually strings ...

Here is an example of one of the fields in the query that I tried adding Nz(

.[field]) the replacement arg is optional but I made it "00" not to ruin anything for the left() and right() functions.
The query:

FormatMins((SELECT DISTINCTROW Sum((60*(ControltimeHrs(LEFT(Nz([tblLogFA].[Start],"00"),2),
RIGHT(Nz([tblLogFA].[Start],"00"),2),LEFT(Nz([tblLogFA].[Stop],"00"),2),RIGHT(Nz([tblLogFA].[Stop],"00"),2))))+
(ControltimeMin(LEFT(Nz([tblLogFA].[Start],"00"),2),RIGHT(Nz([tblLogFA].[Start],"00"),2),LEFT(Nz([tblLogFA].[Stop],"00"),2),
RIGHT(Nz([tblLogFA].[Stop],"00"),2)))) FROM tblLogFA  WHERE [tblLogFA].[klcv_nr]=fWin2KUserName() And [tblLogFA].[Delete]=False
 And Format([tblLogFA].[Date],"YYYY")=Format(Date(),"YYYY") AND ((Month([tblLogFA].[Date]))<7))) AS Tim1st

The functions:

Function ControltimeHrs(intStartHrs As Integer, intStartMin As Integer, intStopHrs As Integer, intStopMin As Integer) As String

intStartHrs = Nz(intStartHrs)
intStartMin = Nz(intStartMin)
intStopHrs = Nz(intStopHrs)
intStopMin = Nz(intStopMin)

    Dim intHrs As Integer
    Dim intMin As Integer
    
    If intStopHrs >= intStartHrs And intStopMin >= intStartMin Then
    intHrs = intStopHrs - intStartHrs
    intMin = intStopMin - intStartMin
    ElseIf intStopHrs < intStartHrs And intStopMin >= intStartMin Then
    intHrs = intStopHrs + 24 - intStartHrs
    intMin = intStopMin - intStartMin
    ElseIf intStopHrs >= intStartHrs And intStopMin < intStartMin Then
    intHrs = intStopHrs - intStartHrs - 1
    intMin = intStopMin + 60 - intStartMin
    ElseIf intStopHrs < intStartHrs And intStopMin < intStartMin Then
    intHrs = intStopHrs + 23 - intStartHrs
    intMin = intStopMin + 60 - intStartMin
    End If
    
    ControltimeHrs = intHrs

End Function

Function ControltimeMin(intStartHrs, intStartMin, intStopHrs, intStopMin As Integer) As String

intStartHrs = Nz(intStartHrs)
intStartMin = Nz(intStartMin)
intStopHrs = Nz(intStopHrs)
intStopMin = Nz(intStopMin)

    Dim intHrs As Integer
    Dim intMin As Integer
    
    If intStopHrs >= intStartHrs And intStopMin >= intStartMin Then
    intHrs = intStopHrs - intStartHrs
    intMin = intStopMin - intStartMin
    ElseIf intStopHrs < intStartHrs And intStopMin >= intStartMin Then
    intHrs = intStopHrs + 24 - intStartHrs
    intMin = intStopMin - intStartMin
    ElseIf intStopHrs >= intStartHrs And intStopMin < intStartMin Then
    intHrs = intStopHrs - intStartHrs - 1
    intMin = intStopMin + 60 - intStartMin
    ElseIf intStopHrs < intStartHrs And intStopMin < intStartMin Then
    intHrs = intStopHrs + 23 - intStartHrs
    intMin = intStopMin + 60 - intStartMin
    End If
    
    ControltimeMin = Format(intMin, "00")

End Function

Btw the above code works, but still produces #Error fields in my query if there are no matches.

Thanks for your help so far