You are being so good to me I thought I try your patience with another problem. Basically this whole thing is trying to convert something which works sucessfully in Excel into Access because you can't adequately lock down the tables in Excel to prevent people being silly. I'm sort of most of the way there, but there are a couple more issues.
The one here which has stumped me is as follows. I have input data which needs to be pivoted in order get the right table for output. The pivot works fine but depending on what filters are applied, there will be a different number of columns to display in the final report. So the standard report might have six columns, but only 1,2,4,5,6 might have date in them after the pivot has been applied. Having six columns bound to the six potential fields as required in the report, means we get the error "The Microsoft Access database engine does not recognise <column3> as a valid field name.
Now in the Excel version we get around that using the capacity in M Formula "Get an Transform" setting MissingColumn=Ignore : the missing column gets filled with nulls which we replaced with zeros and Bob's your uncle.
I can't see how one gets an Access form to do the same and simply include 0s where the field bound to the control doesn't exist. Using a formula with IsError() doesn't trap it nor does disabling the control seem to prevent it.
The only solutions I have come up with so far are:
1) to create a query with examples of all possible entries in it before we pivot the table, add those entries to the table, then pivot the table so we get these entries as column names, then filter out all those dummy entries so we get to the right data to present. This is horrible and longwinded. In fact even that would be easier in M formula than in SQL.
2) adding and removing the binding to the controls on the fly by VBA whenever the report is opened using the Report's On Error event or On_Open event, though even that seems a bit messy as it will involve iterating through the controls testing each one in turn and removing the binding, then on closing the Report, re-iterating through them again and restoring them in the filters/data is different next time.
But there must be a better/simpler way of dealing with missing fields in a report. Somebody please tell me what it is?
Thanks
The one here which has stumped me is as follows. I have input data which needs to be pivoted in order get the right table for output. The pivot works fine but depending on what filters are applied, there will be a different number of columns to display in the final report. So the standard report might have six columns, but only 1,2,4,5,6 might have date in them after the pivot has been applied. Having six columns bound to the six potential fields as required in the report, means we get the error "The Microsoft Access database engine does not recognise <column3> as a valid field name.
Now in the Excel version we get around that using the capacity in M Formula "Get an Transform" setting MissingColumn=Ignore : the missing column gets filled with nulls which we replaced with zeros and Bob's your uncle.
I can't see how one gets an Access form to do the same and simply include 0s where the field bound to the control doesn't exist. Using a formula with IsError() doesn't trap it nor does disabling the control seem to prevent it.
The only solutions I have come up with so far are:
1) to create a query with examples of all possible entries in it before we pivot the table, add those entries to the table, then pivot the table so we get these entries as column names, then filter out all those dummy entries so we get to the right data to present. This is horrible and longwinded. In fact even that would be easier in M formula than in SQL.
2) adding and removing the binding to the controls on the fly by VBA whenever the report is opened using the Report's On Error event or On_Open event, though even that seems a bit messy as it will involve iterating through the controls testing each one in turn and removing the binding, then on closing the Report, re-iterating through them again and restoring them in the filters/data is different next time.
But there must be a better/simpler way of dealing with missing fields in a report. Somebody please tell me what it is?
Thanks