Concat fieldnames in Update SQL

[Deutz]

Hi and thanks in advance,

I have an Access 2003 db with a table that I want to update from values in form textboxes that are unbound (via an update query).

I need to update the MyFieldName1 field with values from textboxes with names that are derived by concatenating MyFieldName2 & MyFieldName3

This SQL crashes as it does not like the Controls reference in there. Is there any way to concat these two fields so that the SQL will get the value from the textbox named and update MyFieldName1 ?

UPDATE MyTableName SET MyFieldName1 = [Forms]![MyFormName].Controls([MyTableName].[MyFieldName2] & ([MyTableName].[MyFieldName3])


Thanks

Deutz

 

[the_net_2.0]

is this in sql? or vba?

in sql, try:

SET MyFieldName1 VALUES ( [Forms]![MyFormName]![MyTableName] )

in vba, do:

SET MyFieldName1 VALUES ( Forms("MyFormName").Controls("MyTableName") )

 

[Deutz]

Thanks for your reply.

This is SQL, not VBA, and when I use the syntax below, I get the Enter Parameter box popping up when I run the query as it does not create the textbox reference correctly and so does not find it on the form.

SET MyFieldName1 VALUES ( [Forms]![MyFormName]![MyTableName].[MyFieldName2] & ([MyTableName].[MyFieldName3])