[AccessD] A2K Reference the dot column property in a query

Hi John

You have exactly hit the catch of the use of static functions like
these.

You have to decide - for yourself and the current application - how to
handle these two questions. There is no right or wrong answer:

1. Will I ever pull a value without having fed one?
2. If yes, do I - when no value has been supplied to it - want the
function to return a don't-know-value (Null) or a preset value?

Further, if you for 2. decide to supply a non determined value like
Null, you must, of course, prepare your query to handle this value.
This is important as the original reason for considering a static
function often is to avoid Nulls.

If you answer No to 1. you have no problems.

If you answer Yes to 1. and decide for supplying a non determined
value when no value is available, Variant is probably the right choice
as return value type of the function as this can be Null.
String will return an empty string which often is satisfactory, while
the numeric types will return zero which quite often _will_ have a
meaning. This is indeed true for date/time values.

For dates it is often not convenient to return Null values as you
can't use these in "Between .. And .." constructions. If an "early"
date must be returned as the non determined value, you may pick
#1/1/100#.

Finally, I see no problem in using Variants. Sometimes they are the
right choice as in your function. However, you should name them as
such, like varCTYID.

/gustav

PS: Why are you not sleeping at this time, hogging your pillow?


> The only way I was able to get this working was using an optional VARIANT
> and checking IsMissing.  IsNull didn't work on a missing integer.

> Static Function FltrSfrmClaimAssociatesCboContactSelFilter(Optional
> llngCTYID As Variant)
> Dim lngCTYID As Variant
>     If Not IsMissing(llngCTYID) Then
>         lngCTYID = llngCTYID
>     End If
>     FltrSfrmClaimAssociatesCboContactSelFilter = lngCTYID
> End Function

> It does work great though.  Set it up to be called in AfterUpdate of the
> filter combo and used the function value in the dependent combo's query.