Assistance with Like Operator

[MacroScope]

I am drawing insurance data into my DB by importing from Excel spreadsheets generated by each of several companies. The formatting of the policy numbers isn't identical to the way we have them recorded in our system, usually a different suffix. I've got a routine already in place that allows me to strip all the imported policy numbers of their suffixes. I'll give an example.

Policy number in our DB= MHP 0010636-01
Policy number coming from company via Excel= MHP 0010636-02

I've stripped the incoming policy number to read MHP 0010636, and now I want to match that to the MHP 0010636-01 that is in our database. I know I need to use a Like Operator with an asterisk. I would have no problem if it were a simple string. Like "MHP 0010636*" would return the result for the one policy. I want to match several thousand of them, so of course I'm using the field name rather than a literal string. Incoming numbers are [PolicyNum], while existing policy numbers are [PolicyNumber]. In a query in the PolicyNum field I don't know how to use the Like + * with a field name, to give me what amounts to Like [PolicyNum]*. Of course that syntax doesn't work.

Does anyone have a good syntax that will allow it to understand that I want it to match all of the field I'm interested in plus any additional characters.

Help would be appreciated.

 

[PHV]

Like [PolicyNum] & '*'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MacroScope]

Thanks. Your previous strip routine works perfectly and I have all numbers properly formatted. Now I just need to match them. Hopefully the syntax will do the trick. I'll let you know.

 

[MacroScope]

Just an update. I could not get it to work by using the Like statement as a criteria in a query. I got it to cooperate by first stripping the incoming Policy Number field to the exact format I needed to see for each company. I then used another query to create a calculated field from the existing database that stripped those numbers to the exact same format that I created for the imported fields without actually changing what's recorded in the database. I then joined the calculated field with the imported table field in another query, and voila! They were already a match in format at that point, and it was pretty straightforward from there to pull matching records.

The basic routine is now fully functional. I have a lot to do yet, calculating totals, creating a report, and a whole lot of other similar things, but what can be done now in about 15 seconds took the bookkeeper at least 3 full days to accomplish by hand. Needless to say they're thrilled, and they'll be doing Olympic class backflips when it's 100% completed.

Thanks for your help. It's been greatly appreciated.