Referring to fields in a subquery

[Leif]

I'm trying to build a where clause, in VB, that refers to a field in a
subquery of a query. The field in the subquery is not exposed. I cannot
expose the field since the queries purpose is to return a count generated by
a grouping. To expose the field would mess up the count.

My where looks like:
where = where & " And [Date New Count].TDCTag.TDCTag = [Date New
Count]!Test!DeviceTag"

The error message is:
cmdExcelError: 3079. The specified field '[Date New Count].TDCTag.TDCTag'
could refer to more than one tablelisted in the FROM clause of the SQL
statement

I have a table called TDCTag with a field called TDCTag. This is inside the
subquery [Date New Count]. It is not possible for it to refer to another
table. The subquery [Date New Count] is used on once in the query.

Regards,
Leif

 

[Jeanette Cunningham]

Hi Leif,
when you get that error message, just qualify the field name with the table
name
where = where & " And [TableName].[Date New Count].TDCTag.TDCTag = [Date New
Count]!Test!DeviceTag"

Instead of [Date New Count]
use
[TableName].[Date New Count]

Note: replace TableName with the actual name of the table.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia