query only works through clicking on icon

i posted about this the other day, thought i sorted it and now it keeps coming back to haunt me

i have an append query in my database it is based on 3 other queries

when the query is run using vb

DBEngine(0)(0).Execute "QRY_ImportDuplicatesTopLevel", dbFailOnError

or by putting its sql into a string and executing using CurrentProject.Connection.Execute strSQL

i get an error message saying too few paramters - expected one

i'm fairly used to these messages which mean that in the query is a non existent or misspelt field and i find them by running the query in the query viewer and letting access highlight the missing / misspelt field

the weird part is that when i run this query as a stored object by clicking on its icon i get no error and the query does the job it is supposed to do

does anyonw have any idea why this situation could occur?

Can you post the SQL?

I assume there are no parameters to the query?

no there are no parameters to the query

right i am starting to shed some light on this one - i won't post the sql of the top level query because i think i have established that it is not where the problem lies

there are two select queries the results of which are pooled together by a union query, that union query itself fails when run from code giving a very silimar error message the union query is a couple of steps down from the top level query i was initially calling

i think the problem is at the level of the union query with the following sql

SELECT * FROM QRY_OldDupeCodes2
UNION ALL SELECT * FROM QRY_NewDupeCodes2;

now one of the fields in QRY_NewDupeCodes2 is returning NULL for every record - could these nulls in the union results be cauzing the issue?

it is strange though... running the union query from its icon just gives you a result set that has some nulls in one of the fields but when the query is run from code it causes an error

this actually just gets weirder the original top level query also works perfectly fine when you turn it into a select query and call it using

DoCmd.OpenQuery "QRY_DuplicatesTopLevel", acViewNormal

it does not however work when turned into a make table query (instead of an append) it fails with the same error

also similarly to before the make table version of this query works when you are clicking on its icon

progress of a sort because i now know this wasn't a problem relating to a missing or mispelt field name in the table i was appending to