Work with DateAdd and DateDiff in query

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

OK Assuming that current date is always greater than F38 the following will work with the parameters you've listed.

SELECT ...,
DateDiff("d",[F38],now()) AS cntDays,
Switch([cntDays]>46,2,[cntdays]>25,3,[cntdays]>21,1,[cntdays]>7,0) AS dayCode

FROM ...

Switch will return the first True it finds so you'll need to put your max days in descending order as shown.

Since you didn't specify a value for when the difference is 7 or less this will return NULL.

So, I import an Excel table into access. and F38 is the date when a produkt is produced. F4 in sprodukt number. According with F38, i must calculate a period of, 7, 18, 21 and again 21 for each department im part. And i must set a priority according with this(i didn't make all the necessary fields):
-when actual date ist less than priority date 1 than y = 0
- when actual date ist less than priority date 2 but bigger than priority date 1 than y = 1, and so further

So i have 3 extra fields for priority.

Important for me is to have an example query how to work with date in my case.
With this date i have to work later with some traffic lights. at this moment i want only to calculate the difference betwenn actual date and x1, x2, x3, x4.

I don-t know how to make, or with insert or with update, i have tried but nothing.. I only whant to write in this fields a, x1, x2, x3, x4, and y the values according with calculations based on F38, for each record.
I want to make a query because i import every day the same excel table, that contains new produkt, or modificated produkt. but F38 remain always the same F38 for each produkt.

Dan

Ok well if you do an insert or an update depends on your table structure.

In my earlier query I assumed you just needed the priority and didn't care about the intermediate values of x1..4 so if you need those values there are 2 options. First is to create a query that just gives the necessary values and then create a second query to do the update or insert or if you want just a single query

Insert into tblPriority (F4, a, x1, x2, x3, x4, y)
Select F4, now() as a, DateAdd("d", 7, F38) as x1, DateAdd("d", 15, F38) as x2, DateAdd("d", 21, F38) as x3, DateAdd("d", 42, F38) as x4, Switch (a > x4, 4, a > x3, 3, a > x2, 2, a > 1, 1, a <= x1, 0) as y
From tblImport

So the dateAdd functions give you:

x1 = F38 + 7
x2 = F38 + 15
x3 = F38 + 21
x4 = F38 + 42

and the Switch function gives you:

If a > x4 then y = 4
If a > x3 and <= x4 then y = 3
if a > x2 and <= x3 then y = 2
if a > x1 and <= x2 then y = 1
if a <= x1 then y = 0

If I didn't get the intervals right you can correct as necessary.

Very good post. Thanks

UPDATE tblDate SET tblDate.time4 = Now(), tblDate.prio = Switch(Now()>[time3],4,Now()>[time2],3,Now()>[time1],2,Now()<=[time1],1), tblDate.F39 = DateAdd("d",7,[F14]), tblDate.F40 = Now(), tblDate.F48 = DateAdd("d",25,[F14]), tblDate.F49 = Now(), tblDate.F57 = DateAdd("d",46,[F14]), tblDate.F58 = Now(), tblDate.F64 = DateAdd("d",14,[F14]), tblDate.F65 = Now();


Dan