Expression to grandfather expiration by date

[LMB]

Hi Everyone,

Access 2000. I don't create my queries using the sql but I know it helps
you folks understand what I am talking about. At this point I can just make
basic queries. I have credential dates for employees. I would like to make
a report to show the expiration dates for credentials that is based on a
query. If an employee was credentialed in the year 2000 or after, their
credentials expire in 5 years. If they were credentialed their credentials
never expire. I would like the expiration date to show but if the employee
was credentialed I would like it to say something like No Expiration. Is
this possible and if so, how?

I tried this to add 5 years to my date by typing the expression below but it
just returns the CredentialCompletionDate. I guess I can't do the simple
expressions yet either.

Expiration Date: [CredentialCompletionDate]+5


SELECT qryDepartmentEmployees.Name, tblCredentialTypes.CredentialType,
tblCredentials.CredentialCompletionDate
FROM tblCredentialTypes INNER JOIN (qryDepartmentEmployees INNER JOIN
tblCredentials ON qryDepartmentEmployees.strEmployeeID =
tblCredentials.EmployeeID) ON tblCredentialTypes.CredentialTypesID =
tblCredentials.CredentialTypeID;


Thanks,
Linda

 

[Marshall Barton]

Access 2000. I don't create my queries using the sql but I know it helps
you folks understand what I am talking about. At this point I can just make
basic queries. I have credential dates for employees. I would like to make
a report to show the expiration dates for credentials that is based on a
query. If an employee was credentialed in the year 2000 or after, their
credentials expire in 5 years. If they were credentialed their credentials
never expire. I would like the expiration date to show but if the employee
was credentialed I would like it to say something like No Expiration. Is
this possible and if so, how?

I tried this to add 5 years to my date by typing the expression below but it
just returns the CredentialCompletionDate. I guess I can't do the simple
expressions yet either.

Expiration Date: [CredentialCompletionDate]+5

SELECT qryDepartmentEmployees.Name, tblCredentialTypes.CredentialType,
tblCredentials.CredentialCompletionDate
FROM tblCredentialTypes INNER JOIN (qryDepartmentEmployees INNER JOIN
tblCredentials ON qryDepartmentEmployees.strEmployeeID =
tblCredentials.EmployeeID) ON tblCredentialTypes.CredentialTypesID =
tblCredentials.CredentialTypeID;

That would be more like:

Expiration Date: IIf(Year(CredentialCompletionDate) < 2000,
"No Expiration", Format(DateAdd("yyyy", 5,
CredentialCompletionDate), "mmm d, yyyy"))

Be sure to check Help for any items that you are not
familiar with.

 

[LMB]

Access 2000. I don't create my queries using the sql but I know it helps
you folks understand what I am talking about. At this point I can just
make
basic queries. I have credential dates for employees. I would like to
make
a report to show the expiration dates for credentials that is based on a
query. If an employee was credentialed in the year 2000 or after, their
credentials expire in 5 years. If they were credentialed their
credentials
never expire. I would like the expiration date to show but if the
employee
was credentialed I would like it to say something like No Expiration. Is
this possible and if so, how?

I tried this to add 5 years to my date by typing the expression below but
it
just returns the CredentialCompletionDate. I guess I can't do the simple
expressions yet either.

Expiration Date: [CredentialCompletionDate]+5

SELECT qryDepartmentEmployees.Name, tblCredentialTypes.CredentialType,
tblCredentials.CredentialCompletionDate
FROM tblCredentialTypes INNER JOIN (qryDepartmentEmployees INNER JOIN
tblCredentials ON qryDepartmentEmployees.strEmployeeID =
tblCredentials.EmployeeID) ON tblCredentialTypes.CredentialTypesID =
tblCredentials.CredentialTypeID;

That would be more like:

Expiration Date: IIf(Year(CredentialCompletionDate) < 2000,
"No Expiration", Format(DateAdd("yyyy", 5,
CredentialCompletionDate), "mmm d, yyyy"))

Be sure to check Help for any items that you are not
familiar with.

Thank-you so much. It worked great! My problem with help has been, I do
not know the right question. When you typed (Year...is that declaring that
we are looking at the year part of the date? and if the date is less than
the year 2000 the what is in the quotes is what is entered, that is pretty
clear but quotes are also used to show certain records when put in the
criteria field so that confuses me a little.

I am on my way in to work so I am also going to play around with trying to
get "Expired" to show up in another column for the credentials that have
expired.

Linda

 

[Marshall Barton]

Access 2000. I don't create my queries using the sql but I know it helps
you folks understand what I am talking about. At this point I can just
make
basic queries. I have credential dates for employees. I would like to
make
a report to show the expiration dates for credentials that is based on a
query. If an employee was credentialed in the year 2000 or after, their
credentials expire in 5 years. If they were credentialed their
credentials
never expire. I would like the expiration date to show but if the
employee
was credentialed I would like it to say something like No Expiration. Is
this possible and if so, how?

I tried this to add 5 years to my date by typing the expression below but
it
just returns the CredentialCompletionDate. I guess I can't do the simple
expressions yet either.

Expiration Date: [CredentialCompletionDate]+5

SELECT qryDepartmentEmployees.Name, tblCredentialTypes.CredentialType,
tblCredentials.CredentialCompletionDate
FROM tblCredentialTypes INNER JOIN (qryDepartmentEmployees INNER JOIN
tblCredentials ON qryDepartmentEmployees.strEmployeeID =
tblCredentials.EmployeeID) ON tblCredentialTypes.CredentialTypesID =
tblCredentials.CredentialTypeID;

That would be more like:

Expiration Date: IIf(Year(CredentialCompletionDate) < 2000,
"No Expiration", Format(DateAdd("yyyy", 5,
CredentialCompletionDate), "mmm d, yyyy"))

Be sure to check Help for any items that you are not
familiar with.

Thank-you so much. It worked great! My problem with help has been, I do
not know the right question. When you typed (Year...is that declaring that
we are looking at the year part of the date? and if the date is less than
the year 2000 the what is in the quotes is what is entered, that is pretty
clear but quotes are also used to show certain records when put in the
criteria field so that confuses me a little.

You're right about the Year function, but you shouldn't
guess at what it means. Part of the trouble with finding
things in Help is that it is split into two separate Help
systems. The one you get by clicking Help in the Access
window is fairly generic help about using Access. Specific
details about functions, methods, properties, etc is only
available from the Help system in the Visual Basic Editor
(you can use Ctrl+G to open the VBE).

 

[Linda RQ]

LMB wrote:
Access 2000. I don't create my queries using the sql but I know it helps
you folks understand what I am talking about. At this point I can just
make
basic queries. I have credential dates for employees. I would like to
make
a report to show the expiration dates for credentials that is based on a
query. If an employee was credentialed in the year 2000 or after, their
credentials expire in 5 years. If they were credentialed their
credentials
never expire. I would like the expiration date to show but if the
employee
was credentialed I would like it to say something like No Expiration. Is
this possible and if so, how?

I tried this to add 5 years to my date by typing the expression below but
it
just returns the CredentialCompletionDate. I guess I can't do the simple
expressions yet either.

Expiration Date: [CredentialCompletionDate]+5

SELECT qryDepartmentEmployees.Name, tblCredentialTypes.CredentialType,
tblCredentials.CredentialCompletionDate
FROM tblCredentialTypes INNER JOIN (qryDepartmentEmployees INNER JOIN
tblCredentials ON qryDepartmentEmployees.strEmployeeID =
tblCredentials.EmployeeID) ON tblCredentialTypes.CredentialTypesID =
tblCredentials.CredentialTypeID;


That would be more like:

Expiration Date: IIf(Year(CredentialCompletionDate) < 2000,
"No Expiration", Format(DateAdd("yyyy", 5,
CredentialCompletionDate), "mmm d, yyyy"))

Be sure to check Help for any items that you are not
familiar with.

Thank-you so much. It worked great! My problem with help has been, I do
not know the right question. When you typed (Year...is that declaring that
we are looking at the year part of the date? and if the date is less than
the year 2000 the what is in the quotes is what is entered, that is pretty
clear but quotes are also used to show certain records when put in the
criteria field so that confuses me a little.

You're right about the Year function, but you shouldn't
guess at what it means. Part of the trouble with finding
things in Help is that it is split into two separate Help
systems. The one you get by clicking Help in the Access
window is fairly generic help about using Access. Specific
details about functions, methods, properties, etc is only
available from the Help system in the Visual Basic Editor
(you can use Ctrl+G to open the VBE).

 

[Linda RQ]

LMB wrote:
Access 2000. I don't create my queries using the sql but I know it helps
you folks understand what I am talking about. At this point I can just
make
basic queries. I have credential dates for employees. I would like to
make
a report to show the expiration dates for credentials that is based on a
query. If an employee was credentialed in the year 2000 or after, their
credentials expire in 5 years. If they were credentialed their
credentials
never expire. I would like the expiration date to show but if the
employee
was credentialed I would like it to say something like No Expiration. Is
this possible and if so, how?

I tried this to add 5 years to my date by typing the expression below but
it
just returns the CredentialCompletionDate. I guess I can't do the simple
expressions yet either.

Expiration Date: [CredentialCompletionDate]+5

SELECT qryDepartmentEmployees.Name, tblCredentialTypes.CredentialType,
tblCredentials.CredentialCompletionDate
FROM tblCredentialTypes INNER JOIN (qryDepartmentEmployees INNER JOIN
tblCredentials ON qryDepartmentEmployees.strEmployeeID =
tblCredentials.EmployeeID) ON tblCredentialTypes.CredentialTypesID =
tblCredentials.CredentialTypeID;


That would be more like:

Expiration Date: IIf(Year(CredentialCompletionDate) < 2000,
"No Expiration", Format(DateAdd("yyyy", 5,
CredentialCompletionDate), "mmm d, yyyy"))

Be sure to check Help for any items that you are not
familiar with.

Thank-you so much. It worked great! My problem with help has been, I do
not know the right question. When you typed (Year...is that declaring that
we are looking at the year part of the date? and if the date is less than
the year 2000 the what is in the quotes is what is entered, that is pretty
clear but quotes are also used to show certain records when put in the
criteria field so that confuses me a little.

You're right about the Year function, but you shouldn't
guess at what it means. Part of the trouble with finding
things in Help is that it is split into two separate Help
systems. The one you get by clicking Help in the Access
window is fairly generic help about using Access. Specific
details about functions, methods, properties, etc is only
available from the Help system in the Visual Basic Editor
(you can use Ctrl+G to open the VBE).

Thanks...Now that I am at work and printed out the report, I am told that it
is after July 1, 2002 that the credentials expire and anything before does
not. I tried to simply put 07/01/2002 in place of the <2000 but of course
it can't be this easy and it didn't work. Do you mind helping a little
more? I think the (Year( Credential ...may be the key, I'll mangle the
thing some more and try putting and other various things (Month, Day,
Year(Credentials....

Expiration Date: IIf(Year(CredentialCompletionDate) < 07/01/2002,
"No Expiration", Format(DateAdd("yyyy", 5,
CredentialCompletionDate), "mmm d, yyyy"))

 

[Marshall Barton]

"Marshall Barton" wrote

LMB wrote:
Access 2000. I don't create my queries using the sql but I know it helps
you folks understand what I am talking about. At this point I can just
make
basic queries. I have credential dates for employees. I would like to
make
a report to show the expiration dates for credentials that is based on a
query. If an employee was credentialed in the year 2000 or after, their
credentials expire in 5 years. If they were credentialed their
credentials
never expire. I would like the expiration date to show but if the
employee
was credentialed I would like it to say something like No Expiration. Is
this possible and if so, how?

I tried this to add 5 years to my date by typing the expression below but
it
just returns the CredentialCompletionDate. I guess I can't do the simple
expressions yet either.

Expiration Date: [CredentialCompletionDate]+5

SELECT qryDepartmentEmployees.Name, tblCredentialTypes.CredentialType,
tblCredentials.CredentialCompletionDate
FROM tblCredentialTypes INNER JOIN (qryDepartmentEmployees INNER JOIN
tblCredentials ON qryDepartmentEmployees.strEmployeeID =
tblCredentials.EmployeeID) ON tblCredentialTypes.CredentialTypesID =
tblCredentials.CredentialTypeID;


That would be more like:

Expiration Date: IIf(Year(CredentialCompletionDate) < 2000,
"No Expiration", Format(DateAdd("yyyy", 5,
CredentialCompletionDate), "mmm d, yyyy"))

Be sure to check Help for any items that you are not
familiar with.


Thank-you so much. It worked great! My problem with help has been, I do
not know the right question. When you typed (Year...is that declaring that
we are looking at the year part of the date? and if the date is less than
the year 2000 the what is in the quotes is what is entered, that is pretty
clear but quotes are also used to show certain records when put in the
criteria field so that confuses me a little.

You're right about the Year function, but you shouldn't
guess at what it means. Part of the trouble with finding
things in Help is that it is split into two separate Help
systems. The one you get by clicking Help in the Access
window is fairly generic help about using Access. Specific
details about functions, methods, properties, etc is only
available from the Help system in the Visual Basic Editor
(you can use Ctrl+G to open the VBE).

Thanks...Now that I am at work and printed out the report, I am told that it
is after July 1, 2002 that the credentials expire and anything before does
not. I tried to simply put 07/01/2002 in place of the <2000 but of course
it can't be this easy and it didn't work. Do you mind helping a little
more? I think the (Year( Credential ...may be the key, I'll mangle the
thing some more and try putting and other various things (Month, Day,
Year(Credentials....

Expiration Date: IIf(Year(CredentialCompletionDate) < 07/01/2002,
"No Expiration", Format(DateAdd("yyyy", 5,
CredentialCompletionDate), "mmm d, yyyy"))

Could explain the significance of 7/1/02?

Whatever it is, that would not be the place to put it. The
Year value is just 1999, 2002, etc. which will not compare
to a date in a meaningful way. Besides, I thought you said
that credentials from before 2000 have no expiration date.

If that is supposed to be a date for the expiration of
credentials, the expression would be more like:

Expiration Date: IIf(Year(CredentialCompletionDate) < 2000,
"No Expiration", IIf(CredentialCompletionDate <
DateAdd("yyyy", -4, CredentialCompletionDate), "Expired",
Format(DateAdd("yyyy", 4, CredentialCompletionDate), "mmm d,
yyyy"))

 

[LMB]

Could explain the significance of 7/1/02?

Whatever it is, that would not be the place to put it. The
Year value is just 1999, 2002, etc. which will not compare
to a date in a meaningful way. Besides, I thought you said
that credentials from before 2000 have no expiration date.

If that is supposed to be a date for the expiration of
credentials, the expression would be more like:

Expiration Date: IIf(Year(CredentialCompletionDate) < 2000,
"No Expiration", IIf(CredentialCompletionDate <
DateAdd("yyyy", -4, CredentialCompletionDate), "Expired",
Format(DateAdd("yyyy", 4, CredentialCompletionDate), "mmm d,
yyyy"))

When the report was given to the requester and they looked at the data, they
figured out that they had given me the wrong info. I looked it up on the
credentialing website myself and found that credentials earned before July,
1, 2002 do not expire but credentials from July 1, 2002 expire in 5 years.
I am starting to learn that intense questioning backwards and forwards is a
necessary component to this database design stuff.

I also have been looking around in the VB help file. Not sure where to
start... any suggestions? There is a lot of stuff in there! Thanks for
explaining that there were 2 different help files. The only way to get to
VB help is ctrl+G?

Thanks,
Linda

 

[Marshall Barton]

When the report was given to the requester and they looked at the data, they
figured out that they had given me the wrong info. I looked it up on the
credentialing website myself and found that credentials earned before July,
1, 2002 do not expire but credentials from July 1, 2002 expire in 5 years.
I am starting to learn that intense questioning backwards and forwards is a
necessary component to this database design stuff.

I also have been looking around in the VB help file. Not sure where to
start... any suggestions? There is a lot of stuff in there! Thanks for
explaining that there were 2 different help files. The only way to get to
VB help is ctrl+G?

A LOT of interaction with the users is absolutely critical
to creating a useful software application, even for a simple
spreadshet ;-)

You can get to the VBE any number of ways, but Ctrl+G is a
very quick way to jump to it from the Access window.

Back to your calculated query field. Given the new, more
precise specifications, try this kind of expression:

If(CredentialCompletionDate < #7/1/2002#,
"No Expiration", IIf(CredentialCompletionDate <
DateAdd("yyyy", -5, CredentialCompletionDate), "Expired",
Format(DateAdd("yyyy", 5, CredentialCompletionDate), "mmm d,
yyyy"))

 

[LMB]

A LOT of interaction with the users is absolutely critical
to creating a useful software application, even for a simple
spreadshet ;-)

You can get to the VBE any number of ways, but Ctrl+G is a
very quick way to jump to it from the Access window.

Back to your calculated query field. Given the new, more
precise specifications, try this kind of expression:

If(CredentialCompletionDate < #7/1/2002#,
"No Expiration", IIf(CredentialCompletionDate <
DateAdd("yyyy", -5, CredentialCompletionDate), "Expired",
Format(DateAdd("yyyy", 5, CredentialCompletionDate), "mmm d,
yyyy"))

I got an error saying the there was a missing closing parentheses, bracket
or vertical bar so I added a closing parentheses after
CredentialCompletionDate on the second line...Not sure if that was the
problem but I got another error after that "The expression you entered has a
function containing the wrong number of arguments." I have no clue what
this means, yet.

 

[John Spencer]

Rewriting the nested IIF using the SWITCH function, the following should be
correct syntactically, but I'm not sure of the logic.

SWITCH (
CredentialCompletionDate < #7/1/2002#, "No Expiration",
CredentialCompletionDate <DateAdd("yyyy", -5, CredentialCompletionDate),
"Expired",
CredentialCompletionDate >=DateAdd("yyyy", -5, CredentialCompletionDate),
Format(DateAdd("yyyy", 5, CredentialCompletionDate), "mmm d,yyyy"))

Test 1 makes sense
Test2 is always going to be FALSE
Test3 is always going to be TRUE

Perhaps you need to replace CredentialCompletionDate with Date() in the
DateAdd functions.

SWITCH (CredentialCompletionDate < #7/1/2002#, "No Expiration",
CredentialCompletionDate <DateAdd("yyyy", -5, Date()), "Expired",
CredentialCompletionDate >=DateAdd("yyyy", -5, Date()),
Format(DateAdd("yyyy", 5, CredentialCompletionDate), "mmm d,yyyy"))

 

[Marshall Barton]

"Marshall Barton" wrote


I got an error saying the there was a missing closing parentheses, bracket
or vertical bar so I added a closing parentheses after
CredentialCompletionDate on the second line...Not sure if that was the
problem but I got another error after that "The expression you entered has a
function containing the wrong number of arguments." I have no clue what
this means, yet.

The missing ) should be at the very end. There was another
error too.

If(CredentialCompletionDate < #7/1/2002#,
"No Expiration", IIf(CredentialCompletionDate <
DateAdd("yyyy", -5, Date()), "Expired",
Format(DateAdd("yyyy", 5, CredentialCompletionDate), "mmm d,
yyyy")))

However, I think John's suggestion of using the Switch
function instead of the nested IIf functions is easier to
understand. Here's a slightly simplified version:

Switch(CredentialCompletionDate < #7/1/2002#,
"No Expiration", CredentialCompletionDate <
DateAdd("yyyy", -5, Date()), "Expired", True,
Format(DateAdd("yyyy", 5, CredentialCompletionDate), "mmm d,
yyyy")

 

[Linda RQ]

The missing ) should be at the very end. There was another
error too.

If(CredentialCompletionDate < #7/1/2002#,
"No Expiration", IIf(CredentialCompletionDate <
DateAdd("yyyy", -5, Date()), "Expired",
Format(DateAdd("yyyy", 5, CredentialCompletionDate), "mmm d,
yyyy")))

However, I think John's suggestion of using the Switch
function instead of the nested IIf functions is easier to
understand. Here's a slightly simplified version:

Switch(CredentialCompletionDate < #7/1/2002#,
"No Expiration", CredentialCompletionDate <
DateAdd("yyyy", -5, Date()), "Expired", True,
Format(DateAdd("yyyy", 5, CredentialCompletionDate), "mmm d,
yyyy")

Still getting the error Missing a closing parenthese, bracket or verticle
bar. I really appreciate you sticking with me here.

Linda

 

[Linda RQ]

Marshal,

John's expression worked. I now have a bunch of expressions to look at and
it's starting to make a little sense..I think

SWITCH (CredentialCompletionDate < #7/1/2002#, "No Expiration",
CredentialCompletionDate <DateAdd("yyyy", -5, Date()), "Expired",
CredentialCompletionDate >=DateAdd("yyyy", -5, Date()),
Format(DateAdd("yyyy", 5, CredentialCompletionDate), "mmm d,yyyy"))

Thanks a million!

 

[Linda RQ]

Worked perfectly. Thanks, John.

Rewriting the nested IIF using the SWITCH function, the following should be
correct syntactically, but I'm not sure of the logic.

SWITCH (
CredentialCompletionDate < #7/1/2002#, "No Expiration",
CredentialCompletionDate <DateAdd("yyyy", -5, CredentialCompletionDate),
"Expired",
CredentialCompletionDate >=DateAdd("yyyy", -5, CredentialCompletionDate),
Format(DateAdd("yyyy", 5, CredentialCompletionDate), "mmm d,yyyy"))

Test 1 makes sense
Test2 is always going to be FALSE
Test3 is always going to be TRUE

Perhaps you need to replace CredentialCompletionDate with Date() in the
DateAdd functions.

SWITCH (CredentialCompletionDate < #7/1/2002#, "No Expiration",
CredentialCompletionDate <DateAdd("yyyy", -5, Date()), "Expired",
CredentialCompletionDate >=DateAdd("yyyy", -5, Date()),
Format(DateAdd("yyyy", 5, CredentialCompletionDate), "mmm d,yyyy"))

 

[Marshall Barton]

Still getting the error Missing a closing parenthese, bracket or verticle
bar. I really appreciate you sticking with me here.

Counting parenthesis (and quotes) can give anyone a
headache. Let's forget about that and stick with the Switch
finction for this problem. Did you note the changes I made
to use the Date() function (critical) and the simplification
to John's idea?