MS Access Update Query (Multiple Where Conditions)

Question

I have a textbox [ScanItem] on a Split Form that take two different barscan values, Works Really Slick!

Alphanumeric, Len 11
Numeric, Len 12

My Update Query works, but for only one WHERE Condition. I was hoping to use the Len function to combine the WHERE function's but can't get it to work.

Update Query for Alphanumeric Len 11 (Example: 019-WMD-001 ):

strSQL = "UPDATE [Location Table]" & _
         "SET [Location]='" & Me.Location1.Value & "'" & _
         "WHERE [Location Table].[Custom Label] = [ScanItem]"

Update Query for Numeric Len 12

"WHERE [Location Table].[Item ID] = [ScanItem]"

I understand the table spaces are a no-no but I do not have control over that. If this will not work, any other solution would be welcomed.

The issue is [Custom Label] needs to be updated with Alphanumeric, Len 11 and [Item ID] updated with Numeric, Len 12 barscans. — EbayBill
– EbayBill, Commented Oct 17, 2017 at 17:45

AndrewBanjo1968 · Accepted Answer · 2017-10-17 18:27:34Z

2

If I understand your question correctly you want to apply one WHERE clause evaluation if length of [ScanItem] = 11 (in which case use [Custom Label] in comparison) and another WHERE clause evaluation if length of [ScanItem] = 12 (in which case use [Item ID] in comparison). There are multiple ways to do this. I'm not addressing SQL injection issue--just the query logic, but you can always rewrite this using parameters (recommended best practice). You could write the query to use conditional logic or your vba could use the conditional logic (assuming there are only possible length conditions of 11 and 12). I'm a fan of scenario 2.

Scenario 1 (query uses conditional logic):

strSQL = "UPDATE [Location Table] " & _
"SET [Location]='" & Me.Location1.Value & "' " & _
"WHERE ([Location Table].[Custom Label] = '" & Me.ScanItem.Value & "' AND LEN('" & Me.ScanItem.Value & "') = 11) " & _
"  OR ([Location Table].[Item ID] = '" & Me.ScanItem.Value & "' AND LEN('" & Me.ScanItem.Value & "') = 12)"

Scenario 2 (vba uses conditional logic--assuming only 2 possible lengths of 11 and not 11):

strSQL = "UPDATE [Location Table] " & _
"SET [Location]='" & Me.Location1.Value & "' " & _
"WHERE ([Location Table]." & IIF(Len(Me.ScanItem.Value)=11,"[Custom Label]","[Item ID]") & " = '" & Me.ScanItem.Value & "'"

edited Oct 17, 2017 at 18:27

answered Oct 17, 2017 at 18:21

AndrewBanjo1968

1159 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

EbayBill Over a year ago

Thank You Andrew, that was what I was looking for. I now noticed I have a problem with my Location Table that I will have to correct.... I used Item ID as a Primary Key, however not all will have a Item ID until I list them on eBay. Lesson learned!

Erik A · Accepted Answer · 2017-10-17 06:43:06Z

1

For the first WHERE condition, you've handled it with string concatenation.

For the second one, however, you're just referring to the field.

A solution using string concatenation (at risk for SQL injection):

strSQL = "UPDATE [Location Table]" & _
         " SET [Location]='" & Me.Location1.Value & "'" & _
         " WHERE [Location Table].[Custom Label] = '" & Me.ScanItem.Value &  "'"

A more proper solution using parameters:

strSQL = "UPDATE [Location Table]" & _
         " SET [Location]= paramLocation" & _
         " WHERE [Location Table].[Custom Label] = paramLabel"
Set qdf = CurrentDb.CreateQueryDef("", strSQL)
qdf.Parameters("paramLocation").Value = Me.Location1.Value
qdf.Parameters("paramLabel").Value = Me.ScanItem.Value
qdf.Execute

edited Oct 17, 2017 at 6:43

answered Oct 16, 2017 at 19:37

Erik A

32.8k13 gold badges49 silver badges75 bronze badges

Collectives™ on Stack Overflow

MS Access Update Query (Multiple Where Conditions)

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related