More date problems

[lsc]

I've done a search but didn't find exactly what I need. From the startdate field I need to calculate: If the month of the startdate is 1,2, or 3 then show ("yyyy",[startdate] & "1", if the month is 4,5, or then ("yyyy,[startdate]& "2", or if the month is 7,8 or 9 then ("yyyy",[startdate] & "3", etc. So using today's day 1-16-2002 the calculation should show 20021. Can this be done in a query?

 

[raskew]

From your example, it appears that you're just trying to represent the Quarter. If so, a calculated field that incorporates the datepart() function would provide what you're after:

Year([OrderDate]) & DatePart("q",[OrderDate]) AS MyQtr

 

[lsc]

Thank you for the reply. It does look like I was needing quarters, hadn't even noticed that. Unfortunately that's not what I need.

 

[raskew]

OK then, you can use the Switch() function to specify exactly what you want. Try the following query in Northwind. Tried to follow your example exactly, so the months which weren't specified (6, 10, 11, 12) now have a sufix of 0. You can modify it to provide exactly what you're after.

SELECT Orders.OrderID, Orders.OrderDate, Year([OrderDate]) & Switch(InStr("1 23",Month([OrderDate]))>0,1,InStr("45",Month([OrderDate]))>0,2,InStr("789",Month([OrderDate]))>0,3,True,0) AS Suffix
FROM Orders;

 

[lsc]

Raskew, you are great! This works wonderfully. Thanks for the help!!