Crosstab query by month and a catch-all field

[lisarmj]

I am creating a crosstab query in VBA to report on claims on a paid and incurred basis. I would like the query to have 13 columns - one for each month of the current year and one for all claims paid prior to January of the current year.

Is there a way to lump all data with a date less than Jan 1 into a single field while retaining the monthly detail for the current year?

All of the data is coming from a single table. Sample code below functions, but provides a column for every month a claim was incurred.

Thanks in advance!

Sub Triangle()
'Triangle Reports
'Check Registers

On Error GoTo Error_Handler:

Dim DB As DAO.Database
Dim RS As DAO.Recordset
Dim QRY As DAO.QueryDef
Dim strQryName As String
Dim strSQL As String
Dim strTransform As String
Dim strSelect As String
Dim strFrom As String
Dim strWhere As String
Dim strGroupBy As String
Dim strPivot As String
Dim strTable1 As String



strTransform = "TRANSFORM sum(tblcheckregister.Amount) AS sumofAmount"
strSelect = "SELECT Format([date]," & "'" & "yyyy/mm" & "') AS [Paid Date]"
strFrom = "FROM tblCheckRegister"
strGroupBy = "GROUP BY Format([date]," & "'" & "yyyy/mm" & "')"
strPivot = "PIVOT Format([low srvc date]," & "'" & "yyyy/mm" & "')"

strSQL = strTransform & " " & strSelect & " " & strFrom & " " & strGroupBy & " " & strPivot

MsgBox strSQL
Set DB = CurrentDb()
strQryName = "qryTemp"

For Each QRY In DB.QueryDefs
If QRY.Name = strQryName Then
DB.QueryDefs.Delete strQryName
Exit For
End If
Next

'DB.QueryDefs.Delete strQryName
Set QRY = DB.CreateQueryDef(strQryName, strSQL)
DoCmd.OpenQuery strQryName, acViewNormal, acReadOnly


Exit_Procedure:
Exit Sub

Error_Handler:
MsgBox (Err.Number & ": " & Err.Description)
Resume Exit_Procedure


End Sub

 

[CJ_London]

You are using Date (which is a reserved word meaning today) which may be causing you problems but to answer your question, yes

Instead of using the date you have, use an iff statement along the following lines:

Iff([MyDate]<#01/01/2013#,#12/31/2012#,[MyDate])

which can then be formatted per your code in your select and group by strings

Note: in case you are not aware, when using # to delimit dates, they need to be in American format

 

[lisarmj]

Thank you, I'll give this a try.

 

[lisarmj]

I'm sorry, but I don't understand how to execute the code you suggested.

To clarify, I would like a crosstab query with the following columns:

Prior to Jan 2013, Jan, Feb, Mar, Apr, May, June, July, Aug, Sept, Oct, Nov, Dec

Thank you.

 

[lisarmj]

I got it to work....thank you again.