Minimum function problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

marwan - 23 Mar 2006 06:39 GMT

whats wrong with this query
select min(dateserial (2006,1,1), (select min([Date]) from dbase ))

The inner Minimum function works fine
(select min([Date]) from dbase )

the outer Minimum function does not work and gives :
wrong number of arguments used with function in query expression ....

what if I want to compare the outcome of the inner select with another
constant dateserial (2006,1,1) then return the minimum of them?
Thanks

Reply to this Message

Tom Ellison - 23 Mar 2006 06:51 GMT

Dear Marwan:

I suggest you use a date literal instead of the dateserial.

The min() function is aggregate, not a simple function to operate with a
list of values from which to select. Your first min() is not even close to
legal for what I think you want.

Could this be useful:

SELECT IIf(MIN([Date]) < #1/1/2006#, MIN([Date]), #1/1/2006#) AS EarlierDate
FROM dbase

Change EarlierDate to whatever you want this column called.

Is this perhaps what you were wanting?

Tom Ellison

> whats wrong with this query
> select min(dateserial (2006,1,1), (select min([Date]) from dbase ))
[quoted text clipped - 8 lines]
> constant dateserial (2006,1,1) then return the minimum of them?
> Thanks

Reply to this Message

Allen Browne - 23 Mar 2006 06:55 GMT

Min() selects the minimum value from a column of values, not from a
horizontal list of values.

If you have just 2 values, you might use an IIf() expression. Or, you could
use the MinOfList() function in this link:
MinOfList() and MaxOfList() functions
at:
http://allenbrowne.com/func-09.html

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Reply to this Message

Tom Ellison - 23 Mar 2006 07:05 GMT

Dear Allen:

We collide again! Well, at least we generally pretty much agree.

I wrote up the IIf() version, probably just what you were suggesting.

As you said, the first case of the use of Min() was not according to the way
it really works, which I tried to express.

I'm saying this so Marwan can see that we're very close in our suggestions.
I hope you agree. I wanted to keep his confusion to a minimum, and his
understanding at a maximum.

Do you agree as to the similarities of our posts?

Tom Ellison

> Min() selects the minimum value from a column of values, not from a
> horizontal list of values.
[quoted text clipped - 17 lines]
>> constant dateserial (2006,1,1) then return the minimum of them?
>> Thanks

Reply to this Message

Allen Browne - 23 Mar 2006 07:13 GMT

Hi Tom. Hopefully the OP will be happy to get different responses saying
essentially the same thing.

Of course, we must make assumptions about the example in the post. I assume
the DateSerial() is just an example, and the actual expression he is using
is based on some field.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Dear Allen:
>
[quoted text clipped - 33 lines]
>>> what if I want to compare the outcome of the inner select with another
>>> constant dateserial (2006,1,1) then return the minimum of them?

Reply to this Message

marwan - 23 Mar 2006 07:51 GMT

Thanks Fellows I'll try these methods and inform you in case of
failure.
Thanks agaim

Reply to this Message