Records Omitted from Query for Errors in Criteria Code

[Tatakau]

I have several rather complicated queries that are used for mail merges that
use functions as their fields.

ex: Field~ Tour Time: IIF(Format([time],"h:mm AM/PM")="12:00
PM","12:00 Noon",Format([time],"h:mm AM/PM"))

However, if by chance the value of [time] is null for a given record, it
will cause an error in the code (internal - nothing visible;
format(null,"h:mm AM/PM") doesn't go over very well), and because of this, it
will not show up in the query results. I could fix this by doing the
following:

ex: Field~ Tour Time: IIF(isnull([time]),"",IIF(Format([time],"h:mm
AM/PM")="12:00 PM","12:00 Noon",Format([time],"h:mm AM/PM")))

but then I would have to go over every single one of my queries and
painstakingly edit every individual query field that uses a function. Which
would just be a pain in the ass.

So, I was wondering if someone could sorta do it for me. $5 per hour. lol,
not really, but it'd be nice if I could make the Jet engine just ignore
errors alltogether in a query. If it can't exceute a function because of a
null value in a table, why omit the entire record? I'd like to do something
like "On Error Resume Next", but this isn't exactly VBA. Is there something
similar I could do?

Thanks!

Nick

 

[Ofer]

You can create a kind of format of your on, that can take care of the errors,
such as

Function Lib_Format(FieldValue As Variant, FieldFormat As String)
On Error GoTo Lib_Format_Err
If IsNull(FieldValue) Or FieldValue = "" Then
Lib_Format = ""
Exit Function
End If
Lib_Format = Format(FieldValue, FieldFormat)
Exit Function

Lib_Format_Err:
Lib_Format = ""
End Function

And then in the query, instead of running format, run the Lib_Format
ex: Field~ Tour Time: IIF(Lib_Format([time],"h:mm AM/PM")="12:00
PM","12:00 Noon",Lib_Format([time],"h:mm AM/PM"))