Dear Friends

I want to build a query with a date field. That particular date field should be formatted and grouped into "Month" or "Quarter" or "Year" depending on an input box from a different form.

Thanks in advance.

use this function Expr1: DatePart("q",[yourDatefieldname])

"m" for month and "yyyy" for year

Dear Ziggy1
Thanks for your reply.
What I actually want is, I want to select from a form. The form will have 3 button (may be option button or check box). These option button will be Monthly report, Quarterly Report, Annual report. If I check one of these bottun, the query will format the date field depend on respective button.

Thanks

I can't decide the best way because I don't have all the parts...if you post a sample DB with sample data it might help give you a better answer.

The easy way is to make 3 queries using the function I gave you, and then make 3 reports...I think you have to anyways because of the "Grouping".

then with an option group you can use something like this....



Private Sub Command11_Click()

Dim rptOpt As Integer

' where Fram2 is the name of your option group
rptOpt = Me.Frame2.Value

Select Case rptOpt

Case Is = 1
'MsgBox "Quarterly"
DoCmd.OpenReport "rptQuarterly", acViewPreview

Case Is = 2
'MsgBox "Monthly"
DoCmd.OpenReport "RptMonthly", acViewPreview

Case Is = 3
'MsgBox "Anual"
DoCmd.OpenReport "RptAnual", acViewPreview


End Select


End Sub


I know there are more efficient ways of doing it ( single query/report) I just don't have time to post an example.

Dear friend
I have to prepare the report periodically. Usually they are monthly, quarterly, 6 monthly and yearly. I usually have to change the "Period" column with the following formatting expressions. Now, instead of changing the formatting manually, I want to go it through a Form. Will it be possible?

With this main query "00MainService", I have a series of other query and report.

Period: Format([BHCDate],"yyyy")
Period: Format([BHCDate],"yyyy") & IIf(Month([BHCDate])>6,2,1)
Period: Format([BHCDate],"yyyy q")
Period: Format([BHCDate],"yyyy mm")

Thanks in advance.

ok based on your last post, I thought it would be best if you had a criteria table. Here you can enter what ever date expression you want to put into query, the code will put it into the SQL.

I made a form that has a combo that selects the criteria


Option Compare Database

Private Sub Command2_Click()

Dim strCrit As String
Dim QueryName As String
Dim qryDef As DAO.QueryDef
Dim strSelect As String


strCrit = Me.cobCrit

QueryName = "00MainService"

strNewT = NewTable
strQueryName = QueryName

strSelect = "SELECT " & strCrit & " AS Period,"
strSelect = strSelect & "BHC.Code , BHC.ProjSite, BHC.Staff, BHC.BHCID FROM BHC;"



Set qryDef = CurrentDb.QueryDefs(strQueryName)

qryDef.SQL = strSelect
qryDef.Close

DoCmd.OpenQuery QueryName, acNormal, acEdit


End Sub

Dear Ziggy1

Thank you very much. It is exactly what I want to do.

I wish I could do like you!

Happy new year.:)

no problem naungsai , glad to help...it helps to hang around sites like this, you learn alot from helping others.

Happy New Year