expiration

[Arkienpa]

Hi I'm new to Access and I'm trying to create a simple report that contains
multiple medical and certification license expiration dates. How do I create
query for all of the criteria for anything that would expire from today's
date up to 1 month out.
I do not know what SQL is and I don't understand most computer language
speak and I'm very new to access. if you can answer my question simply
please help.
Thanks so much

 

[Conan Kelly]

Arkienpa,

For us to be most helpful, you should post as much info about your DB as
possible, like table/query names (that will be involved...we don't need
every single table), field names in the tables involved, and maybe even data
types of those fields.

Will this new query be based off of a table or another query?

If you are familiar with designing queries, then in this new query, add the
fields that you want returned and then try the following as criteria for
your expiration date field:

=Date() and <= DateSerial(Year([Name of your expiration date
field]),Month([Name of your expiration date field])+1,Day([Name of your
expiration date field]))

Be sure to change "[Name of your expiration date field]" to the name of your
field, but I would leave the square brackets ([]) in just to be safe.
Depending on your field names, they might be requiered.

That will return records with a date between today's date and the same day
of the month in next month.

I have not tested this, I just typed this in to my reply from memory, so
there is always a potential for error. If for some reason Access doesn't
like "Date()", then change it to "Today()", but I think Date() is correct.

FYI, SQL is the language/code that Access uses to execute queries. When you
design a query, Access builds the SQL statement behind the scenes. When you
execute the query, it is the actual SQL statement that is executed. The
query design view is just a GUI for building SQL statements so it is easier
for people who don't know SQL to design queries. After you design a query,
you can look at the SQL statement that Access came up with. In order to do
that, in Query Design or Datasheet view, click the View menu > SQL View.
That will display the SQL statement Access created. You might try some
simple queries and look at the SQL statement just to see what is going on,
and you might learn something in the process. Gradually make the queries
more complicated. Many times those of us here helping will ask you to post
your SQL statement. Just switch to the SQL View and copy everything you see
and paste it into your post/response. Also many times we will put a SQL
statement in our response. Same thing...just copy the statement from our
response and paste it into the SQL View. After pasting, you can switch back
to Design View to see how the query was designed.

Please post back with more information if this did not work for you or you
don't understand what is going on.

HTH,

Conan

 

[Arkienpa]

Hmmm ok these are all date fields and they are all basically the same thing.
Driver's License expiration, Professional License expiration, FBI clearance
etc. What I'm really trying to create is a report that will tell me what has
expired not necessarily when they will expire.

This query is based off the existing employee database created by someone
else.

This is what I have been using-
IIf([License Exp]<=Now()+30=0,[License Exp],"not yet") Now this was given
to me by the access teacher at the local community college but when I enter
it and run the query, it gives me dates out of the range. up to years later.

I want to show licenses that expired as of today's date or have already
expired days past today + 30 days out only.

Arkienpa,

For us to be most helpful, you should post as much info about your DB as
possible, like table/query names (that will be involved...we don't need
every single table), field names in the tables involved, and maybe even data
types of those fields.

Will this new query be based off of a table or another query?

If you are familiar with designing queries, then in this new query, add the
fields that you want returned and then try the following as criteria for
your expiration date field:

=Date() and <= DateSerial(Year([Name of your expiration date
field]),Month([Name of your expiration date field])+1,Day([Name of your
expiration date field]))

Be sure to change "[Name of your expiration date field]" to the name of your
field, but I would leave the square brackets ([]) in just to be safe.
Depending on your field names, they might be requiered.

That will return records with a date between today's date and the same day
of the month in next month.

I have not tested this, I just typed this in to my reply from memory, so
there is always a potential for error. If for some reason Access doesn't
like "Date()", then change it to "Today()", but I think Date() is correct.

FYI, SQL is the language/code that Access uses to execute queries. When you
design a query, Access builds the SQL statement behind the scenes. When you
execute the query, it is the actual SQL statement that is executed. The
query design view is just a GUI for building SQL statements so it is easier
for people who don't know SQL to design queries. After you design a query,
you can look at the SQL statement that Access came up with. In order to do
that, in Query Design or Datasheet view, click the View menu > SQL View.
That will display the SQL statement Access created. You might try some
simple queries and look at the SQL statement just to see what is going on,
and you might learn something in the process. Gradually make the queries
more complicated. Many times those of us here helping will ask you to post
your SQL statement. Just switch to the SQL View and copy everything you see
and paste it into your post/response. Also many times we will put a SQL
statement in our response. Same thing...just copy the statement from our
response and paste it into the SQL View. After pasting, you can switch back
to Design View to see how the query was designed.

Please post back with more information if this did not work for you or you
don't understand what is going on.

HTH,

Conan

 

[John Spencer]

SO, do you want licenses that have expired between 30 days ago and today?

IIF([License Exp] Between DateAdd("d",-30,Date()) and Date(), [License Exp],
"Not Yet")

That will return a date string or the words Not Yet for every record in the
database.

If you are trying to get just records with the License has expired within
the last 30 days, then you need to apply criteria to the field

In the Design view of the query
Field: License Exp
Table: YourTable
Criteria: Between DateAdd("d",-30,Date()) and Date()

If you have multiple fields for licenses, then you would need to apply the
criteria against each field. However, the criteria would have to be on a
different criteria row for each license.

A better design would be to have a table for PersonalLicenses with
--PersonID - Identifies the individual from the Personnel table (value of
the primary key from the personnel table)
--LicenseType - Identifies the type of license
--ExpirationDate - When license expires
--EffectiveDate - When the license was granted/gained
plus any additional fields about the license that you wish to store (Issuing
Authority, License Number, etc)

You also might want a lookup table of License Types to ensure consistent
entry of the License type field.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hmmm ok these are all date fields and they are all basically the same
thing.
Driver's License expiration, Professional License expiration, FBI
clearance
etc. What I'm really trying to create is a report that will tell me what
has
expired not necessarily when they will expire.

This query is based off the existing employee database created by someone
else.

This is what I have been using-
IIf([License Exp]<=Now()+30=0,[License Exp],"not yet") Now this was given
to me by the access teacher at the local community college but when I
enter
it and run the query, it gives me dates out of the range. up to years
later.

I want to show licenses that expired as of today's date or have already
expired days past today + 30 days out only.

Arkienpa,

For us to be most helpful, you should post as much info about your DB as
possible, like table/query names (that will be involved...we don't need
every single table), field names in the tables involved, and maybe even
data
types of those fields.

Will this new query be based off of a table or another query?

If you are familiar with designing queries, then in this new query, add
the
fields that you want returned and then try the following as criteria for
your expiration date field:

=Date() and <= DateSerial(Year([Name of your expiration date
field]),Month([Name of your expiration date field])+1,Day([Name of your
expiration date field]))

Be sure to change "[Name of your expiration date field]" to the name of
your
field, but I would leave the square brackets ([]) in just to be safe.
Depending on your field names, they might be requiered.

That will return records with a date between today's date and the same
day
of the month in next month.

I have not tested this, I just typed this in to my reply from memory, so
there is always a potential for error. If for some reason Access doesn't
like "Date()", then change it to "Today()", but I think Date() is
correct.

FYI, SQL is the language/code that Access uses to execute queries. When
you
design a query, Access builds the SQL statement behind the scenes. When
you
execute the query, it is the actual SQL statement that is executed. The
query design view is just a GUI for building SQL statements so it is
easier
for people who don't know SQL to design queries. After you design a
query,
you can look at the SQL statement that Access came up with. In order to
do
that, in Query Design or Datasheet view, click the View menu > SQL View.
That will display the SQL statement Access created. You might try some
simple queries and look at the SQL statement just to see what is going
on,
and you might learn something in the process. Gradually make the queries
more complicated. Many times those of us here helping will ask you to
post
your SQL statement. Just switch to the SQL View and copy everything you
see
and paste it into your post/response. Also many times we will put a SQL
statement in our response. Same thing...just copy the statement from our
response and paste it into the SQL View. After pasting, you can switch
back
to Design View to see how the query was designed.

Please post back with more information if this did not work for you or
you
don't understand what is going on.

HTH,

Conan

 

[Arkienpa]

John,
thank you soo much for your excellent explanation.

I did use this
Between DateAdd("d",-30,Date()) and Date() and it is bring back info up to
2009. which isn't what I need. I'm sure I'm doing something wrong here. If
I want anything that has expired regardless of the date from today, before
today, and 30 days from today would i use a + or -?

Sorry for so many questions. I kind of got this handed to me and I've only
had access level one.

SO, do you want licenses that have expired between 30 days ago and today?

IIF([License Exp] Between DateAdd("d",-30,Date()) and Date(), [License Exp],
"Not Yet")

That will return a date string or the words Not Yet for every record in the
database.

If you are trying to get just records with the License has expired within
the last 30 days, then you need to apply criteria to the field

In the Design view of the query
Field: License Exp
Table: YourTable
Criteria: Between DateAdd("d",-30,Date()) and Date()

If you have multiple fields for licenses, then you would need to apply the
criteria against each field. However, the criteria would have to be on a
different criteria row for each license.

A better design would be to have a table for PersonalLicenses with
--PersonID - Identifies the individual from the Personnel table (value of
the primary key from the personnel table)
--LicenseType - Identifies the type of license
--ExpirationDate - When license expires
--EffectiveDate - When the license was granted/gained
plus any additional fields about the license that you wish to store (Issuing
Authority, License Number, etc)

You also might want a lookup table of License Types to ensure consistent
entry of the License type field.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hmmm ok these are all date fields and they are all basically the same
thing.
Driver's License expiration, Professional License expiration, FBI
clearance
etc. What I'm really trying to create is a report that will tell me what
has
expired not necessarily when they will expire.

This query is based off the existing employee database created by someone
else.

This is what I have been using-
IIf([License Exp]<=Now()+30=0,[License Exp],"not yet") Now this was given
to me by the access teacher at the local community college but when I
enter
it and run the query, it gives me dates out of the range. up to years
later.

I want to show licenses that expired as of today's date or have already
expired days past today + 30 days out only.

Arkienpa,

For us to be most helpful, you should post as much info about your DB as
possible, like table/query names (that will be involved...we don't need
every single table), field names in the tables involved, and maybe even
data
types of those fields.

Will this new query be based off of a table or another query?

If you are familiar with designing queries, then in this new query, add
the
fields that you want returned and then try the following as criteria for
your expiration date field:

=Date() and <= DateSerial(Year([Name of your expiration date
field]),Month([Name of your expiration date field])+1,Day([Name of your
expiration date field]))

Be sure to change "[Name of your expiration date field]" to the name of
your
field, but I would leave the square brackets ([]) in just to be safe.
Depending on your field names, they might be requiered.

That will return records with a date between today's date and the same
day
of the month in next month.

I have not tested this, I just typed this in to my reply from memory, so
there is always a potential for error. If for some reason Access doesn't
like "Date()", then change it to "Today()", but I think Date() is
correct.

FYI, SQL is the language/code that Access uses to execute queries. When
you
design a query, Access builds the SQL statement behind the scenes. When
you
execute the query, it is the actual SQL statement that is executed. The
query design view is just a GUI for building SQL statements so it is
easier
for people who don't know SQL to design queries. After you design a
query,
you can look at the SQL statement that Access came up with. In order to
do
that, in Query Design or Datasheet view, click the View menu > SQL View.
That will display the SQL statement Access created. You might try some
simple queries and look at the SQL statement just to see what is going
on,
and you might learn something in the process. Gradually make the queries
more complicated. Many times those of us here helping will ask you to
post
your SQL statement. Just switch to the SQL View and copy everything you
see
and paste it into your post/response. Also many times we will put a SQL
statement in our response. Same thing...just copy the statement from our
response and paste it into the SQL View. After pasting, you can switch
back
to Design View to see how the query was designed.

Please post back with more information if this did not work for you or
you
don't understand what is going on.

HTH,

Conan

 

[John Spencer]

Ok,
First what type of field are you applying the criteria against? It should
be a dateTime field. If it is a text field that is storing the date as a
string then you will get the wrong results. As Access will try to be
helpful and will convert the criteria to strings and search for date strings
between the two strings.

"02/18/ 2009" Is Between "01/28/07" and "02/28/07" as strings. As Dates
it is not.

If your field is a text field, then you can convert the field in the query,
convert it permanently by changing the field type (back up your data first).

You can try the following in a query.

Field: DateValue([YourDateFieldThatIsText])
Criteria: Between DateAdd("d",-30,Date()) and Date()

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

John,
thank you soo much for your excellent explanation.

I did use this
Between DateAdd("d",-30,Date()) and Date() and it is bring back info up to
2009. which isn't what I need. I'm sure I'm doing something wrong here.
If
I want anything that has expired regardless of the date from today, before
today, and 30 days from today would i use a + or -?

Sorry for so many questions. I kind of got this handed to me and I've
only
had access level one.

SO, do you want licenses that have expired between 30 days ago and today?

IIF([License Exp] Between DateAdd("d",-30,Date()) and Date(), [License
Exp],
"Not Yet")

That will return a date string or the words Not Yet for every record in
the
database.

If you are trying to get just records with the License has expired within
the last 30 days, then you need to apply criteria to the field

In the Design view of the query
Field: License Exp
Table: YourTable
Criteria: Between DateAdd("d",-30,Date()) and Date()

If you have multiple fields for licenses, then you would need to apply
the
criteria against each field. However, the criteria would have to be on a
different criteria row for each license.

A better design would be to have a table for PersonalLicenses with
--PersonID - Identifies the individual from the Personnel table (value
of
the primary key from the personnel table)
--LicenseType - Identifies the type of license
--ExpirationDate - When license expires
--EffectiveDate - When the license was granted/gained
plus any additional fields about the license that you wish to store
(Issuing
Authority, License Number, etc)

You also might want a lookup table of License Types to ensure consistent
entry of the License type field.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hmmm ok these are all date fields and they are all basically the same
thing.
Driver's License expiration, Professional License expiration, FBI
clearance
etc. What I'm really trying to create is a report that will tell me
what
has
expired not necessarily when they will expire.

This query is based off the existing employee database created by
someone
else.

This is what I have been using-
IIf([License Exp]<=Now()+30=0,[License Exp],"not yet") Now this was
given
to me by the access teacher at the local community college but when I
enter
it and run the query, it gives me dates out of the range. up to years
later.

I want to show licenses that expired as of today's date or have already
expired days past today + 30 days out only.

:

Arkienpa,

For us to be most helpful, you should post as much info about your DB
as
possible, like table/query names (that will be involved...we don't
need
every single table), field names in the tables involved, and maybe
even
data
types of those fields.

Will this new query be based off of a table or another query?

If you are familiar with designing queries, then in this new query,
add
the
fields that you want returned and then try the following as criteria
for
your expiration date field:

=Date() and <= DateSerial(Year([Name of your expiration date
field]),Month([Name of your expiration date field])+1,Day([Name of
your
expiration date field]))

Be sure to change "[Name of your expiration date field]" to the name
of
your
field, but I would leave the square brackets ([]) in just to be safe.
Depending on your field names, they might be requiered.

That will return records with a date between today's date and the same
day
of the month in next month.

I have not tested this, I just typed this in to my reply from memory,
so
there is always a potential for error. If for some reason Access
doesn't
like "Date()", then change it to "Today()", but I think Date() is
correct.

FYI, SQL is the language/code that Access uses to execute queries.
When
you
design a query, Access builds the SQL statement behind the scenes.
When
you
execute the query, it is the actual SQL statement that is executed.
The
query design view is just a GUI for building SQL statements so it is
easier
for people who don't know SQL to design queries. After you design a
query,
you can look at the SQL statement that Access came up with. In order
to
do
that, in Query Design or Datasheet view, click the View menu > SQL
View.
That will display the SQL statement Access created. You might try
some
simple queries and look at the SQL statement just to see what is going
on,
and you might learn something in the process. Gradually make the
queries
more complicated. Many times those of us here helping will ask you to
post
your SQL statement. Just switch to the SQL View and copy everything
you
see
and paste it into your post/response. Also many times we will put a
SQL
statement in our response. Same thing...just copy the statement from
our
response and paste it into the SQL View. After pasting, you can
switch
back
to Design View to see how the query was designed.

Please post back with more information if this did not work for you or
you
don't understand what is going on.

HTH,

Conan