VBA Function

[Valeriya]

Hi all,
Can some one give me a little advise or tell me why is this function can't be called from a query?
It's giving me a zero value for the whole field....

 Public Function Discrep_Length_Test() As Variant



If (IsNull(DMax("[ArchiveDate]", "0018 Counts Archive"))) Then

Discrep_Length_Test = DateDiff("m", DMax("[ArchiveDate]", "0016 In Bin Not Counted Archive"), DMin("[ArchiveDate]", "0016 In Bin Not Counted Archive"))

     
           
ElseIf (DMax("[ArchiveDate]", "0018 Counts Archive") > DMax("[ArchiveDate]", "0016 In Bin Not Counted Archive")) Then

                 Discrep_Length_Test = "Reconciled"

 Else
Discrep_Length_Test = DateDiff("m", DMax("[ArchiveDate]", "0018 Counts Archive"), DMax("[ArchiveDate]", "0016 In Bin Not Counted Archive"))

 End If

 

End Function

 

[hwkranger]

Because it returns a VARIANT

Randall Vollen
National City Bank Corp.

 

[Valeriya]

If you Could be so kind and tell me,a new starter:
1. If I want this function do the calculation and return the result of it, what type (if it's not variant) should I use then?

Thank you very much,

Valeriya.

 

[PHV]

Just a note:
An UDF (User Defined Function) without argument is only evaluated once at the start of the query and its return value stands for all retrieved records.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Valeriya]

right that excatly what I'm doing...
I'm getting the function ( Discrep_Length_Test()) from the guery through the expression builder...
Is that means I have to change my code somehow?
I still dont get what's wrong with my VARIANT type?

Thanks in advance,

Valeriya

 

[PHV]

Why not playing with the F8 key (step by step) when the cursor is in the body of your function ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886