Date calculation query

[Guest]

Hi
Please forgive my insistant nature, but i do need help. I need to create a
query that calculates (for example) a date 3 months prior to the one entered.
For instance, i have a field called 'Passport Expiry' which has a date value
entered which i need to have a query work out when i need the employee to
renew his passport roughly 3 months prior to its expiration date.

Once this is working, i then need to look at how to make it an automatic
pop-up warning which will show when i open the database each morning, if
there is someone who i need to contact to inform them to now renew their
passport. this part is just a bonus, my real concern is trying to get the
query mentioned above to work properly. I realise i have asked this question
before, but alas, was not able to find the right answer. so hopefully asking
it differently will help. Thank youfor your time and understanding. Sorry for
being so persistent.

Kind regards
Rigby

 

[Vjeran]

you can make query which shows all passports that have 3 months until they
expire:
field: expiry_date
criteria: >now() - 30

on your startup form you can add in properties
onload
Docmd.openquery=name

so every time you enter your database access will open query with that
list...

i hope this will help you a bit....

 

[Van T. Dinh]

I guess what you want is to create a Query that list the employees whose
passport expiry date stored in the Table will be in the next 3 months.

If it is the case, you can set up the criteria something like:

....
WHERE [ExpiryDate] BETWEEN Date() and DateAdd("m", 3, Date())

Check Access VB Help on the Date() and DateAdd() function.

 

[Guest]

Hi
This doesnt seem to work properly. It is still giving me all the expiration
dates regardless of when they are. My field is called 'PassportExpiry' in a
table called 'TravelDocs'. I think you understand me but i need rather
specific help as I am fairly new to this all still. When you say 'now()' what
is that referring to and must i adapt that to a name of my own field or is it
an expression word. must i put something in the brackets?
Thank you for your help

Rigby

 

[Guest]

I would just like to thank you all for your help.
I have been able to figure it out with the help of another colleague along
with your advice. I am posting my result which works for anyone else who may
need similar help.

SELECT [SW ID No], [Passport Expiry Date]
FROM [Travel Docs]
WHERE (([Travel Docs].[Passport Expiry Date] BETWEEN Now() AND (Now()+90)))

This SQL Query gives me a list of all employee's whose Passport expires in
the next 90 days. So far it works. Enjoy.

Kind Regards
Rigby

 

[fredg]

I would just like to thank you all for your help.
I have been able to figure it out with the help of another colleague along
with your advice. I am posting my result which works for anyone else who may
need similar help.

SELECT [SW ID No], [Passport Expiry Date]
FROM [Travel Docs]
WHERE (([Travel Docs].[Passport Expiry Date] BETWEEN Now() AND (Now()+90)))

This SQL Query gives me a list of all employee's whose Passport expires in
the next 90 days. So far it works. Enjoy.

Kind Regards
Rigby

The function Now() includes a time value as well as the data value,
i.e. 1/3/2006 09:38:00 AM.
If you use Now() -30, the records returned by the query may vary
according to the time of day you run the query, (it will exclude
records of that day 30 days previous, if the time is before the time
you run the query.
Use Date() instead.
Date() does not include a time value (or rather the time is midnight)
and will return the proper records no matter what time of day you run
the query.

WHERE [Travel Docs].[Passport Expiry Date] BETWEEN Date() AND
Date()+90