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

 

[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.

 

[Linda RQ]

Thanks Everyone. The first recommendation worked perfectly!!! I will keep
the others and try those when time permits.

You guys are great!

Linda