Previous Calendar Quarter (1 Viewer)

[TallMan]

Hello,

I am trying to create a small function that looks that a date field, finds the current calendar quarter, and then returns the previous quarter. I came up with the code below, however, because I subtract 1, when we are in quarter 1, the function/string returns 0. Any ideas how I can get this to show Q4 instead of Q0?

 strTrailer = (Format(RS.Fields("upload_date"), "Q") - 1) & "Q" & " " & "Payment"

Thank you for your help!
TallMan

 

[mdlueck]

See if you can make use of the VBA IIF() function to pre-detect when the value was already one and substitute 4 for the answer, else perform the minus 1 operation.

Example usage from my code:

IIF([t].[prodpartflg] = True, [t].[jdepartrev], [t].[fpartrevdisp])

So in that case it is choosing one value or anotehr from a table.

Perhaps "air code":

IIF(variable = 1, 4, variable - 1)

would do the trick.

 

[DavidAtWork]

Could try:
If Format(RS.Fields("upload_date"), "Q") = 1 then
strTrailer = "Q4" & " " & "Payment"
Else
strTrailer = "Q" &(Format(RS.Fields("upload_date"), "Q") - 1) & " " & "Payment"
End If

From your posting I think you might have the Q and quarter number wrong way round

David

 

[Solo712]

This is what you might be looking for:

strTrailer = (((Format(RS.fields("upload_date"), "Q") - 1) + 3) Mod 4) + 1 & "Q" & " " & "Payment"

Just in case anyone wonders: -1 sets the base of "Q" to 0; + 3 actually subtracts 1 in the modulo 4 setup; and the final + 1 resets the quarters back to base 1.

Best,
Jiri

 

[mdlueck]

Jiri, Come again???

? (((Format(Date(), "Q") - 1) + 3) Mod 4) + 1
 4

I consider January Q1.

 

[Solo712]

Jiri, Come again???

? (((Format(Date(), "Q") - 1) + 3) Mod 4) + 1
 4

I consider January Q1.

The OP asks for a function that returns the quarter previous to the one in the date of the argument. IOW, the function needs to return Q4 if the current one is Q1.

Best,
Jiri

 

[mdlueck]

The OP asks for a function that returns the quarter previous to the one in the date of the argument.

Quite right. I momentarily had forgotten that.

A most elegant solution.