More tbl Design for expriation date

  • Thread starter Thread starter LMB
  • Start date Start date
L

LMB

Hi Everyone,

Need a report or query to show all employees, their credentials and when
their credentials expire. If an employee got credentialed before July 1,
2002, their credentials do not expire. Some employees have some credentials
before this date and also have some after this date. Some employees have
all credentials before this date and then some have all credentials after
this date.

I was wondering if there is a way to add an if/then statement to a query to
return a future date? I am using Access 2000. Originally I was going to
just enter in expiration dates for credential expiration dates and for the
employees who do not have expiration dates because they were credentialed
before July 1, 2002 I was going to use the year 1/1/2060. Employees who
don't have a particular credential will have a blank expiration date field.
Even though the users swear that they will not need to have on file when the
individual was credentialed, I think they may at some time want to see this.
When I started this database, they said they would not need to have
credentials in there either since license is what is required to work in our
state and not credentials. I wanted to calculate the expiration dates in a
query. I am making a crosstab query which works great if I only have an
expiration date and use the tables for the data but I don't know how to
begin this if I calculate the date. Heck, I'm looking up how to calculate
the expiration date at this moment.

1. Will a calculated field work in a crosstab query or do I need to make a
regular query first?

2. What would my calculation be if I wanted the date 1/1/2060 returned for
any credentials created before July 1, 2002 or can this even be done because
dates after this will be +5 years from credential completion date?

Here are my tables for what I think I should do

tblCredentials
CredentialsID (PK)
CredentialsType (fk)
tblEmpID (fk from my tblEmployee)
CredentialCompletionDate

tblCredentialTypes
CredentialTypesID

I have a crosstab query working but this is only using the
CredentialExpirationDate in it and just entering the future date for those
people who are "grandfathered". I was just trying to do it like a "real
developer". would and use a calculated field based on the completion date
for my data. So, if there is going to be a lot of complicated code to get
this to work, I'll just keep it the way it is.

Thanks,
Linda
 
To calculate the expiration date, create a simple function that accepts the
CredentialCompletionDate as an argument and returns a expiration date:

Function GetExpireDate(datComplete as Date) as Date
If datComplete < #7/1/2002# Then
GetExpireDate = #1/1/2060#
Else
GetExpireDate = DateAdd("yyyy",5,datComplete)
End If
End Function

I would not create an IIf() expression in a query since this calculation
will probably change some day. It's best to keep your business calculations
in a designated module.

You can use functions like this in Crosstab queries.
 
Thanks, Duane. I am not sure how to work with functions yet. I'll look
around for a simple tutorial because it's time that I learn this. I have
links to a lot of the MVP sites there has to be something there.

Linda
 
For starters using code, create a new, blank module and copy the code that I
provided (Function.... End Function). Test the function by opening the debug
window (press Ctrl+G) and enter:
+---------------------------
| ? GetExpireDate(#6/22/2002#)
|

Then save the module with a name like "modBusinessCalcs". You can then use
the code in a query like you would use any other function:

ExpireDate: GetExpireDate([CredentialCompletionDate])
 
Hi Duane,

Sorry, I got a little busy with life. Ok, I created a new blank database to
put this in. I managed to find the modules and I selected New, and pasted
your initial function in there. I named it "modBusinessCalcs" but I can't
figure out how to get into a debug window. I see the debug tool button but
no window opens. I can select step into, add watch, Quick watch, toggle
breakpoint, clear all breakpoints. None seem to do anything when I select
them. I looked around in help and thought the immediate window is what you
are talking about and I see that just below my module window but when I
typed ctrl+G+enter, nothing happens.

Linda

Duane Hookom said:
For starters using code, create a new, blank module and copy the code that
I provided (Function.... End Function). Test the function by opening the
debug window (press Ctrl+G) and enter:
+---------------------------
| ? GetExpireDate(#6/22/2002#)
|

Then save the module with a name like "modBusinessCalcs". You can then use
the code in a query like you would use any other function:

ExpireDate: GetExpireDate([CredentialCompletionDate])

--
Duane Hookom
MS Access MVP
--

LMB said:
Thanks, Duane. I am not sure how to work with functions yet. I'll look
around for a simple tutorial because it's time that I learn this. I have
links to a lot of the MVP sites there has to be something there.

Linda
Click to expand...
Click to expand...
 
The immediate window is the same as the debug window. Type what I suggested
into the debug/immediate window.

--
Duane Hookom
MS Access MVP
--

LMB said:
Hi Duane,

Sorry, I got a little busy with life. Ok, I created a new blank database
to put this in. I managed to find the modules and I selected New, and
pasted your initial function in there. I named it "modBusinessCalcs" but
I can't figure out how to get into a debug window. I see the debug tool
button but no window opens. I can select step into, add watch, Quick
watch, toggle breakpoint, clear all breakpoints. None seem to do anything
when I select them. I looked around in help and thought the immediate
window is what you are talking about and I see that just below my module
window but when I typed ctrl+G+enter, nothing happens.

Linda

Duane Hookom said:
For starters using code, create a new, blank module and copy the code
that I provided (Function.... End Function). Test the function by opening
the debug window (press Ctrl+G) and enter:
+---------------------------
| ? GetExpireDate(#6/22/2002#)
|

Then save the module with a name like "modBusinessCalcs". You can then
use the code in a query like you would use any other function:

ExpireDate: GetExpireDate([CredentialCompletionDate])

--
Duane Hookom
MS Access MVP
--

LMB said:
Thanks, Duane. I am not sure how to work with functions yet. I'll look
around for a simple tutorial because it's time that I learn this. I
have links to a lot of the MVP sites there has to be something there.

Linda

To calculate the expiration date, create a simple function that accepts
the CredentialCompletionDate as an argument and returns a expiration
date:

Function GetExpireDate(datComplete as Date) as Date
If datComplete < #7/1/2002# Then
GetExpireDate = #1/1/2060#
Else
GetExpireDate = DateAdd("yyyy",5,datComplete)
End If
End Function

I would not create an IIf() expression in a query since this
calculation will probably change some day. It's best to keep your
business calculations in a designated module.

You can use functions like this in Crosstab queries.

--
Duane Hookom
MS Access MVP
--

Hi Everyone,

Need a report or query to show all employees, their credentials and
when their credentials expire. If an employee got credentialed before
July 1, 2002, their credentials do not expire. Some employees have
some credentials before this date and also have some after this date.
Some employees have all credentials before this date and then some
have all credentials after this date.

I was wondering if there is a way to add an if/then statement to a
query to return a future date? I am using Access 2000. Originally I
was going to just enter in expiration dates for credential expiration
dates and for the employees who do not have expiration dates because
they were credentialed before July 1, 2002 I was going to use the year
1/1/2060. Employees who don't have a particular credential will have a
blank expiration date field. Even though the users swear that they
will not need to have on file when the individual was credentialed, I
think they may at some time want to see this. When I started this
database, they said they would not need to have credentials in there
either since license is what is required to work in our state and not
credentials. I wanted to calculate the expiration dates in a query. I
am making a crosstab query which works great if I only have an
expiration date and use the tables for the data but I don't know how
to begin this if I calculate the date. Heck, I'm looking up how to
calculate the expiration date at this moment.

1. Will a calculated field work in a crosstab query or do I need to
make a regular query first?

2. What would my calculation be if I wanted the date 1/1/2060 returned
for any credentials created before July 1, 2002 or can this even be
done because dates after this will be +5 years from credential
completion date?

Here are my tables for what I think I should do

tblCredentials
CredentialsID (PK)
CredentialsType (fk)
tblEmpID (fk from my tblEmployee)
CredentialCompletionDate

tblCredentialTypes
CredentialTypesID

I have a crosstab query working but this is only using the
CredentialExpirationDate in it and just entering the future date for
those people who are "grandfathered". I was just trying to do it like
a "real developer". would and use a calculated field based on the
completion date for my data. So, if there is going to be a lot of
complicated code to get this to work, I'll just keep it the way it is.

Thanks,
Linda
Click to expand...
Click to expand...
Click to expand...
 
Back
Top