Date Calculation and Formatting

[Guest]

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:
ExpiresDat

03/20/200
04/28/200
06/09/200
06/11/200
06/11/200
06/19/200
08/20/200
10/23/200
12/10/200

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

 

[Chris Nebinger]

I think the problem is that Access isn't seeing the field
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

date. WarrantyMonths is a number field. InvoiceDate is a
date field. Iâ?Tve 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.

 

[fredg]

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.

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.

 

[Guest]

Fred
Your solution works also. Thank you
Chri