Cross Tab Query not accepting Form ComboBox as filter criteria

[Kitticus_Kattus]

Hi All

I have tried everything i can think of to get around this but i am at a dead end.

I have a report which, for reasons that are annoying and too long to go into, has to be exported into excel as a recordset. The query i am using has gone through many queries in order to get to the finished product of a crosstab query with departments in the columns and dates and pay items as rows. I need the report to filter on the dates in order to get a report which only shows 1 date. For some reason when i try to just use the criteria section of the query i get the error message

"The Microsoft Access database engine does not recognize '[Forms]![Selector]![date]!' as a valid field name or expression"

I believe this is a known bug and nothing i can do (including rewriting the query) seems to get rid of it.

This is the code i am using to get the data into the recordset. I used a Youtube video but apparently i am not allowed to post links until i have 10 posts but i will find a way to send it if you think it will be helpful.

'Dim xlApp as Object 'Dim xlBook as Object 'Dim xlSheet as Object Dim XlApp As Excel.Application Dim XLBook As Excel.Workbook Dim Xlsheet As Excel.Worksheet Dim SQL As String Dim SQL2 As String Dim rs1 As DAO.Recordset Dim RS2 As DAO.Recordset2 SQL = "SELECT Order, Description, IP, CBI, FXMT, TI, D_S, TAD, FF, LA, CSOE, [GSLS - FO], [GSLS - MO], [OPS - GSLS], ORM, RM, COM, A_T, GAP, [GAP-PCM], IT_DEV, IT_SAS, LEG, SM, [OPS-MGMT], DC, PER, UKAO FROM [Local Personnel Costs] " Set rs1 = CurrentDb.OpenRecordset(SQL, dbOpenSnapshot) SQL2 = "SELECT Order, Description, IP, CBI, FXMT, TI, D_S, TAD, FF, LA, CSOE, [GSLS - FO], [GSLS - MO], [OPS - GSLS], ORM, RM, COM, A_T, GAP, [GAP-PCM], IT_DEV, IT_SAS, LEG, SM, [OPS-MGMT], DC, PER, UKAO FROM [Delegate Personnel Costs] " Set RS2 = CurrentDb.OpenRecordset(SQL2, dbOpenSnapshot) Set XlApp = Excel.Application XlApp.Visible = True Set XLBook = XlApp.Workbooks.Add Set Xlsheet = XLBook.Worksheets(1) With Xlsheet .Name = "PERSONNEL COSTS" .Cells.Font.Name = "Calibri" .Cells.Font.Size = 11 [I].[formatting that i am sure you don't need][/I] For cols = 0 To rs1.Fields.Count - 1 .Cells(3, cols + 1).Value = rs1.Fields(cols).Name Next .Range("A4").CopyFromRecordset RS2 .Range("A21").CopyFromRecordset rs1 End With End Sub

I have tried to apply a filter to the query in the VB code and also in the SQL code and neither seem to work.

Does anyone have any ideas?

Thanks a big ton!

Kitty

 

[Gasman]

Let's see your filter attempt.
Also please use code tags when posting code. That keeps indentation.

Sub NoIndent()
'Dim xlApp as Object

'Dim xlBook as Object

'Dim xlSheet as Object

    Dim XlApp As Excel.Application
    Dim XLBook As Excel.Workbook
    Dim Xlsheet As Excel.Worksheet
    Dim SQL As String
    Dim SQL2 As String
    Dim rs1 As DAO.Recordset
    Dim RS2 As DAO.Recordset2


    SQL = "SELECT Order, Description, IP, CBI, FXMT, TI, D_S, TAD, FF, LA, CSOE, [GSLS - FO], [GSLS - MO], [OPS - GSLS], ORM, RM, COM, A_T, GAP, [GAP-PCM], IT_DEV, IT_SAS, LEG, SM, [OPS-MGMT], DC, PER, UKAO FROM [Local Personnel Costs] "

    Set rs1 = CurrentDb.OpenRecordset(SQL, dbOpenSnapshot)

    SQL2 = "SELECT Order, Description, IP, CBI, FXMT, TI, D_S, TAD, FF, LA, CSOE, [GSLS - FO], [GSLS - MO], [OPS - GSLS], ORM, RM, COM, A_T, GAP, [GAP-PCM], IT_DEV, IT_SAS, LEG, SM, [OPS-MGMT], DC, PER, UKAO FROM [Delegate Personnel Costs] "

    Set RS2 = CurrentDb.OpenRecordset(SQL2, dbOpenSnapshot)

    Set XlApp = Excel.Application
    XlApp.Visible = True
    Set XLBook = XlApp.Workbooks.Add
    Set Xlsheet = XLBook.Worksheets(1)

    With Xlsheet
        .Name = "PERSONNEL COSTS"
        .cells.Font.Name = "Calibri"
        .cells.Font.Size = 11

        For cols = 0 To rs1.Fields.Count - 1
            .cells(3, cols + 1).Value = rs1.Fields(cols).Name
        Next

        .Range("A4").CopyFromRecordset RS2
        .Range("A21").CopyFromRecordset rs1

    End With

End Sub

 

[CJ_London]

have tried to apply a filter to the query in the VB code and also in the SQL code and neither seem to work.

What did you actually try - what code did you use? and what does 'did not work' mean?

Four possible problems

1. if using parameters in a crosstab query, you need to declare it as such

PARAMETERS [Forms]![Selector]![date] as Date;​

SELECT......​

​

pretty sure that applies to parameters in an earlier query in the 'chain' as well​

2. Creating a recordset with parameters such as [Forms]![Selector]![date] does not work

3. date is a reserved word and should not be used for field and control names - can cause unexpected problems

4. is your Forms]![Selector]![date] formatted correctly to work in a query? (i.e. in the SQL standard of yyyy-mm-dd or the US standard of mm/dd/yyyy

In your SQL, I don't see a field that looks like it might be a date for you to filter the recordset

but you could try filtering the recordset if it is there - you will need an additonal recordset variable
dim frs as dao.recordset

rs1.filter="myDateField=#" & format(Forms]![Selector]![date],"yyyy-mm-dd") & "#"
set frs=rs1.openrecordset

not sure how that would work with recordset2, but in principle should be OK

 

[Kitticus_Kattus]

Hi All

Apologies for the missing Code tags. I did try but i obviously used the wrong thing.

'Dim xlApp as Object
'Dim xlBook as Object
'Dim xlSheet as Object
Dim XlApp As Excel.Application
Dim XLBook As Excel.Workbook
Dim Xlsheet As Excel.Worksheet
Dim SQL As String
'Dim rs1 As DAO.Recordset
'Dim RS2 As DAO.Recordset
Dim RS1filter As DAO.Recordset
'Dim RS2filter As DAO.Recordset
Dim PayrollMonth As String
'Dim PayrollMonth2 As String

SQL = "SELECT Order, Description, month, IP, CBI, FXMT, TI, D_S, TAD, FF, LA, CSOE, [GSLS - FO], [GSLS - MO], [OPS - GSLS], ORM, RM, COM, A_T, GAP, [GAP-PCM], IT_DEV, IT_SAS, LEG, SM, [OPS-MGMT], DC, PER, UKAO FROM [Local Personnel Costs] "

Set rs1 = CurrentDb.OpenRecordset(SQL, dbOpenSnapshot)

'SQL2 = "SELECT Order, Description, month, IP, CBI, FXMT, TI, D_S, TAD, FF, LA, CSOE, [GSLS - FO], [GSLS - MO], [OPS - GSLS], ORM, RM, COM, A_T, GAP, [GAP-PCM], IT_DEV, IT_SAS, LEG, SM, [OPS-MGMT], DC, PER, UKAO FROM [Delegate Personnel Costs] "

'Set RS2 = CurrentDb.OpenRecordset(SQL2, dbOpenSnapshot)

Do While Not rs1.EOF
PayrollMonth = rs1!Month

rs1.filter = "Month ='" & PayrollMonth & "'"
Set RS1filter = rs1.OpenRecordset



'Do While Not RS2.EOF
'PayrollMonth2 = RS2!Month
'RS2filter = "Month ='" & PayrollMonth2 & "'"
'Set RS2filter = RS2.OpenRecordset

Exit Do
Loop




Set XlApp = Excel.Application
XlApp.Visible = True
Set XLBook = XlApp.Workbooks.Add
Set Xlsheet = XLBook.Worksheets(1)

With Xlsheet
.Name = "PERSONNEL COSTS"
.Cells.Font.Name = "Calibri"
.Cells.Font.Size = 11

FORMATTING GUMF

For cols = 0 To rs1.Fields.Count - 1
.Cells(3, cols + 1).Value = rs1.Fields(cols).Name
Next


.Range("A4").CopyFromRecordset rs1
'.Range("A21").CopyFromRecordset rs2


End With

End Sub

I have taken the RS2 part out to just try and concentrate on getting the code right. I got the code from Learn Microsoft

Recordset.Filter property (DAO)​

(i can't post the link because it looks like spam)


As you may have guessed i am pretty much a novice at VBA. I know some SQL and have been able to do some basics but this has got me stumped. I think the parameters that CJ London pointed out in his 1) point might be my best shot. Do i set those in the SQL editor within Access?

Kind regards

Kitty

 

[Kitticus_Kattus]

Okay, so i tried to do something with the parameters. Working within Access just seemed to give me that same problems with not fielding the field so i tried it in the SQL code within my VBA. This is what i came up with

'Dim xlApp as Object
'Dim xlBook as Object
'Dim xlSheet as Object
Dim XlApp As Excel.Application
Dim XLBook As Excel.Workbook
Dim Xlsheet As Excel.Worksheet
Dim SQL As String
'Dim SQL2 As String
Dim rs1 As DAO.Recordset
'Dim RS2 As DAO.Recordset
Dim PayrollMonthFilter As String

Set PayrollMonthFilter = Me.PayrollMonth.Value

SQL = "Parameters " & PayrollMonthFilter & "SELECT Order, Description, month, IP, CBI, FXMT, TI, D_S, TAD, FF, LA, CSOE, [GSLS - FO], [GSLS - MO], [OPS - GSLS], ORM, RM, COM, A_T, GAP, [GAP-PCM], IT_DEV, IT_SAS, LEG, SM, [OPS-MGMT], DC, PER, UKAO FROM [Local Personnel Costs] where month = " & payrollmonthfilter

I think i am getting closer but the error i now get is

Compile Error:

Object Required

I am thinking that There is something wrong with me setting the filter as a string. but this is where my complete lack of experience shines through. I tried setting it as an object but that didn't do anything and i tried putting the PayrollMonth into quotes like i have on report filters i have used but that gave me a syntax error.

Any poking in the right direction would be most humbly appreciated

Begging

Kitty

 

[CJ_London]

When reporting errors in your code , provide the line that errors.

as it is, I can see you are using set - for a string which is not an object

it is not clear to me what you are trying to do since your sql is not a crosstab query

 

[Kitticus_Kattus]

Hi CJ

Apologies, the error is on

Set PayrollMonthFilter = me.payrollMonth.value

The SQL i am using is not a cross tab but the query i am building it from is a cross tab. Although i thought you made an interesting point so i thought i would change my SQL in the VBA to the CrossTab to see if i could circumnavigate the error unfortunately it didn't work and i still got the error

The Microsoft Access Engine does not recognize '[Forms]![Report Selector]![PayrollMonth]!' as a valid field name or expression.

Dim XlApp As Excel.Application
Dim XLBook As Excel.Workbook
Dim Xlsheet As Excel.Worksheet
Dim SQL As String
'Dim SQL2 As String
Dim rs1 As DAO.Recordset
'Dim RS2 As DAO.Recordset



DoCmd.OpenQuery "z", acViewNormal, acReadOnly

SQL = "TRANSFORM Sum([z].Value) AS SumOfValue " & _
" SELECT [z].Order, [z].Description, [z].PayPeriod, [z].Payroll, Sum([z].Value) AS [Total Of Value] " & _
" FROM z " & _
" GROUP BY [z].Order, [z].Description, [z].PayPeriod, [z].Payroll " & _
" PIVOT [z].Department;"

Set rs1 = CurrentDb.OpenRecordset(SQL, dbOpenSnapshot)

I have done some renaming of fields etc in order to avoid using the word month.
The Payroll Month field is being used to filter the table [z]

Can anyone tell me why the crosstab has such a problem with the form filter? Is it something i have done? Should i try and go back and redo everything?

Kitty

 

[Kitticus_Kattus]

I got it!!!!!! I made it work!!!!

'Dim xlApp as Object
'Dim xlBook as Object
'Dim xlSheet as Object
Dim XlApp As Excel.Application
Dim XLBook As Excel.Workbook
Dim Xlsheet As Excel.Worksheet
Dim SQL As String
'Dim SQL2 As String
Dim rs1 As DAO.Recordset
'Dim RS2 As DAO.Recordset
Dim PayPeriodFilter As String


PayPeriodFilter = Me.PayrollMonth.Value

SQL = "TRANSFORM Sum([z].Value) AS SumOfValue " & _
" SELECT [z].Order, [z].Description, [z].PayPeriod, [z].Payroll, Sum([z].Value) AS [Total Of Value] " & _
" FROM z " & _
" Where [z].PayPeriod = " & "'" & PayPeriodFilter & "'" & _
" GROUP BY [z].Order, [z].Description, [z].PayPeriod, [z].Payroll " & _
" PIVOT [z].Department;"

Set rs1 = CurrentDb.OpenRecordset(SQL, dbOpenSnapshot)

I still have some work to do but the filter works!!!

Thank you so much for your help!

Kitty!

 

[CJ_London]

Glad you got there

just be aware that value is a reserved word and should not be used as a field or control name. For example you have used

PayPeriodFilter = Me.PayrollMonth.Value

and

[z].Value

in the first case value is referring to a control value

in the second it could potentially refer to a table (or query) value - which is a nonsense, they don't have values, they have fields which have values

It may work for you in this instance, but don't be surprised if you get some weird error messages if you continue to use Value as a field name

To play safe use [z].[Value] although that won't always work - better to give is a meaningful name like PayValue

and you don't need the .value when referring to a control value - that is the default - so PayPeriodFilter = Me.PayrollMonth is sufficient.

edit: other reserved words you are using is Order (can be confused with Order By) and Description which is a standard property of all objects. Abbreviating it to Desc leaves you is the same hole as Desc is used to define the direction of a sort order.

See this link about reserved words

List of reserved words in Access - Microsoft 365 Apps

Lists the reserved words that you should not use in field, object, and variable names. It is not practical to provide a list of all reserved words, such as built-in function names or Access user-defined names.

learn.microsoft.com