3 Replies Latest reply: May 15, 2014 10:04 AM by Nick Osdale-Popa

# Wildcard and Instr Problems

Hi,

I am trying to filter my data based on a text field - TRAN. I want to keep those records where the text field contains "TRANSFER".

I tried using wildcards: TRAN = "TRANSFER"

All records were returned.

I tried using Instr: Instr(TRAN,"TRANSFER") > 0

Zero records were returned.

Then I created a calculated field using the wildcards above:

If(TRAN = "TRANSFER", 1, 0)

All records returned a 1, even those without "TRANSFER" in them.

Then I created a calculated field using the Instr function above:

If(Instr(TRAN,"TRANSFER")>0, 1, 0)

All records returned a 0, even those without "TRANSFER" in them.

If I created a calculated field using Instr and enter the entire field:

Instr(TRAN,"ACCOUNT TO ACCOUNT TRANSFER")

The number 1 would be returned of course for those that matched entirely and 0 for the rest. However, TRAN field may have numerous phrases with "TRANSFER" in it, so I don't want to have to find and enter all of them.

HELP!

Thanks,

JSR

• ###### Wildcard and Instr Problems

Sorry...forgot to let you know, I am using Pro 10.

• ###### Wildcard and Instr Problems

I feel so humiliated...I played around some more and found I mixed up my arguments with Instr function...once I switched them I was golden:  Instr("TRANSFER",TRAN)>0

However, I would still like to know why the Wildcards are not working.

• ###### Wildcard and Instr Problems

However, I would still like to know why the Wildcards are not working.[/QUOTE]

That's a very interesting question.

I tried a filter to look for vendors:

= "TAY*"

It found one occurrence for Taylor

I tried

= "?AY*"

It found 4 occurrences:

Taylor...

Day...

Jay...

Ray...

I then tried

= "AY"

= "*AY"

Each returned all records.

So it seems that if the "*" is the first character in the match, it disregards all trailing characters, thus matching all occurrences. Don't know if this is by design or a bug. I'd go with the latter as it's not performing as one would think it should.

In searching the Help File, the only place I found that even mentions the way the * wildcard should work is when searching text in the Tree View.