IN Predicate in UPDATE statement

  • Thread starter Thread starter Anon
  • Start date Start date
A

Anon

I have an update statement that uses the IN predicate the exclude a
list of values from another table. However, I would like the values
to have wild cards at the end rather than having to store every
variation that I'm trying to exclude. Any suggestions?
 
I have an update statement that uses the IN predicate the exclude a
list of values from another table. However, I would like the values
to have wild cards at the end rather than having to store every
variation that I'm trying to exclude. Any suggestions?
Click to expand...

You can use LIKE, or you can use IN - but not on the same argument.
The IN() syntax does not recognize wildcards. You may just need to use

[fieldname] NOT LIKE "dk*" AND [fieldname] NOT LIKE "kd*" AND
[fieldname] NOT LIKE "gq*"

etc.



John W. Vinson [MVP]
 
billsinc said:
I have an update statement that uses the IN predicate the exclude a
list of values from another table. However, I would like the values
to have wild cards at the end rather than having to store every
variation that I'm trying to exclude. Any suggestions?
Click to expand...

This is usually frowned upon in this newsgroup,
but if you saved following function to a code module,
I believe following

WHERE NOT fFldInList([textfield],"ab*, cdefg, bk*")

will *slowly* exclude records
where textfield starts with "ab" or "bk"
or textfield = "cdefg"

'*** start code ***
Public Function fFldInList(strFld As Variant, _
strList As Variant) As Boolean
On Error GoTo Err_fFldInList
Dim arrList As Variant
Dim strElement As String
Dim strWildCard As String
Dim i As Integer


'Returns TRUE only if
' 1) field string = an element of list
' 2) field string starts with an element (less "*")
' that ends with "*"
'
'?fFldInList("abcd","ad,ab*")
'True
'?fFldInList("abcd","a**d,bc*")
'False
'?fFldInList("abcd","ad,abcd")
'True

fFldInList = False
If Len(Trim(strFld & "")) > 0 Then
'continue
Else
'field value either Null or ZLS
Exit Function
End If

If Len(Trim(strList & "")) > 0 Then
'continue
Else
'list either Null or ZLS
Exit Function
End If

arrList = Split(strList, ",", -1, vbTextCompare)
For i = 0 To UBound(arrList)
strElement = arrList(i)
If Right(strElement, 1) = "*" Then
'element ends in wildcard,
'so test if strElement (less "*") starts strFld

'strip element of wildcard
strWildCard = Left(strElement, Len(strElement) - 1)
'cannot be true if wildcard is bigger than strFld
If Len(strWildCard) > Len(strFld) Then
Exit Function
Else
'does field string start with strWildCard?
If strWildCard = Left(strFld, Len(strWildCard)) Then
fFldInList = True
Exit Function
Else
'continue comparisons
End If
End If
Else
'no wildcard, so test equality
If strElement = strFld Then
fFldInList = True
Exit Function
Else
'continue comparisons
End If

End If
Next i

Exit_fFldInList:
Exit Function

Err_fFldInList:
MsgBox Err.Description
Resume Exit_fFldInList
End Function
'*** end code ***

If nothing else, I'm sure purists will object
to so many function exit points....

I have used similar function when I am "assembling
the list" in code into a string variable, then redefining
or running a query from code, i.e., something like...

strList = "ad, ab*"

strSQL = "SELECT f1, f2, f3 FROM tbl " _
& "WHERE fFldInList([f1],""" & strList & """);"
Debug.Print strSQL
CurrentDb.QueryDefs("someqry").SQL = strSQL

would give following in Immediate Window

SELECT f1, f2, f3 FROM tbl WHERE fFldInList([f1],"ad, ab*");

and "someqry" would then have that SQL...
 
Perhaps you could post the SQL you are attempting to use.

You might be able to use something like:

UPDATE TableA LEFT JOIN TableB
ON TableA.MatchThis LIKE TableB.MatchThis & "*"
SET TableA.SomeField = "Some Value"
WHERE TableB.MatchThis is Null

Or if you want to match exact values in some cases and wild card matches
in others then you would need to include the wild cards in the
TableB.MatchThis field and drop it from the On clause

UPDATE TableA LEFT JOIN TableB
ON TableA.MatchThis LIKE TableB.MatchThis
SET TableA.SomeField = "Some Value"
WHERE TableB.MatchThis is Null
 
I have an update statement that uses the IN predicate the exclude a
list of values from another table. However, I would like the values
to have wild cards at the end rather than having to store every
variation that I'm trying to exclude. Any suggestions?
Click to expand...

You can use LIKE, or you can use IN - but not on the same argument.
The IN() syntax does not recognize wildcards. You may just need to use

[fieldname] NOT LIKE "dk*" AND [fieldname] NOT LIKE "kd*" AND
[fieldname] NOT LIKE "gq*"

etc.

John W. Vinson [MVP]
Click to expand...

The problem with this scenario is that LIKE can only return one field,
or so Access tells me. I'm familiar with how to use LIKE with
wildcards, but I'm trying to add a wildcard character to a list of
values in a table.
 
My code follows. I know it's not right, but I've been playing with
different combinations.

SELECT MailList.Owner_Name
FROM Exclude RIGHT JOIN TaxRoll ON Exclude.Exclude_Owner =
TaxRoll.Lessor_Name
GROUP BY MailList.Owner_Name, MailList.Flag
HAVING (((MailList.Owner_Name) Not In (SELECT Exclude_Owner FROM
Exclude WHERE Exclude_Owner Like Exclude_Owner & "*"));

I'm trying to get the subquery right as a separate query. But this
does not seem to work either.

Not In ([Exclude].[Exclude_Owner] & "*")
 
Sorry, my FROM statement posted prior was incorrect.

It should read:
FROM Exclude RIGHT JOIN MailList ON Exclude.Exclude_Owner =
MailList.Owner
 
I'm not sure which table and field you would want to update. Do you
want to update a field in MailList? I am guessing you would need
something like the SQL below. TEST this on a copy of your data or make
a backup first.

If the Exclude.Exclude_Owner is ever null then you need to be careful
with concatenation. Using
null & "*"
would end up matching every record in MailList that had a value.

Untested code follows

UPDATE MailList LEFT JOIN Exclude
ON MailList.Owner_Name Like (Exclude.Exclude_Owner + "*")
UPDATE MailList.[SomeField] = "Some value"
WHERE Exclude.Exclude_Owner is Null



The query you posted would never return a record as far as I can tell.
You are joining on two fields and then negating the join in the where
clause using the same two fields.


SELECT MailList.Owner_Name
FROM Exclude RIGHT JOIN MailList
ON Exclude.Exclude_Owner =
MailList.Owner
GROUP BY MailList.Owner_Name, MailList.Flag
HAVING (((MailList.Owner_Name) Not In (SELECT Exclude_Owner FROM
Exclude WHERE Exclude_Owner Like MailList.Owner & "*"));
 
Thanks John. But I'm getting a missing operator in query expression
when I run your query. And no, the exclude list does NOT have null
values.
 
I found the error in your query and modified it to the following:

UPDATE MailList LEFT JOIN Exclude
ON MailList.Owner_Name Like (Exclude.Exclude_Owner + "*")
SET MailList.Flag = "Z"
WHERE Exclude.Exclude_Owner is Null

However, it just return the MailList.Flag field with a bunch of null
values.
 
My Fault I just selected table view instead of running the query. It
appears as though that worked. Thanks a bunch...now I can go home!
 
Back
Top