Displaying the Date Quarter in a Query

[etiangsonizer]

Is there a simple formula to return the fiscal year quarter that applies to a date? I simply want January thru March to show as Qtr 1, etc.

thanks

 

[PeterF]

Something like this?
"Qtr " & (((month(#12/05/2007#)-1)\3)+1)

 

[etiangsonizer]

thanks for the quick reply. i already did what you said. thanks a lot.

 

[boblarson]

This will also work:

Format(YourDateFieldHere,"q")

 

[raskew]

Hi -

There are many instances when a fiscal year does not start on 1 Jan. Try the following which allows the user to specify the FY start date.

Function fGetFYQtr(FYStart As Date, pDate As Date) As Integer
'To call: from debug (immediate window)
'? fGetFYQtr(#7/1/06#, date())
'returns: 3
   fGetFYQtr = DateDiff("m", FYStart, pDate) \ 3 + 1
End Function

HTH - Bob

 

[jvbeats]

This will also work:

Format(YourDateFieldHere,"q")

Where do I put this expression?

 

[kzutter]

[FONT=&quot]Public Function Quarter(TheDate As Date) As Variant
'Enter with a date
'Returns the Quarter that date is in
'where July 1 is start of fiscal year
'Change the Choice items for different fiscal year start quarters


Quarter = Choose(DatePart("q", TheDate), 3, 4, 1, 2)

End Function
[/FONT]

 

[Masikonde]

Something like this?
"Qtr " & (((month(#12/05/2007#)-1)\3)+1)

Is it possible to apply the same formula to multiple years and Quarters, where there are different years and Quarters? I'm facing a similar problem but I have a column for different years and a column for different quarters, and I need to combine the two to come up with a date.

 

[plog]

Having a hard time visualizing what you want. Can you demonstrate your issue with data? The best way is with 2 sets of data:

A. Starting data from your table(s). Include table and field names and enough data to cover all cases.

B. Expected results of A. Show what data you expect to end up with when you feed in the data from A.

 

[Masikonde]

I'm looking to populate the last column "Actual Planned Date" with the "mm/dd/yyyy" as shown in the attachment

 

[plog]

You can do that in a query using the CDate() function:

MS Access: CDate Function

This MSAccess tutorial explains how to use the Access CDate function with syntax and examples. The Microsoft Access CDate function converts a value to a date.

www.techonthenet.com

 

[Isaac]

Or if all you want is a text display, you could even concatenate them:

ActualPlannedDate: [Planned_Month_Sales] & "/" & [Planned_Date_Sales] & "/" & [Planned_Year_Sales]