date query

[kevcar40]

hi
i have a field call testdate
what i would like to do is
return all the records that are due for a retest in 10 days or less
but greater than 5 days

in another query i would also like to return all the records that are
due for retest in 5 days or less

in another query i would also like to return all the records that have
passed the retest date

i have tried <=date()+5 but it not workig correctly
what the best method to use?


thanks

kev

kevin

 

[Jeff Boyce]

Kevin

"not working correctly" doesn't give us much to go on ...

If you are ONLY checking for "<=Date() + 5, how is Access supposed to know
the "10 days or less" part?

That one sounds like (untested):

(Date() + 5) And (<Date() + 10)

The second sounds like (also untested):

<=Date() + 5

.... and (untested):

<Date()


Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[fredg]

hi
i have a field call testdate
what i would like to do is
return all the records that are due for a retest in 10 days or less
but greater than 5 days

As criteria on the [TestDate] column:
Between Date() + 5 and Date() + 10

in another query i would also like to return all the records that are
due for retest in 5 days or less

What if the [TestDate] is the current date?
Is that one of the "or less" dates?

Between Date() and Date() + 5

or would
Between Date() +1 and Date() + 5
be more accurate?

in another query i would also like to return all the records that have
passed the retest date

As criteria use:
< Date()

 

[James A. Fortune]

Kevin:

There are a number of ways you could do it, but the following should return
all rows where testdate is between 5 and 10 days from the current date:

SELECT *
FROM YourTable
WHERE testdate BETWEEN
DATEADD("d", 5, DATE()) AND
DATEADD("d", 10, DATE());

The following should return all rows where testdate is within the next 5 days.


SELECT *
FROM YourTable
WHERE testdate BETWEEN
DATE() AND
DATEADD("d", 5, DATE());

The following should return all rows where the testdate is before the current
date:

SELECT *
FROM YourTable
WHERE testdate < DATE();

A potential fly in the ointment when using a BETWEEN….AND operation is that
if any of the testdate values inadvertently include a non-zero time of day
element (there is no such thing in Access as a date vale, only a date/time
value) dates on the final day of a range with a non-zero time of day element
won't be returned. If a field is intended to accept only a date and not the
time of day it’s a good idea to give it a Validation Rule in table design
view of:

Is Null Or [testdate]=DateValue([testdate])

This allows Nulls or a date with a zero time of day, which is what you are
entering if you enter a date only. NB: the square brackets are necessary here.
Should you find that you can't save the table with the Validation Rule that
means that there is at least one date with a non-zero time of day. In which
case you can correct them with an update query like so:

UPDATE YourTable
SET testdate = DATEVALUE(testdate)
WHERE testdate IS NOT NULL;

Ken Sheridan
Stafford, England

Nice answer!

James A. Fortune
(e-mail address removed)

You use Microsoft Windows Preinstallation Environment (Windows PE) 2.1
to start computers, which is similar to using MS-DOS in the old days.
Windows PE allows you to fully automate the preparation and installation
process.

....

Windows PE is freely available as part of the Windows Automated
Installation Kit (Windows AIK).

....

Microsoft recommends that you use [Microsoft Deployment Toolkit (MDT)
2008] to generate Windows PE images in most cases.

Windows PE, which is supplied with Windows Vista and in the Windows AIK,
is the installation engine for Windows Vista. It is directly bootable
from CD, DVD, and USB Flash Drives (UFDs). You can also start Windows
PE by using Windows Deployment Services (Windows DS) and the Preboot
Execution Environment (PXE) extensions to DHCP (if supported by the
network adapters of your computers).

-- Windows Vista Resourse Kit, 2nd Edition, Microsoft Press 2008

 

[Nancy Major]

I don't think I am sure about this news group. Can you give me more info on
what this news group is all about and what is it used for?

Thank you James.

Ken Sheridan
Stafford, England

Kevin:

[quoted text clipped - 44 lines]

Ken Sheridan
Stafford, England

Nice answer!

James A. Fortune
(e-mail address removed)

You use Microsoft Windows Preinstallation Environment (Windows PE) 2.1
to start computers, which is similar to using MS-DOS in the old days.
Windows PE allows you to fully automate the preparation and installation
process.

...

Windows PE is freely available as part of the Windows Automated
Installation Kit (Windows AIK).

...

Microsoft recommends that you use [Microsoft Deployment Toolkit (MDT)
2008] to generate Windows PE images in most cases.

Windows PE, which is supplied with Windows Vista and in the Windows AIK,
is the installation engine for Windows Vista. It is directly bootable
from CD, DVD, and USB Flash Drives (UFDs). You can also start Windows
PE by using Windows Deployment Services (Windows DS) and the Preboot
Execution Environment (PXE) extensions to DHCP (if supported by the
network adapters of your computers).

-- Windows Vista Resourse Kit, 2nd Edition, Microsoft Press 2008