4 weeks?

[Poot]

Hello,

I am trying to run a report from a query that will show me cancellation dates that are 4 weeks before an arrival date for my booking system.

I have a cancellation field and I have an arrival field. I have in the query "Is Not Null" as a criteria for cancellation so as only to pick out the records that have a cancellation date.

I have tried to mess about to have a criteria in the arrival date so as to only pick out those records that have the cancellation date 4 weeks (28 days) before the arrival.

Can anyone point me in the right direction - I have nothing but a sore head!!

Thank you for any reply,

Regards,
Poot.

 

[C-PRS]

Between #4/1/2003# And #4/22/2003# sample 1
>3/01/2003 sample 2

 

[raskew]

Assume you want this to be dynamic, ie, no hard-coded dates. If so, try copying/pasting this example into a new query in Northwind. It uses the Orders table with OrderDate representing your Cancel Date and the ShippedDate as your Arrival Date. Once you've got it up in the query grid, click on the OrderDate field, bring up properties and assign CancelDte as the caption. Do the same with ShippedDate, captioning it ArrivalDte.

SELECT Orders.OrderID, Orders.OrderDate AS CancelDte, Orders.ShippedDate AS ArriveDte, 
DateDiff("d",[CancelDte],[ArriveDte]) AS NumDays
FROM Orders
WHERE ((Not (Orders.OrderDate) Is Null And (Orders.OrderDate)<=[shippeddate]-28));

Hopefully the example will be enough to point you in the right direction. If not, please post back.

Bob

 

[Poot]

Thank you

I have now in my cancel criteria:

Is Not Null And <=[Arrival Date]-28

...which would appear to work...

Thank you for your time and expertise. Very much appreciated.


Regards,
Poot.