Thanks John but unfortunately that also didn't work.
But, if anyone is interested I have ended up doing it by code on the
AfterUpdate of the [YearStart] field (in the Setup form) that will change
the Column Headings of 2 queries that derive the totals of Accounts Payable
and Invoices. Note that the function only runs if the Month portion of the
[YearStart] changes and not the year (an if statement in the AfterUpdate
code).
Function is:
Function ChangeColumnHeadings()
Dim db As Database
Dim qd As QueryDef
Dim strSQL As String
Dim Dt As Date
Dim x As Integer
Dim str As String
Dt = DLookup("YearStart", "Setup")
x = 1
Do Until x = 13
str = str & Month(DateAdd("m", x - 1, Dt)) & ","
x = x + 1
Loop
str = Left(str, 26)
Set db = CurrentDb()
Set qd = db.QueryDefs("xAccountPayableBudget")
strSQL = "TRANSFORM Sum([Subtotal]+[Tax]) AS Total " & _
"SELECT qryAccountPayableItem.CategoryID " & _
"FROM Setup, [Account Payable] INNER JOIN qryAccountPayableItem ON "
& _
"[Account Payable].AccountPayableID =
qryAccountPayableItem.AccountPayableID " & _
"WHERE ((([Account Payable].AuthorisedDate)>[YearStart])) " & _
"GROUP BY qryAccountPayableItem.CategoryID " & _
"PIVOT Month([AuthorisedDate]) In (" & str & ") " & _
"WITH OWNERACCESS OPTION;"
qd.SQL = strSQL
Set qd = db.QueryDefs("xInvoiceBudget")
strSQL = "TRANSFORM Sum([Subtotal]+[Tax]) AS Total " & _
"SELECT qryInvoiceItem.CategoryID " & _
"FROM Setup, Invoice INNER JOIN qryInvoiceItem ON " & _
"Invoice.InvoiceID = qryInvoiceItem.InvoiceID " & _
"WHERE (((Invoice.PrintedDate) > [YearStart])) " & _
"GROUP BY qryInvoiceItem.CategoryID " & _
"PIVOT Month([PrintedDate]) In (" & str & ") " & _
"WITH OWNERACCESS OPTION;"
qd.SQL = strSQL
Set db = Nothing
Set qd = Nothing
End Function
I hope this may be of some help to other newsgroup users
Regards
Ian
| You might try specifying the data type for YearStart.
|
| Month(DateAdd("m",1,CDate([YearStart]))); ...
|
| That is an untested suggestion.
|
| Ian Baker wrote:
| >
| > Thanks for your response!
| > In using the Setup table which allows the user to "close off" a year I
had
| > in the Column Headings of the Query properties the following which
didn't
| > work displaying the error "Data type mismatch in criteria expression"
| >
| >
Month(DateAdd("m",1,[YearStart]));Month(DateAdd("m",2,[YearStart]));Month(DateAdd("m",3,[YearStart]));Month(DateAdd("m",4,[YearStart]));Month(DateAdd("m",5,[YearStart]));Month(DateAdd("m",6,[YearStart]));Month(DateAdd("m",7,[YearStart]));Month(DateAdd("m",8,[YearStart]));Month(DateAdd("m",9,[YearStart]));Month(DateAdd("m",10,[YearStart]));Month(DateAdd("m",11,[YearStart]));Month(DateAdd("m",12,[YearStart]))
| >
| > So I am still stuck but thanks anyway
| >
| > Regards
| > Ian
| >
| > | > | -----BEGIN PGP SIGNED MESSAGE-----
| > | Hash: SHA1
| > |
| > | Well, you can get rid of the Setup table & just use a parameter
| > | [DateStart] instead. Then, you'll have to change the PIVOT clause to
be
| > | some sort of mathmatical expression that will produce the 12 months in
| > | the order you want, based on the Month value of the DateStart. Of
| > | course, you know that the columns will print in the order you specify
in
| > | the parentheses. Unfortunately, I can't think of the mathmatical
| > | expression. Maybe the Choose() function & some mod calculations.
| > |
| > | Here is an example of your query using a parameter instead of the
Setup
| > | table; also, I removed the "OR [Account Payable].AuthorisedDate Is Not
| > | Null" 'cuz it will return all records w/ a value in AuthorisedDate,
not
| > | just those w/ date >= the parameter date. Also, note that I change
the
| > | AuthorisedDate comparison to BETWEEN 'cuz that way you'll only get 12
| > | months of data instead of, possibly, more than 12 months.
| > |
| > | PARAMETERS [StartDate] Date;
| > |
| > | TRANSFORM Sum([Subtotal]+[Tax]) AS Total
| > |
| > | SELECT API.CategoryID
| > |
| > | FROM [Account Payable] As AP INNER JOIN qryAccountPayableItem AS API
| > | ON AP.AccountPayableID = API.AccountPayableID
| > |
| > | WHERE AP.AuthorisedDate BETWEEN [StartDate] And DateAdd("m", 12,
| > | [StartDate])
| > |
| > | GROUP BY API.CategoryID
| > |
| > | PIVOT Month([AuthorisedDate]) In (1,2,3,4,5,6,7,8,9,10,11,12)
| > |
| > | WITH OWNERACCESS OPTION;
| > |
| > | See the Access Help article PARAMETERS for more info.
| > |
| > | --
| > | MGFoster:::mgf00 <at> earthlink <decimal-point> net
| > | Oakland, CA (USA)
| > |
| > | -----BEGIN PGP SIGNATURE-----
| > | Version: PGP for Personal Privacy 5.0
| > | Charset: noconv
| > |
| > | iQA/AwUBQdJk3oechKqOuFEgEQISTQCfbw3WIHhw0nn0k0UbFszKm/IbjxgAoPvx
| > | xQ3++OAqIC080kOYuN2HKGUo
| > | =8ghr
| > | -----END PGP SIGNATURE-----
| > |
| > |
| > | Ian Baker wrote:
| > | > I have a crosstab query that sums all the Account Payable entries
per
| > | > Category per month and is as follows:
| > | > TRANSFORM Sum([Subtotal]+[Tax]) AS Total
| > | > SELECT qryAccountPayableItem.CategoryID
| > | > FROM Setup, [Account Payable] INNER JOIN qryAccountPayableItem ON
| > [Account
| > | > Payable].AccountPayableID = qryAccountPayableItem.AccountPayableID
| > | > WHERE ((([Account Payable].AuthorisedDate)>[YearStart] Or ([Account
| > | > Payable].AuthorisedDate) Is Not Null))
| > | > GROUP BY qryAccountPayableItem.CategoryID
| > | > PIVOT Month([AuthorisedDate]) In (1,2,3,4,5,6,7,8,9,10,11,12)
| > | > WITH OWNERACCESS OPTION;
| > | >
| > | > Currently the above query has column headings for each month of the
year
| > | > from 1 to 12. One of the tables in the query is called Setup which
has a
| > | > field called [YearStart] that contains a date that the financial
year
| > | > starts. What I am trying to do (without success to date) is to get
the
| > | > columns to start at the month of the {YearStart] field and go for 12
| > months.
| > | > i.e. If YearStart=1/6/04 then I need the columns to be
| > | > 6,7,8,9,10,11,12,1,2,3,4,5
