Wildcard search

[Victor70]

Dear Access experts,

I have a query where I want to distinguish between cases happened in my city versus others. There are multiple ways one can misspell that, so I am using wildcard search for this:

IIf(Trim([dbo_nmmain].[city])="PORTS*","Portsmouth",[dbo_nmmain].[city])

This works fine for all but one entry that returns me PORTS, which is the original name from [dbo_nmmain].[city]. The only way I was able to get the code convert this particular entry to Portsmouth is by deleting the asterisk in my formula.

I always thought that asterisk is any number of characters from nothing to infinity. Why does not it work in this case?

Thanks a lot in advance.

 

[vbaInet]

Can I get you to check this link out?

http://www.techonthenet.com/access/queries/like.php

It discusses the use of the LIKE condition which is what you seek.

 

[Victor70]

Thanks for the prompt reply.

I saw it but thought there is no difference between = "PORTS*" and Like "PORTS*".

Should it be than

IIf(Trim([dbo_nmmain].[city]) like "PORTS*","Portsmouth",[dbo_nmmain].[city])

?

 

[vbaInet]

The examples in that link didn't make any use of the IIF() function. It started with LIKE. Have a look again and if you don't get it I will tell you.

 

[Victor70]

I need the IIF() function in my expression to replace all entries (including misspelled ones) with the correct city name, while keeping the others unchanged. So, it is not just about filtering out entries that do not meet the criteria.

 

[vbaInet]

You need two things:

1. The criteria to find those LIKE 'PORTS*'
2. The IIF() function to replace PORTS with PORTSMOUTH

You also need to re-design your form because it shouldn't be allowing users to type in different variations of cities. They should be fixed.

 

[Victor70]

That's right.

I was hoping my IIF function would pick up everything. It works on all misspelled entries but the one I mentioned before. It is literally just one entry.

1. Can't I combine finding those LIKE "PORTS*" and replacing them with PORTSMOUTH in one IIF function?
2. What is the difference between ="PORTS*" and LIKE "PORTS*? I am somewhat confused because the IIF works fine for 99.9% of the cases.

The form where the data gets entered is actually another program and it has validation but it also has OVERRIDE function and sometimes, when it is down, validation does not work either. Anyway, I would not be able to modify that program.

 

[vbaInet]

1. Yes you can. Think about it, you've already filtered through those records that match PORTS* so all you do is replace whatever is in that field with PORTSMOUTH. But you have to be careful with that statement because it will match match PORTSFARM, PORTSMAN etc.

2. LIKE 'PORTS*' finds all words that begin with PORTS, whereas ='PORTS*' finds those records that are exactly PORTS*.

 

[Victor70]

VbaInet, thank you very much!

I changed = to Like and everything worked fine.

Still cannot understand though why my previous syntax was able to pick up all misspelled city names (with an exception of one instance), if ="PORTS*" calls for exact match only.

 

[vbaInet]

You're welcome!