Question error message (1 Viewer)

[bigmac]

when running a form "table1" i have a button that opens another form whoes controlsource is a crosstab query if i open it with an id of between1 and5 then it works ok but if i look at a record of 6 or higher then i get the following message "microsoft office access data base engine does not reccognize '~sq_finput_crosstab am' as a valid field name or expression" what am i doing wrong please?

 

[Endre]

Pre-Creating a Form or Report based on the output of a cross-tab query is always dodgy as you cannot always predict the output of your query.

For example you create a form that has financial data per country. You have rows of country named vertically and column headings for the years 2005 to 2010 running across horizontally. You create a cross tab query that has the same definition, and you now use the query as the data source for your form. You map country control to the country in the query, 2005 to the control source of the query 2005, etc etc for 2006, 2007, 2008, 2009, 2010. Open the form and everything is fine.

Now remove some of your countries from your data and run the cross-tab query so that data does not exist for the year 2007. You will have no column heading or data for the year 2007 in the output of your query and therefore no control source data for 2007. Your form has a control source mapped to the output 2007 as it wants to map to it, but it does not exist and will error. (It existed when you originally created the form, but not now).

Perhaps this is causing your problem ???

 

[bigmac]

so are you saying to start a new form ? or is there another way to do this?

 

[Endre]

Yes there is a get around - Open up your cross-tab query in design view, and in the general properties of the query you will see a property called Column Headings. Physically type in here the values of the column headings of the data you expect - i.e you are going to fix them. This means that when you run the cross-tab query you will only ever see the results for the column headings you are fixing. Now you can bind your form controls to to these, or, embed the query in your form. You cannot embed the query unless you fix the column headings.

So in the case of my previous example using my numeric column headings for Years, I would have to have the setting as:
2005, 2006, 2007, 2008, 2009, 2010.

And although the data for 2007 does not exist it won't error. However, if data should now exist for 2000, 2001, 2011, 2012 - the query will not output the values either as we have fixed the columns to only show those between 2005 - 2010. So you have to decide how you want to play this. Fix or Variable?

If the returned values were text, you probably would have to use double-quotes for column headings as:
"Red", "Yellow", "Green", "Blue".

Better Answer:

You can also (in VBA code find the values of data returned by your variable query at run-time, and populate them into a fixed column query and use the fixed column query in your main form. This would work well if you have only the query output embedded into your main form, and not a sub-form based on the fixed column x-tab query.

So:
Create QueryV as variable x-tab query.
Create QueryF as fixed x-tab query (it doesn't matter really what the query is as we will overwrite the SQL anyway)

Open up QueryV in design mode, switch to the SQL view and copy the SQL text - we can call this QueryVSQLText

'Create Recordset with variable query output
Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("    Paste your QueryVSQLText here     ;")
Dim int_i As Integer, str_t As String
'Iterate and concatenate field output
'Only start the following code from your first field you want output, not from 0 as here
For int_i = 0 To rst.Fields.Count - 1
    str_t = str_t & """" & rst.Fields(int_i).Name & ""","
Next int_i
str_t = Left(str_t, Len(str_t) - 1)
'Display text
MsgBox (str_t)
'I have used [1YRS].[YR_ID] as my column that has the year output.
'Attach field output to fixed column SQL query
CurrentDb.QueryDefs("QueryF ").SQL = QueryVSQLText & " PIVOT [1YRS].[YR_ID] In (" & str_t & ");"

Now your fixed x-tab query is "variable"......


By the way you still haven't stated if this is causing your problem.

 

[bigmac]

hi ENDRE, i will give this a go and let you know if this works , thanks for responding,