UDF with two parameters only fires when both present

[G Lykos]

Greetings! Have a UDF being called in an append query to fill a calculated
field. It reads two dates from the source file, then decides what to do. I
realized that the UDF only fires when both dates are present, but I'd like
it to run whether they are present or not. What are some ways to cause the
UDF to fire for every record regardless if the argument fields have data or
not?

Thanks,
George

 

[John Spencer]

It would help if you posted your UDF.

I assume that you mean you are using two fields as the arguments that are
passed to the UDF and one or both of the fields may be null.

So, you either need to set the arguments inthe UDF to variant type (not date
type) and then test the arguments to see if they are null.

Alternatively, use NZ around the fieldnames to convert the nulls to some
specific value (that will never occur in your data) such as #1/1/1899# and
then test for that in your UDF.

 

[G Lykos]

John, you surmised correctly - the two arguments are two date fields being
passed to the UDF.

Your two suggestions are interesting. I had started to define the UDF
arguments as variants rather than dates, which led to a layer of CDATE( )'s
being required for comparisons and such, and thought I was perhaps
complicating things unnecessarily and should ask the question in this forum
as a sanity check.

Separately, in casting about for ideas I also noticed a passing reference to
the NZ( ) function in Fixing Access Annoyances. It is something I was not
aware of from previous programming adventures in other environments, and
appears to be an Access-specific device to deal with issues resulting from
Access's default treatment of empty fields.

I'll play with both approaches and see which is cleaner to use in this
application.

Thanks for your thoughtful replies to my several questions posted here.

Regards,
George