Date criteria based on another tables date 1

[jw5107]

I have two tables in a query. Joined by fldTAIL. One table is aircraft delays and the other is aircraft parts needed for the delays. Both tables have a DATE field in date/time data type. I need to get all the parts needed for the delay (based on the fldTAIL join) 1 day prior to the delay DATE.

Any suggestions?? DateAdd or DateDiff or etc..... and then how??

Thanks in advance,
jw

 

[KenReay]

Hi

If I understand your question correctly

SELECT ...whatever ... WHERE tblParts.[datDate] < DateAdd("d",-1,tblDelay.[datDate])

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[jw5107]

KenReay,

Thanks for the fast response!! This is exactly what I needed!!! Thanks!! Star for you!!

jw5107

 

[jw5107]

KenReay,

After further tests.....

Its not working the way I thought!!!

When I put this: <DateAdd("d",-1,[Delays]![DATE]) as criteria in a query under the DATE field from the parts needed table - I get parts that are SEVERAL days prior to the delay DATE not just 1.

Any fixes???

jw

 

[KenReay]

Hi

well yes

just look at the criteria

< DateAdd("d",-1,[Delays]![DATE])

if you want only 1 day less and nothing else

= DateAdd("d",-1,[Delays]![DATE])



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[jw5107]

KenReay,

I need all parts needed one day less than Delays DATE and all parts needed greater than Delays DATE.
Is that: >= DateAdd("d",-1,[Delays]![DATE]) ??

I'm just havin' a hard time!!!

Thanks for your help!!
jw