Query field name that has ability to change?

[JonAccess]

In this instance, I use Access to manipulate data, not create a normalized database. My problem is that everytime I run through my saved queries, I have to manually redo my last update query because the field name changes. The field name that changes always begins with "BRC" then followed by a 3 digit number. The 3 digit number changes. For example, today i might use "BRC123" and tomorrow I will use "BRC134" etc. Also, whichever 3 digit suffix i use, i put those 3 digits in the criteria. which i have done so already by referencing a form control value. FYI there are too many possible 3 digit suffixes to store, plus they change

Any way to concatenate the field name kind of like
in SQL...

MyTable.BRC & forms!Myform.MyControl

or
in query design grid

BRC & forms!Myform.MyControl

or use a variable in the field name somehow?
Any ideas?

Thanks in advance

 

[vbaInet]

You won't be able to concatenate a field name in a query. You would have to build your SQL statement in code, before running it.

 

[JonAccess]

vbaInet -

Thanks for your response. Are you saying if I want to be able to do that I would have to use SQL in VBA like:

Dim strSQL as string
 
strSQL = "UPDATE [Work-QP] INNER JOIN 4649 ON [Work-QP].[Item ID] = [4649].[McJ ID] SET [Work-QP].brc [COLOR=green][B]& forms!Myform.MyControl[/B][/COLOR]= [4649]![BRC]
WHERE ((([4649].BR)= [B][COLOR=green]forms!Myform.MyControl[/COLOR][/B]));"
 
DoCmd.RunSQL strSQL

??
or that I cannot change a field name in a query?

 

[vbaInet]

That's right!

WHERE [4649].BR" &  Forms!Myform.MyControl & " = 'Sometext';"

If your control contains 123, the string will reflect as:

WHERE [4649].BR123 = 'Sometext';"

No you can't concatenate a field name in a query. Can only be done in VBA.

 

[plog]

You need to load your data into a temporary table with a constant structure and then run the queries off that temporary table. Here's how it works:

You have a table called TempTable where you load your data--it has fields named like Field1, Field2 etc. The point is the structure and names of the fields in TempTable never change. That means the field called Field1 will hold BRC123 data today, BRC456 data tomorrow and so on. Now the field name and table name for your data is no longer changing, its always in a field called Field1 and your query will work without manually updating it.


To load your data, you first delete all the data in TempTable, then load whatever data you want to work with into it.

 

[JonAccess]

I'm getting a Run-Time error '3144' "Syntax error in UPDATE statement."

while trying to run the code below. Any suggestions?

Tables: "4649", "Work-QP"

Forms: "frm_Queries"

 

[JonAccess]

Sorry, sent it without finishing.

here is the code that produces error when run

Private Sub Command40_Click()
Dim strSQL As String
strSQL = "UPDATE [Work-QP] INNER JOIN 4649 ON [Work-QP].[Item ID] = [4649].[McJ ID] SET [Work-QP].brc' &  Forms!frm_queries.brc1 & ' = [4649]![BRC] WHERE ((([4649].BR)=' &  Forms!frm_queries.brc1 & '));"
[B][COLOR=darkred]DoCmd.RunSQL strSQL[/COLOR][/B]
End Sub

 

[vbaInet]

You are mixing up your single and double quotes.

 

[JonAccess]

Plog -

I've thought about creating a table kind of like that, but I'm having trouble wrapping my head around exactly how it works. Can you explain in a more idiot proof way? Thank you.

 

[JonAccess]

I tried using all double quotes and mixing them up to no avail. I searched the forum for ideas about quotes but I'm not finding anything. Do you see what I'm messing up exactly? Thanks for your effort.

 

[vbaInet]

Tell me what this part of the sql should look like without the concats:

strSQL = "UPDATE [Work-QP] INNER JOIN 4649 ON [Work-QP].[Item ID] = [4649].[McJ ID] SET [Work-QP].brc' &  Forms!frm_queries.brc1 & ' = [4649]![BRC] WHERE ((([4649].BR)=' &  Forms!frm_queries.brc1 & '));"

For example:

strSQL = "UPDATE [Work-QP] INNER JOIN 4649 ON [Work-QP].[Item ID] = [4649].[McJ ID] SET [Work-QP].brc1234 = [4649]![BRC] WHERE [4649].BR = 'some text';"

Then I will be able to tell you how to concatenate the relevant parts.

 

[JonAccess]

strSQL = "UPDATE [Work-QP] INNER JOIN 4649 ON [Work-QP].[Item ID] = [4649].[McJ ID] SET [Work-QP].brc[COLOR=green][B]123[/B][/COLOR]= [4649]![BRC] WHERE ((([4649].BR)=[B][COLOR=green]123[/COLOR][/B]));"

Those two concats will be the same number from the same from control

 

[vbaInet]

strSQL = "UPDATE [Work-QP] INNER JOIN 4649 ON [Work-QP].[Item ID] = [4649].[McJ ID] SET [Work-QP].brc" & Forms!frm_queries.brc1 & " = [4649]![BRC] WHERE ((([4649].BR) = " & Forms!frm_queries.brc1 & "));"

 

[JonAccess]

vbaInet -

I think it's trying to work , but it's giving me a Run-time error '3464' Data type mismatch in criteria expression.

Do I have to change the data types of some of my fields or what?

 

[vbaInet]

What are the datatypes of br1234 and br?

 

[JonAccess]

vbaInet = GENIUS!!

br is a text field so I wrapped that concat in extra single quotes and it worked! (Even though the field I'm updating TO is a number field)

Private Sub Command40_Click()
Dim strSQL As String
strSQL = "UPDATE [Work-QP] INNER JOIN 4649 ON [Work-QP].[Item ID] = [4649].[McJ ID] SET [Work-QP].brc" & Forms!frm_queries.BRC1 & " = [4649]![BRC] WHERE ((([4649].BR) =[COLOR=blue][B]'[/B][/COLOR]" & Forms!frm_queries.BRC1 & "[B][COLOR=blue]'[/COLOR][/B]));"
DoCmd.RunSQL strSQL
End Sub

Thank you so much!

 

[vbaInet]

You're welcome!