If before today's date insert Expired

[Linda RQ]

Hi Everyone,

Using Access 2003. I have a query with 5 fields. I have created an
expression to insert words if the CredentialExpirationDate is after a
1/1/2050 it inserts "No Expiration" or if the date in the
CredentialExpirationDate field is today or before I want it to insert
"Expired" all other dates are as entered I am having trouble with the last
part today or before. I have tried various things I have pulled from other
posts but I keep getting an error. This is my most recent <Now().

Credential Expiration: Switch([CredentialExpirationDate]>#1/1/2050#,"No
Expiration",[CredentialExpirationDate],<Now(),"Expired")

Thanks,
Linda

 

[Linda RQ]

Whoops, I discovered that the dates that don't fit either criteria don't
populate the Credential Expiration field. In another query, I thought this
[CredentialExpirationDate],"mmmd\,yyy") would work but I must be missing
something here too.

Linda

 

[KenSheridan via AccessMonster.com]

Linda:

This should do it:

Switch([CredentialExpirationDate] >#1/1/2050#,"No Expiration",
CredentialExpirationDate < Date(),"Expired",[CredentialExpirationDate] >=
Date(),Format([CredentialExpirationDate], "dd mmmm yyyy"))

You can of course format the date returned however you wish. Note the use of
the Date function rather than the Now function. The former returns the
current date with a zero time of day, the latter the current date and current
time of day.

Ken Sheridan
Stafford, England

Whoops, I discovered that the dates that don't fit either criteria don't
populate the Credential Expiration field. In another query, I thought this
[CredentialExpirationDate],"mmmd\,yyy") would work but I must be missing
something here too.

Linda

Hi Everyone,

[quoted text clipped - 12 lines]

Thanks,
Linda

 

[John Spencer]

Adding a third set of criterion response will handle returning the value in
CredentialExpirationDate. You should be aware that you will return a string
in every case.

Credential Expiration: Switch(
[CredentialExpirationDate]>#1/1/2050#,"No Expiration",
[CredentialExpirationDate]<Date(),"Expired",
True,[[CredentialExpirationDate])



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[John W. Vinson]

Hi Everyone,

Using Access 2003. I have a query with 5 fields. I have created an
expression to insert words if the CredentialExpirationDate is after a
1/1/2050 it inserts "No Expiration" or if the date in the
CredentialExpirationDate field is today or before I want it to insert
"Expired" all other dates are as entered I am having trouble with the last
part today or before. I have tried various things I have pulled from other
posts but I keep getting an error. This is my most recent <Now().

Credential Expiration: Switch([CredentialExpirationDate]>#1/1/2050#,"No
Expiration",[CredentialExpirationDate],<Now(),"Expired")

Thanks,
Linda

A couple of things: for one, Now() is not today's date, it's the current date
and time accurate to the second; you've got a comma in the middle of the
second condition; and you don't have all the options covered in your Switch.
Try:

Credential Expiration: Switch(
[CredentialExpirationDate]>#1/1/2050#,"No Expiration",
[CredentialExpirationDate]<Date(),"Expired",
True,Null)

Use <= Date() if you want the expiration to take effect instantly at midnight
on the beginning of the day.