Min and Max functions
These 2 are missing in VBA, but I found them both useful:
Function Max(ByVal d1 As Date, ByVal d2 As Date) As Date
Max = d1
If d2 > d1 Then Max = d2
End Function
Function Min(ByVal d1 As Date, ByVal d2 As Date) As Date
Min = d1
If d2 < d1 Then Min = d2
End Function
posted by Alex Dybenko | 19:49
4 Comments:
I like the work you've done here. You've given excellent solutions.
In Access03 the above was a problem for me. I received the message "The expression you entered has a function containing the wrong number of arguments."
Try this. Create a brand new query in a brand new database and do NOT create any VBA functions. Make your query simply select max(2,3). Access 03 gives that message every time you enter 2 arguments for max.
The solution is to use MyMax and MyMin. Silly, huh. Apparently Max and Min are reserved words.
Thanks for comments! I never used this function in query, so good to know!
Well queries are where the big payoff is, in "byte compacting." I was recently stunned when I hit a QBE character limit - beyond which you are relegated to the raw SQL editor. (Ironic...this is precisely when the QBE is *most* useful.) I hold myself blameless for the huge size :(
Assume (A)K1 is one very long expression - hundreds of bytes; (B)K2 is a similar expression; (C)both are query field names(... AS K1, ... AS K2); (D)you want the greater of the two to be a sorted third output field. Now you wouldn't mind going
LaterDate: Iif(K1>K2,K1,K2)
and in fact you can do just that IF you don't sort. If sorting, you must expand everything. In this case, that means four giant expansions. This is where the Max - errr, MyMax is great, basically cutting the length in half.
(Yeah, yeah, some would say such bloat indicates a need for redesign/workarounds such as subqueries. Noted. However, compacting the size with the UDFs unarguably shrinks the visual size of a query or query component, simplifying visual interpretation. Sometimes that's well worth the speed tradeoff.)
Hi,
Agree, that makes sense, but in most cases i use subqueries for such long-sql queries
Post a Comment
<< Home