How To Properly Reference a Field in a Query

[jdegeorge]

Hi:

I have a report that's based on a query, which is based on 2 other queries. Some of the sub queries have fields with the same name. So, the resulting query (qryMainQuery) has fields that look like this:

qrySubQuery1.Area
qrySubQuery1.Name
qrySubQuery1.Address
qrySubQuery2.Area
qrySubQuery2.Name
qrySubQuery2.Address

If I set a field on the report to equal any one of these it works fine, but if I try to create a formula, such as to concatenate 2 of the fields, I get an error message.

I've tried "=[qryMainQuery]![qrySubQuery1]![Area] & " - " & [qryMainQuery]![qrySubQuery1]![Name]" and get a parameter prompt for "qryMainQuery".

If I try "=[qrySubQuery1]![Area] & " - " & [qrySubQuery1]![Name]" and get a parameter prompt for "qrySubQuery1".

I even tried "=[Me]![qrySubQuery1]![Area] & " - " & [Me]![qrySubQuery1]![Name]" and get a parameter prompt for "Me".

What am I doing wrong?

Jim DeGeorge

 

[jdegeorge]

Hi:

I solved my problem. I assigned ALIAS names to each of these fields in the query grid so I could refer to them as such.

So

qrySubQuery1.Area
qrySubQuery1.Name
qrySubQuery1.Address
qrySubQuery2.Area
qrySubQuery2.Name
qrySubQuery2.Address

resulted as

Area1
Name1
Address1
Area2
Name2
Address2

Thanks anyway!

Jim DeGeorge