I'M LOST.... AGAIN....

[scott_baird]

Hello... Once again, I'm stuck doing something I KNOW I've done
before, but I simply can't make it work this time in my query:

FIELD A is "date tested"
FIELD B is "number of months until next test" i.e., 12, 24, 72
EXPR1 is a dateadd (A+B = "Date test due")

I need my query to only display records where EXPR1 is a date that is
within the next 12 months. Some of these tests aren't due for 13
months or more, and I just need to have a report that shows "Date test
due" that are in the next 12 months (from "date())
Thanks in advance.

V/r
Scott

[PC Datasheet]

Put the following expression in a blank field in your query:
DateTestDue:DateAdd("m",[Field B],[Field A])

Put the following expression in the criteria of that field:
<= DateAdd("m",12,Date())

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
<sc*********@japan.com> wrote in message
news:11**********************@c13g2000cwb.googlegr oups.com...

Hello... Once again, I'm stuck doing something I KNOW I've done
before, but I simply can't make it work this time in my query:

FIELD A is "date tested"
FIELD B is "number of months until next test" i.e., 12, 24, 72
EXPR1 is a dateadd (A+B = "Date test due")

I need my query to only display records where EXPR1 is a date that is
within the next 12 months. Some of these tests aren't due for 13
months or more, and I just need to have a report that shows "Date test
due" that are in the next 12 months (from "date())
Thanks in advance.

V/r
Scott

[Br]

sc*********@japan.com wrote:

Hello... Once again, I'm stuck doing something I KNOW I've done
before, but I simply can't make it work this time in my query:

FIELD A is "date tested"
FIELD B is "number of months until next test" i.e., 12, 24, 72
EXPR1 is a dateadd (A+B = "Date test due")

I need my query to only display records where EXPR1 is a date that is
within the next 12 months. Some of these tests aren't due for 13
months or more, and I just need to have a report that shows "Date test
due" that are in the next 12 months (from "date())
Thanks in advance.

V/r
Scott

SELECT *
FROM tblMyTable
WHERE DateDiff("m", DateAdd("m", [Date Tested], [MonthsUntilTest]),
Date())
BETWEEN 0 and 12

??

Not sure that will work.. but it should give you something to start on
:) There's probably a better approach too.

Br@dley

[Loki]

I end up with a "Data type mismatch in criteria expression".

[Br]

Loki wrote:

I end up with a "Data type mismatch in criteria expression".

Which means your trying to compare a value of one type with a value of
another incompatible type.

Br@dley

[PC Datasheet]

Check your table that contains Field B. Field B needs to be a Number
(Integer) data type not text.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"Loki" <sc*********@japan.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

I end up with a "Data type mismatch in criteria expression".