#name? error using a form based on a crosstab query

[niki09]

I have a form showing a result (an amount) from a cross tab query. The form is to present weekly totals, preferably for each of the last 26 weeks.
201601 $200
201602 $250
201603 $102
..
201612 $75
etc

The week number comes from a formula in the query based on the transaction date (eg 201601 is the total of transactions in week 1 2016).

I hope the form can present rolling weekly totals.

The initial form had the fields 201601 201602 to 201613 etc and was fine.
When I progressed to the next week, 201614 was now a field to report, but it was not in the form (as it was not in the cross tab when the form was written).

I thought a work around could be to create fields in the form to cover the next (say) 26 weeks, then when I run the form, it will show the value for 201613 and 201614 when I reach those weeks (and it does do this).
So I added fields in the form for
201614


201615
201616
etc

However, as I now have fields in the form (i.e. future weeks) that do not exist in the cross tab, those fields get the #name? error when the form is run.
I understand that access is saying that the field does not exist (true), but is there a work around? I tried iif with iserror, ismissing etc, but still the same.

Thanks

[Micron]

Crosstabs don't make for good queries when the number of distinct rows is dynamic because as you probably have figured out, CT fields that don't exist in the source throw an error. I don't get why you're not using a more compatible/traditional form/subform datasheet configuration. Why not have a datasheet below a main/top level data section?

I can only think of one scenario that would be an exception (i.e. where something like a dynamic crosstab is required) and that is if you HAD to have a delimited list that needed to be transposed into rows).