union query

[dott]

here is my syntactically immaculate query:

SELECT [Contractor Name],[Tendor No],[Project No],[WCB Valid Date],[Insurance Valid Date],[NSCSA Valid Date] FROM [WINcontracts] UNION SELECT [Contractor Name],[Tendor No],[Project No],[WCB Valid Date],[Insurance Valid Date],[NSCSA Valid Date] FROM [RIMcontracts];


Now here's my problem: it prompts me for a parameter value for Tendor No, which exists (case and all) in both of those tables.

I've had a similar problem with a report asking for parameter values for Tendor No. It just doesnt make sense. Does anyone have any clue what is going on here? This is frustrating as hell.

 

[dott]

its like it doesnt see 'Tendor No' in my table
it doesnt have any beefs with any other fields, just this one

is there anything i can do? I tried renaming the fields (which would create a HEAP of other problems, but even that didnt work)

it just seems to hate the field (or me)

god help me please

 

[boblarson]

Hopefully, you aren't actually using commas (,) between the table name and fields.

Need a period between table and field name.

 

[dott]

each of those is a field, not a table name
it actually is smart enough to know what table the fields are coming from, surprisingly

as a side note i had it setup as TABLE.FIELD before what you see above and it changed absolutely nothing

 

[IgorB]

I creted 2 tables and all fileds as you have.
Even copied your query.
Everything seems OK!!
Union query with your syntax gave me a good result!!!!!!!!!!!!!!

1) Try highlight your query syntax, copy it to buffer.
2) Delete your original Query.
3) Create a new query.
4) Paste query syntax into it
5) Save query with a original name.

I did have the similar problem like yours a few times and above
manipulation always fixed the issue.

Igor.

 

[dott]

igor, what i ended up doing was creating a new row in my table, copied all the info from the 'tendor no' field into it, deleted 'tendor no' and then renamed my new field 'tendor no'

that made it work.
didnt even have to mess with the query once i did that.

weird huh?