View Full Version : Work with DateAdd and DateDiff in query
dand_dd
04-21-2008, 08:00 AM
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
04-21-2008, 03:02 PM
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
04-23-2008, 03:52 AM
Thanks for advice. I will try.
Dan
ben.oates
04-23-2008, 04:55 AM
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
04-25-2008, 02:25 AM
Thank's. I will try.
Dan
DarkSprout
04-25-2008, 03:17 AM
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?
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.