Minimum function problem

[marwan]

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

 

[Tom Ellison]

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

 

[Allen Browne]

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

 

[Tom Ellison]

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.

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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

 

[Allen Browne]

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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.

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

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?

 

[marwan]

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