Querying Code String for Values Containing an *

[MrMitchell]

Hey all,

I have a database that has a service code field that contains values such as "F*", "X?", etc., and it's formatted in this format for each work order with vertical bars to seperate out the indvidual codes ala:

Service Code String
I{|F*|X?|TW|

I keep running into issues where I'm trying to filter for service code "F*", and it's returning all values that begin with "F", and I was wondering if there was any way around having it autoformat into the Like operand? any help would be greatly appreciated, I know it's not the best way to format the string but I didn't design it or I would have ommitted *!! Thanks for any help!

 

[boblarson]

Create a new column in your query like this:

SvcCodeAsterisk:Instr(1, [Service Code String], Chr(42))

And then in your criteria you would put

<> 0

But that will only return any record which has the asterisk, so if you want a specific one, you would need to use


SvcCodeAsterisk:Instr(1, [Service Code String], "F" & Chr(42))

 

[vbaInet]

LIKE is failing because your string contains the operators that are used with the LIKE keyword, such as "*" and "?".

Look into the Instr() function.

By the way, why are the codes saved as one string?

 

[vbaInet]

Create a new column in your query like this:

SvcCodeAsterisk:Instr(1, [Service Code String], Chr(42))

And then in your criteria you would put

<> 0

But that will only return any record which has the asterisk, so if you want a specific one, you would need to use


SvcCodeAsterisk:Instr(1, [Service Code String], "F" & Chr(42))

Got there before me huh!! Looks like you've got a lot of time to yourself now

 

[boblarson]

Got there before me huh!! Looks like you've got a lot of time to yourself now

I'm procrastinating. I have an ultra hard problem to figure out and so I'm working my brain up to it. It is a search form with about 35 different items to filter a report by, including filtering by the subreports. So it is kind of wicked.

 

[vbaInet]

Sounds like a fun task!