1

I am using MS Query in Excel 2010. I have two tables and I am appending them together. colG is then added. ColA has dates and in colG I want to diplay a message about how long ago the date is from the current date. I am having issues using DateDiff within a switch statement. Any ideas what is wrong? Here is what I have so far:

SELECT `Sheet1$`.colA, `Sheet1$`.colB, 
Switch(

1<DateDiff('m', #colA#, CURRENT_TIMESTAMP) < 2, '1-2 months',

2<DateDiff('m', #colA#, CURRENT_TIMESTAMP) < 4, '2-4 months',

4<DateDiff('m', #colA#, CURRENT_TIMESTAMP)< 6, '4-6 months',

6<DateDiff('m', #colA#, CURRENT_TIMESTAMP)< 9, '6-9 months',

DateDiff('m', #colA#, CURRENT_TIMESTAMP)> 9, '+9 months'


)AS colG


FROM (

SELECT `Sheet1$`.colA, `Sheet1$`.colB
FROM `Sheet1$` `Sheet1$`
UNION ALL
SELECT `Sheet2$`.colC, `Sheet2$`.colD
FROM `Sheet2$` `Sheet2$`
) t;
kit
  • 83
  • 4
  • 16
  • "I'm having issues" doesn't give us much to go on. Can you describe the results, the error etc? One thing to try is to replace the `DateDiff('m', #colA#, CURRENT_TIMESTAMP)` on each line of the switch with something else, like `5` to test that the rest of your syntax is good. – Turophile Aug 22 '16 at 12:34
  • If I replace the DateDiff with something else it works as expected. Is it allowed to use datediff inside a switch? – kit Aug 22 '16 at 12:37
  • I think you are using `DateDiff` wrong. Shouldn't it be `DateDif( #colA#, CURRENT_TIMESTAMP,'m')` ? – Turophile Aug 22 '16 at 12:45
  • 1
    I think the syntax is right - try now() instead of current_timestamp, or "m" instead of 'm'. Otherwise, tell us more about the error you are seeing, as Turophile commented. – Don George Aug 22 '16 at 12:48
  • Isn't this a duplicate of the same question you asked a few hours ago? – Don George Aug 22 '16 at 12:52
  • no, that was for a different problem. Thanks again for your help. Much appreciated. – kit Aug 22 '16 at 13:19

1 Answers1

0

Thanks for your help and advice. Here is the solution that ended up working for me:

SELECT `Sheet1$`.colA, `Sheet1$`.colB, 

Switch (

DateDiff ('m', colA, NOW()) < 3 AND DateDiff ('m', colA, NOW()) > 0 , '1 - 2 months',

DateDiff ('m', colA, NOW()) < 5 AND DateDiff ('m', colA, NOW()) > 1 , '2 - 4 months',

DateDiff ('m', colA, NOW()) < 7 AND DateDiff ('m', colA, NOW()) > 3 , '4 - 6 months',

DateDiff ('m', colA, NOW()) < 10 AND DateDiff ('m', colA, NOW()) > 5 , '6 - 9 months',

DateDiff ('m', colA, NOW()) > 9, '+9 months'

)AS colG 

FROM (

SELECT `Sheet1$`.colA, `Sheet1$`.colB
FROM `Sheet1$` `Sheet1$`
UNION ALL
SELECT `Sheet2$`.colC, `Sheet2$`.colD
FROM `Sheet2$` `Sheet2$`
) t;
kit
  • 83
  • 4
  • 16