Access 2010 UNION * from two tables (with the same column names) asking for parameter value

[cwillson]

I am TOTALLY flummoxed with this one...

I have two tables. Both have the same number of columns, and the column names are exactly the same.

I'm trying to use a UNION query to combine them, which has worked previously, but now I keep getting the error "Enter parameter value: Query2.Legacy_ID_Article"!!

How can it be asking for this when there isn't a table called Query2, and the UNION is definitely referencing the two names tables (not another query)?

Please help!!!

[cwillson]

UNION query is as follows (in case I am missing something obvious!):

SELECT * FROM ANB_AC_CMS_S1
UNION SELECT * FROM CMS_MULTIPLE_SUBJECT_AC_CORRECTIONS;

I've checked and double checked the two tables for differences, but apart from the values (which I am trying to combine) everything is the same....

[cwillson]

Weirdly, when I run a subsequent macro, which creates a new table from the results of the above UNION query, then deletes a column that's not required in the resultant table, it runs without the "Enter parameter value" error????

Macro uses this query (ANB_AC_UNION being the UNION query with the issue above when being run in isolation):

SELECT * INTO ANB_AC_FINAL FROM ANB_AC_UNION

Totally and utterly lost with this now.....need....help....

[JoeM]

What happens if you explicitly list the field names in the query instead of using *?

[cwillson]

same thing

I've saved a copy of the DB, used Compact/Repair, and still no joy....

[JoeM]

Are these linked tables, or are the queries stored directly in your Access database?
If linked, are we sure that these aren't actually linked to queries/views in the system they are coming from?

Just for kicks, have you tried re-creating your Union Query from scratch in a new Query (just to make sure there isn't something flaky saved in the one you were working on)?

[cwillson]

I tried deleting and recreating the query.....


....and it worked like a charm!!! Thank you , thank you, thank you!!

Would still love to know what had happened, but perhaps life is too short! haha

[JoeM]

You are welcome!

Sometimes, an underlying setting might get changed or even corruption.

So when all else fails, try recreating it (the equivalent of a "reboot"!)