Date Calculation and Formatting

Have a form based on a query to calculate an expiration date. WarrantyMonths is a number field. InvoiceDate is a date field. I’ve written the following expression to the query

ExpiresDate: IIf(IsNull(VehicleServiceDetails!WarrantyMonths)," ",DateAdd("m",[WarrantyMonths],[InvoiceDate]))

However, if I try to sort the ExpiresDate, the results appear to sort by month and day only, not month, day and year.

Eg results of sort ascending:
ExpiresDate

03/20/2004
04/28/2005
06/09/2004
06/11/2004
06/11/2004
06/19/2004
08/20/2004
10/23/2004
12/10/2002

Additionally, I would like to use conditional formatting to flag unexpired warranties with green. If I set the form control DateExpires to

Field Value Is Greater than or equal to Date() The 04/28/2005 record shows as expired and the 12/10/2002 record shows as unexpired. Any assitance is appreciated.

Thank you,
Chris

I think the problem is that Access isn't seeing the field=20
as being a date. Try:

iif(IsNull(WarrantyMonths),Null,
DateAdd("m",[WarrantyMonths],[InvoiceDate]))


Chris Nebinger




-----Original Message-----
Have a form based on a query to calculate an expiration=20
date. WarrantyMonths is a number field. InvoiceDate is a=20
date field. I=E2?Tve written the following expression to=20
the query =20

ExpiresDate: IIf(IsNull(VehicleServiceDetails!
WarrantyMonths),"",DateAdd("m",[WarrantyMonths],
[InvoiceDate]))

However, if I try to sort the ExpiresDate, the results=20
appear to sort by month and day only, not month, day and=20
year. =20

Eg results of sort ascending:=20
ExpiresDate

03/20/2004
04/28/2005
06/09/2004
06/11/2004
06/11/2004
06/19/2004
08/20/2004
10/23/2004
12/10/2002

Additionally, I would like to use conditional formatting=20
to flag unexpired warranties with green. If I set the=20
form control DateExpires to=20

Field Value Is Greater than or equal to Date() The=20
04/28/2005 record shows as expired and the 12/10/2002=20
record shows as unexpired. Any assitance is appreciated. =20

Thank you,
Chris
.

That did it. Thank you!
Chris

Chris,

The IIf(IsNull([FieldName]),"" part is causing Access to sort the
field as though it were text. Try it this way:
ExpiresDate:
IIf(IsNull(VehicleServiceDetails!WarrantyMonths),[WarrantyMonths],Date
Add("m",[WarrantyMonths],[InvoiceDate]))

Since [WarrantyMonths] is null nothing will show anyway.

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

On Tue, 25 May 2004 13:21:07 -0700, Chris wrote:

Have a form based on a query to calculate an expiration date.
WarrantyMonths is a number field. InvoiceDate is a date field.
I¢ve written the following expression to the query

ExpiresDate: IIf(IsNull(VehicleServiceDetails!WarrantyMonths)," ",Dat
eAdd("m",[WarrantyMonths],[InvoiceDate]))

However, if I try to sort the ExpiresDate, the results appear to
sort by month and day only, not month, day and year.

Eg results of sort ascending: ExpiresDate

03/20/2004 04/28/2005 06/09/2004 06/11/2004 06/11/2004 06/19/2004
08/20/2004 10/23/2004 12/10/2002

Additionally, I would like to use conditional formatting to flag
unexpired warranties with green. If I set the form control
DateExpires to

Field Value Is Greater than or equal to Date() The 04/28/2005
record shows as expired and the 12/10/2002 record shows as
unexpired. Any assitance is appreciated.


Thank you, Chris

Fred,
Your solution works also. Thank you!
Chris