Expiry dates query

Dear all,
I have a list of employees along with a list of expiry days
example

ID Passport expiry date Driving License expiry Date
Insurance Expiry Date
5001 11/7/2009
11/9/2010 11/7/2009
5002 1/12/2010
11/9/2010 11/7/2009

I have used the following :Format(Date(),"yyyymm") and
=(DateAdd("h",-24,Now()))

i can only put it in one in of the expiry dates, what i need is an
expression that gives me todays expiry dates by ID

One reason you're having trouble trying to set this up is because your table
structure is not normalized -- meaning you have a separate field for each
type of date. What you need is a record for each type of date.


tblDocumentType
DocumentTypeID
DocumentTypeDescription

tblEmployeeDocumentType
ID
DocumentTypeID
ExpiryDate


You put the different types of documents into tblDocumentType (e.g.,
Passport, Driving License, Insurance). Then you put a record in
tblEmployeeDocumentType for each unique combination of employee/document
type, and the ExpiryDate field holds the value for that type of document for
that employee.

Then your query is very simple to do. You put the criterion on the
ExpiryDate field, and the query will return all types where the criterion is
met for an employee.

SELECT * FROM tblEmployeeDocumentType
WHERE ExpiryDate =(DateAdd("h",-24,Now()))


Using your current setup, you'd have to put the =(DateAdd("h",-24,Now()))
expression on all three expiry date fields in your table, using OR logic,
but you'd have to return all the date fields in the query.

Another way of using your current setup is to use a UNION query, where each
subquery in the UNION query returns the value of one field (note: you cannot
build this type of query in the Grid View, it must be built in SQL View):

SELECT ID, [Passport expiry date] AS ExpiryDate, "Passport" AS DocumentType
FROM YourTableName
WHERE [Passport expiry date] =(DateAdd("h",-24,Now()))
UNION ALL
SELECT ID, [Driving License expiry Date] AS ExpiryDate, "Passport" AS
DocumentType
FROM YourTableName
WHERE [Driving License expiry Date] =(DateAdd("h",-24,Now()))
UNION ALL
SELECT ID, [Insurance Expiry Date] AS ExpiryDate, "Passport" AS DocumentType
FROM YourTableName
WHERE [Insurance Expiry Date] =(DateAdd("h",-24,Now()))


--

Ken Snell
http://www.accessmvp.com/KDSnell/



"Tia" wrote in message
...
Dear all,
I have a list of employees along with a list of expiry days
example

ID Passport expiry date Driving License expiry Date
Insurance Expiry Date
5001 11/7/2009
11/9/2010 11/7/2009
5002 1/12/2010
11/9/2010 11/7/2009

I have used the following :Format(Date(),"yyyymm") and
=(DateAdd("h",-24,Now()))

i can only put it in one in of the expiry dates, what i need is an
expression that gives me todays expiry dates by ID