Field Expressions work in Select Query but not as Append Query (1 Viewer)

I have a Query involving calculated Field expressions that works fine in Select mode but not when run as an Append Query (without any other modifications). When run as an Append Query, Access treats the fields as parameters and asks for their values (and then delivers incorrect results, of course).

That's just WRONG! :banghead:

The Field expressions that don't work in Append mode seem to be the ones that use other Field expressions. For example:

Field: is_lang_match: ([n_lang_id_1]=[n_lang_id_2])

Field: is_lang_id_1: Language_ID
Table: First_Name_Usages

Field: is_lang_id_2: Language_ID
Table: First_Name_Usages_1

I suppose I could decompose all such expressions into ones only involving primary references to Table Fields. However, some of them would become quite long, complicated, hard to understand, and hard to maintain, especially in that tiny little box in Query Design (I wish there were an Expression Builder pop-up there!).

I just seems to me that it is just plain WRONG! that Access should run a Query differently when it is converted to an Update Query (other than that it should append records, of course!).

Very frustrating. I would appreciate any help you could offer. Thank you.

P.S. I'm using Access 2013 Preview. Do you think this is an Access bug? (I think so!). Maybe it works in earlier versions?

Most likely Error-40. You complain but do not show exactly what you are complaining about. Show the SQL for your select query and for your append query. Off-hand, I reckon you are trying to use the alias defined in the SQL statement in the criteria in the same statement, but you cannot do that in Access SQL.

I was hoping to spare you the details. I'm just using Query Design in a fairly straightforward way and not customizing the SQL.

The Select Query SQL is:

PARAMETERS [Primary_First_Name] Text ( 255 );
SELECT First_Name_Usages_1.ID AS First_Name_Usage_ID_1, First_Name_Usages.ID AS First_Name_Usage_ID_2, First_Names_1.First_Name, First_Names.First_Name, First_Name_Usages_1.Language_ID AS n_lang_ID_1, First_Name_Usages.Language_ID AS n_lang_ID_2, ([n_lang_ID_1]=[n_lang_ID_2]) AS is_lang_match, First_Names_1.is_Male AS is_M_1, First_Names_1.is_Female AS is_F_1, First_Names.is_Male AS is_M_2, First_Names.is_Female AS is_F_2, (([is_M_1]<>[is_M_2]) Or ([is_F_1]<>[is_F_2])) AS is_G_cross, (([is_M_1]<>[is_M_2]) And ([is_F_1]<>[is_F_2])) AS is_always_G_cross, IIf([is_always_G_cross],3,IIf([is_lang_match],10,4)) AS Variant_First_Name_Type_ID
FROM First_Name_Usages AS First_Name_Usages_1 INNER JOIN First_Names AS First_Names_1 ON First_Name_Usages_1.First_Name_ID = First_Names_1.ID, (Languages INNER JOIN (First_Names_Import INNER JOIN First_Names ON First_Names_Import.FirstName = First_Names.First_Name) ON Languages.Language_Name = First_Names_Import.Language_Name) INNER JOIN First_Name_Usages ON (Languages.ID = First_Name_Usages.Language_ID) AND (Languages.ID = First_Name_Usages.Language_ID) AND (First_Names.ID = First_Name_Usages.First_Name_ID) AND (First_Names.ID = First_Name_Usages.First_Name_ID)
WHERE (((First_Names_1.First_Name)=[Primary_First_Name]));

The Append Query SQL is:

PARAMETERS [Primary_First_Name] Text ( 255 );
INSERT INTO Variant_First_Name_Links ( First_Name_Usage_ID_1, First_Name_Usage_ID_2, Variant_First_Name_Type_ID )
SELECT First_Name_Usages_1.ID AS First_Name_Usage_ID_1, First_Name_Usages.ID AS First_Name_Usage_ID_2, IIf([is_always_G_cross],3,IIf([is_lang_match],10,4)) AS Variant_First_Name_Type_ID
FROM First_Name_Usages AS First_Name_Usages_1 INNER JOIN First_Names AS First_Names_1 ON First_Name_Usages_1.First_Name_ID = First_Names_1.ID, (Languages INNER JOIN (First_Names_Import INNER JOIN First_Names ON First_Names_Import.FirstName = First_Names.First_Name) ON Languages.Language_Name = First_Names_Import.Language_Name) INNER JOIN First_Name_Usages ON (Languages.ID = First_Name_Usages.Language_ID) AND (Languages.ID = First_Name_Usages.Language_ID) AND (First_Names.ID = First_Name_Usages.First_Name_ID) AND (First_Names.ID = First_Name_Usages.First_Name_ID)
WHERE (((First_Names_1.First_Name)=[Primary_First_Name]));

I do not see is_lang_match defined in your append query.

The stuff is very hard for me to read because of your naming convention, to the extent that I do not wish to spend any time on this. My suggestion is to build your query up element by element, and making sure that everything is fine at each step. Further, I'd advise you to use proper name for each primary key instead of just ID. Eg. LanguageID or FirstNameID etc.

Thanks.

It's hard for me to read, too. I'm not an SQL jockey.

I'll try your suggestions. I was doing the step-by-step method as I build the Select Query, assuming it would still work as an Append Query. It doesn't really make sense as an Append Query until all the pieces are in place. But then they don't work anymore!

Being new to Access, I was using "ID" as the key name since that's what Access puts in by default. Now I'm going to have a lot of work changing all those names everywhere!

What else do you find difficult about the naming convention -- the underscores? Matter of opinion. In the SQL, probably.

I think you found the crux of the problem -- the calculated fields are not defined in the SQL when the Query is converted to an Append Query.

But I didn't write any of this SQL. Access did.

I still think it's an Access bug!