DMax Function

D

Dan

Could someone tell me what is wrong with this:

program = DMax("[programID]", "[Program]", "[startDate] < " & Date & " AND
[ExpirationDate > " & Date)

It keeps returning null for this
If I enter: program = DMax("[programID]", "[Program]", "[startDate] > " & Date
It returns a value where the startDate is less than the date, not greater
than.
The record it returns from my table has the startDate = 5/12/05 and the
expirationdate = 12/30/05 and the current date is 5/14/05. So it is saying
5/12/05 > 5/14/05. both startDate and expirationDate is stored as the short
date in the tables and I have even tried to format the date as the short date
and still no luck


Also how would I sort the DateStarted field on a subform?
 
W

Wayne Morgan

program = DMax("[programID]", "[Program]", "[startDate] < " & Date & " AND
[ExpirationDate > " & Date)
Click to expand...

I'm assuming that this first one is just a typo in the message, but you have
a missing ] after ExpirationDate. Next, are startDate and ExpirationDate
defined as a Date/Time data type in the table? Storing as "Short Date" is a
format, not a data type, although it probably won't be effective unless the
data type matches. However, I believe adding Date Delimiters will solve your
problem.

program = DMax("[programID]", "[Program]", "[startDate] < #" & Date & "# AND
[ExpirationDate] > #" & Date & "#")


--
Wayne Morgan
MS Access MVP


Dan said:
Could someone tell me what is wrong with this:

program = DMax("[programID]", "[Program]", "[startDate] < " & Date & " AND
[ExpirationDate > " & Date)

It keeps returning null for this
If I enter: program = DMax("[programID]", "[Program]", "[startDate] > " &
Date
It returns a value where the startDate is less than the date, not greater
than.
The record it returns from my table has the startDate = 5/12/05 and the
expirationdate = 12/30/05 and the current date is 5/14/05. So it is saying
5/12/05 > 5/14/05. both startDate and expirationDate is stored as the
short
date in the tables and I have even tried to format the date as the short
date
and still no luck


Also how would I sort the DateStarted field on a subform?
Click to expand...
 
A

adsl

Dan said:
Could someone tell me what is wrong with this:

program = DMax("[programID]", "[Program]", "[startDate] < " & Date & " AND
[ExpirationDate > " & Date)

It keeps returning null for this
If I enter: program = DMax("[programID]", "[Program]", "[startDate] > " &
Date
It returns a value where the startDate is less than the date, not greater
than.
The record it returns from my table has the startDate = 5/12/05 and the
expirationdate = 12/30/05 and the current date is 5/14/05. So it is saying
5/12/05 > 5/14/05. both startDate and expirationDate is stored as the
short
date in the tables and I have even tried to format the date as the short
date
and still no luck


Also how would I sort the DateStarted field on a subform?
Click to expand...
 
A

adsl

Wayne Morgan said:
program = DMax("[programID]", "[Program]", "[startDate] < " & Date & "
AND
[ExpirationDate > " & Date)
Click to expand...

I'm assuming that this first one is just a typo in the message, but you
have a missing ] after ExpirationDate. Next, are startDate and
ExpirationDate defined as a Date/Time data type in the table? Storing as
"Short Date" is a format, not a data type, although it probably won't be
effective unless the data type matches. However, I believe adding Date
Delimiters will solve your problem.

program = DMax("[programID]", "[Program]", "[startDate] < #" & Date & "#
AND [ExpirationDate] > #" & Date & "#")


--
Wayne Morgan
MS Access MVP


Dan said:
Could someone tell me what is wrong with this:

program = DMax("[programID]", "[Program]", "[startDate] < " & Date & "
AND
[ExpirationDate > " & Date)

It keeps returning null for this
If I enter: program = DMax("[programID]", "[Program]", "[startDate] > " &
Date
It returns a value where the startDate is less than the date, not greater
than.
The record it returns from my table has the startDate = 5/12/05 and the
expirationdate = 12/30/05 and the current date is 5/14/05. So it is
saying
5/12/05 > 5/14/05. both startDate and expirationDate is stored as the
short
date in the tables and I have even tried to format the date as the short
date
and still no luck


Also how would I sort the DateStarted field on a subform?
Click to expand...
Click to expand...
 
Top