Searching for (1 Viewer)

S

smilman

New member

Local time

Today, 06:32

Joined

Jan 17, 2019

Messages

5

• Jan 17, 2019

• #1

Our company is using an asterisk - * in the description to note an item's status. In a query, I want to select any item that has and asterisk in the description. The use of the same wild card in MS Access has presented some difficulty. I have been looking and trying solutions manually, but would appreciate help for this issue. The solution in Excel is =IFERROR(IF(FIND(CHAR(42),E5)>0,"Discontinued"),"").

Thank-you

 

pbaldy

Wino Moderator

Staff member

Local time

Today, 04:32

Joined

Aug 30, 2003

Messages

36,131

• Jan 17, 2019

• #2

Probably isn't very efficient but one way:

WHERE InStr(1,FieldName,"*")>0

 

isladogs

MVP / VIP

Local time

Today, 12:32

Joined

Jan 14, 2017

Messages

18,207

• Jan 17, 2019

• #3

If the entire field contents are just *, then use [*]. Access will add Like automatically in a query to give Like "[*]"
If it's just somewhere in the text string then use e.g. Like "*[*]*"

Using leading and trailing wildcards is also inefficient as it forces Access to search each record in full.

 

theDBguy

I’m here to help

Staff member

Local time

Today, 04:32

Joined

Oct 29, 2018

Messages

21,447

• Jan 17, 2019

• #4

Another way is:

Code:Copy to clipboard

SELECT...FROM...WHERE FieldName Like "*
[*]*"

 

S

smilman

New member

Local time

Today, 06:32

Joined

Jan 17, 2019

Messages

5

• Jan 17, 2019

• #5

Thank-you both for your update.
Both worked for my issue - returning the exact same number of records.
InStr(1,[Description],"*")>"0"
Like "*[*]*"

 

pbaldy

Wino Moderator

Staff member

Local time

Today, 04:32

Joined

Aug 30, 2003

Messages

36,131

• Jan 17, 2019

• #6

Use their method. Mine is probably less efficient as it applies the function to every record in the table.