SQL Error Only When running Report

[lameid]

I hate it when Access does this...

The specified field '<Field Name Here>' could refer to more than one table in the from clause of your SQL statement.

The field is grouped on in the report and is joined in the record source query. In those sub-queries (query objects), the field is calculated (via IIF) and aliased back to a field name used in the calculation.

The record source executes fine on its own. I kind of see how the report could get confused. I'll play with it but does anyone have ideas to fix it or know how the report object runs statements to group?

I am hoping this all means something to someone who can help as I bet it would take forever to explain everything behind the queries.

 

[KenReay]

Why not just qualify the offending column name in the query eg Yourtablename.[ColumnName]

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[lameid]

It is except where aliased and then I use the FQN in the grouping for the report. This seems odd (it is the only column in the query with that field name) but it is what is available in the list.

 

[lameid]

If I alias Field to Field2 and fix all the control sources and Report Grouping, it works.

I hate weird.