Re: expiration
- From: Arkienpa <Arkienpa@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 28 Feb 2008 10:44:00 -0800
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.
"John Spencer" wrote:
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
..
"Arkienpa" <Arkienpa@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8D37434A-8018-477F-8B42-0E04961292E8@xxxxxxxxxxxxxxxx
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.
"Conan Kelly" wrote:
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
- Follow-Ups:
- Re: expiration
- From: John Spencer
- Re: expiration
- References:
- Re: expiration
- From: Conan Kelly
- Re: expiration
- From: Arkienpa
- Re: expiration
- From: John Spencer
- Re: expiration
- Prev by Date: Re: Criteria in Query
- Next by Date: Need to upate union query statement every month
- Previous by thread: Re: expiration
- Next by thread: Re: expiration
- Index(es):
Relevant Pages
|
