I need to create a Quarter field in a table

[slimjeanyves]

I have a Date field in the format X/XX/2012 for all my records.

I want to create a field that labels each record according to its quarter. So if a date is 3/29/2012, I'd want the corresponding field entry to be: Q1 2012. If it's 3/29/2013, I'd want it to be: Q1 2013, etc.

I guess I would run an update query, but I don't know how to build the proper expression in order to update my table with a new field.

Any help would be much appreciated!

Oh, and I have figured out how to create a quarter #, but I actually need output in the format mentioned above.

 

[pbaldy]

I wouldn't save the field, since it can be derived from the date field. You can use the DatePart() and Year functions and concatenation to get your format:

"Q" & DatePart(...) & " " & Year(...)

 

[slimjeanyves]

Thank you for this! I got it running with your suggested code in a Select query, however I cannot for the life of me figure out how to do this with an update query.

Here's my code:

"UPDATE [tbl_Multi_Q_2012-2013 Query]
SET [Field1]"="Q" & DatePart([qq],[tbl_Multi_Q_2012-2013]![Date]) & " " & Year([tbl_Multi_Q_2012-2013]![Date])

I'm not sure if I'm using the SET function properly. I ultimately want the query to create a new field on its own and populate it (and not have to have me first create one in the table (which is called Field1 in this case).

Anyway, I've gotten a variety of errors as I've tried to tweak this statement .

And thanks for your patience, i'm new with this stuff, but really like playing aroudn with it.

 

[pbaldy]

So you've decided to save it anyway? An update query can/will not create a new field, it can only change the value of an existing field.

 

[gemma-the-husky]

isn't there an option in the format to return the quarter

format(date,"q") returns 3

 

[pbaldy]

isn't there an option in the format to return the quarter

format(date,"q") returns 3

Sure; are you thinking the Format() function is more efficient than the DatePart() function or something?

 

[nanscombe]

You can use the Format() function to do the necessary but in a single step.

I would keep the date field as is but if you want to display it in a query, based on tbl_Multi_Q_2012-2013, and add a column which says something like ...

Quarter: Format([Date],"\Qq yyyy")

For the date 3/29/2013 it'll give you a value of "Q1 2013". "\Q" is used to put a literal "Q" at the front of the string.

 

[pbaldy]

Good point; I hadn't thought about the Format() function being able to do both together. :banghead:

 

[nanscombe]

I even half remembered, and managed to figure out, the"\Q" bit as well.

(Is there a "chuffed" smilie? )

 

[teiben]

Thanks, I tried this to and couldn't get the Q to show, seems like the "\ has something to do with it?

 

[pbaldy]

How are you using it? I tested in 2007 Immediate window and it worked as expected:

?Format(date(),"\Qq yyyy")
Q1 2014