News, links, downloads, tips and tricks on Microsoft Access and related

About Me Search
Name:Alex Dybenko

Location:Moscow, Russia

Thursday, April 27, 2006

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


Blogger Gates Is Antichrist said...

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.

2:24 AM  
Blogger Alex Dybenko said...

Thanks for comments! I never used this function in query, so good to know!

7:57 AM  
Blogger Gates Is Antichrist said...

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.)

8:13 PM  
Blogger Alex Dybenko said...

Agree, that makes sense, but in most cases i use subqueries for such long-sql queries

2:22 PM  

Post a Comment

<< Home