Concatenation in Update Query (1 Viewer)

johndohnal

Registered User.
Local time
Today, 10:56
Joined
Sep 12, 2013
Messages
41
I would like to use concatenation of a form field with a string to be used as the Field and part of the Update To expressions of an update query. I can enter the Field name and the Update To manually, but I want them to be able to change when the drop-down selections in my form change.
For example, If “Quarantine” is selected in the “To” drop-down of my Receipt form, I’d like for the first Field name in the update query to be “QuarantineInv” which would be something like [forms]![frmReceipt]![To]&”Inv” and the Update To field would be [tblMaterialSpecifications].[QuarantineInv]+[forms]![frmReceipt]![Quantity]
But instead of [tblMaterialSpecifications].[QuarantineInv] in the Update To field, I want it to be something like
[tblMaterialSpecifications].[([forms]![frmReceipt]![To]&”Inv”)]
This doesn’t work, but can anyone give me a solution that will work? Any help will be greatly appreciated.
 

David R

I know a few things...
Local time
Today, 09:56
Joined
Oct 23, 2001
Messages
2,633
What is the purpose behind storing the calculated value, versus just putting it in a query?
 

johndohnal

Registered User.
Local time
Today, 10:56
Joined
Sep 12, 2013
Messages
41
The calculated value must be stored to give a running inventory. The only significance of the calculated value is that it will be stored under a field name. I'm trying to use a concatenated field name to select another field name in which the calculated value will be stored.
Thanks,
John
 

JHB

Have been here a while
Local time
Today, 15:56
Joined
Jun 17, 2012
Messages
7,732
Can't you create query/SQL string and execute it, in the after update event in the combo box in the form, (or do I misunderstand something)?

Code:
SqlString = ".... [tblMaterialSpecifications].[" & [forms]![frmReceipt]![To] & "Inv"] ...."
 

johndohnal

Registered User.
Local time
Today, 10:56
Joined
Sep 12, 2013
Messages
41
I tried the last solution and got no luck. I believe my problem is with syntax and incorporating a form field name concatenated with a literal string, and then using that as a field name in my update query. If I need to do that in two or more steps, that's fine, but I don't know what that would look like. Any help would be greatly appreciated.
Thanks
John D
 

David R

I know a few things...
Local time
Today, 09:56
Joined
Oct 23, 2001
Messages
2,633
Post the SQL of the update query you tried.
 

johndohnal

Registered User.
Local time
Today, 10:56
Joined
Sep 12, 2013
Messages
41
Here is my original update query. This works, but I have to have an update query and hard code for each combination of "To" and "From". I'd like to simplify it by using the "To" and "From" selection on the Receipt Form to select the fields to be updated: in this example, QuarantineInv, by concatenating the "To" Form Field with the literal "Inv".

Code:
UPDATE tblMaterialSpecifications SET tblMaterialSpecifications.QuarantineInv = [tblMaterialSpecifications].[QuarantineInv]+[forms]![frmReceipt]![Quantity]
WHERE (((tblMaterialSpecifications.ID)=forms!frmReceipt!MaterialSupplyID) And ((forms!frmReceipt!From)="Vendor") And ((forms!frmReceipt!To)="Quarantine"));

Again, thanks for your time and effort.
John D
 
Last edited:

Users who are viewing this thread

Top Bottom