Access 2016 Form Query Issue

0
votes

The query grabs/obtains the criteria from a form. The problem is I have one field that is supplied in the data table that has multiple items separated by a comma. They are called Upgrade Codes:

Example: 18AAA, 8AAA, 2GAA, 1EAA

enter image description here

I am using Like [Forms]![Service Action Report]![Upgrade Codes] Or Like "*" & [Forms]![Service Action Report]![Upgrade Codes] & "*" in my query and it works all but also returns similar matches.

For example: 8AAA Form query search would return all records with 8AAA and also 18AAA since 8AAA is part of 18AAA this is not the desired outcome, is their a way to just get all records with the Upgrade Code supplied in the Form?

1
ms-access

1 Answers

0
votes

Try the following:

Like [Forms]![Service Action Report]![Upgrade Codes] Or 
Like [Forms]![Service Action Report]![Upgrade Codes] & ", *" Or 
Like "*, " & [Forms]![Service Action Report]![Upgrade Codes] & ", *" Or 
Like "*, " & [Forms]![Service Action Report]![Upgrade Codes]

By including the * , and , * in your query it actually looks like the following for a upgrade code value like 8AAA:

Like "8AAA" Or 
Like "8AAA, *" Or 
Like "*, 8AAA, *" Or 
Like "*, 8AAA"

The differend "or" seperated statements work like follows:

  1. "8AAA":
    Full match of 8AAA (there are no other values in the string)
  2. "8AAA, *":
    Match 8AAA only if it is the first comma seperated value in the list
  3. "*, 8AAA, *":
    Match 8AAA only if it is a middle value surounded by 2 commas
  4. "*, 8AAA":
    Match 8AAA only if it is the last value

By including the boundaries comma or beginning/end of the string you ensure that only full codes can match.