Work with DateAdd and DateDiff in query

[dand_dd]

Hi. I want to make a query (or more) to calculate DateDiff and DateAdd into one table. I have tried but nothing works.

a ? is actual date =Date()
x1 ? is Date F38+ 7 Days = DateAdd(?d?, 7, F38)
x2 ? is Date F38+ 25 Days = DateAdd(?d?,25, F38)
x3 ? is Date F38 +46 Days = DateAdd(?d?,46, F38)
x4 ? is Date F38+ 21 Days = DateAdd(?d?,21, F38)

y have value 0 when a> x1,
y have value 1 when a> x2,
y have value 2 when a> x3,
y have value 3 when a> x4,

Can somebody help me?


Dan

[Trevor]

Try changeing your date formate to a more convential format ie (mm/dd/yyy) then perform your date dif and put datediff result into a variable Then use the variable in your query

[dand_dd]

Thanks for advice. I will try.

Dan

[ben.oates]

I'm not entirely sure what you're trying to achieve here dand_dd, unless the table you have us there is an output of a query you already made.

I would only have columns F4 and F38 as the others will get in the way for the code I'm about to give you below. If I'm wrong, replace Now() with a and remove y so that it can be added just in the query. All of these columns were empty when I looked.

SELECT F4, F38, SWITCH(DateAdd("d", 46, F38) < Now(), 2,DateAdd("d", 25, F38) < Now(), 1,DateAdd("d", 21, F38) < Now(),3,DateAdd("d", 7, F38) < Now(), 0) As Y FROM date;

This seems to work after I deleted all but the last 2 columns.

[dand_dd]

Thank's. I will try.

Dan

[DarkSprout]

Here is my solution to your problem -

SELECT Now() AS Ta, DateAdd("d",7,[F38]) AS Tx1, DateAdd("d",25,[F38]) AS Tx2, DateAdd("d",46,[F38]) AS Tx3, DateAdd("d",21,[F38]) AS Tx4, IIf([Ta]>[Tx1],0,IIf([Ta]>[Tx1],1,IIf([Ta]>[Tx2],2,IIf([Ta]>[Tx3],3,IIf([Ta]>[Tx4],0,-1))))) AS Ty, date.F38
FROM [date];

Turn the above into an UPDATE query, if you whish to write back to the table.

one issue I have, is with

y have value 0 when a> x1,
y have value 1 when a> x2,
y have value 2 when a> x3,
y have value 3 when a> x4,

x1, x2, x3 and x4 have all had a DateAdd Function applied, they will allways be greater than y - All of them, so why the test?