Search for specific format

[rjonas]

Hi all - I'm trying to construct a search query on a particular field that has text contained within a specific format...

The text will be a combination of numbers and letters (hence the problem) but the format will always be the same...

I'm using the following formula, which works great when I know what the text is:

ProjID: IIf(InStr(1,[Transaction Description],"ABC"),Mid([Transaction Description],InStr(1,[Transaction Description],"ABC"),13))

When I use this formula, an example result I get is "ABC US3-BG020". I don't want the ABC part included in the result and there are times where ABC isn't in the field but the result I want is, so I can't rely on "ABC" as criteria.

What I would like to do is replace the "ABC" portion of the formula with a specific format and then have the query return the result..

For example, the format I'm looking for is "###-#####", where results would be something like "US3-BG020". The results can be a wide variety of letters and numbers but the format will always be 3 characters and a dash followed by 5 characters.

Any idea how I can construct the formula to give me the results I want? I can always run the query search as above and then do an update query to remove the "ABC" but would prefer to do it in one step if possible.

Thanks for the help!

Ron

 

[Beetle]

If you always want to return only the "###-#####" portion then just look for the dash, subtract 3 then take the next 9;

Mid([Transaction Description],InStr([Transaction Description],"-")-3,9)

Or maybe I misunderstood your post?

 

[rjonas]

Nope, you understood correctly... I tried this previously but got an "Invalid Procedure Call" error, so figured I'd done something wrong....

Any idea why I'd get an invalid procedure call? I added "is not null" in the criteria field as I know you can get this error on a null field/value...

I've probably got some syntax error or something but appreciate any other thoughts you may have...

Thanks! Ron

 

[rjonas]

Got it.... Using Beetle's suggestion and changing the parameter "1" to "4", the query ran and gave me the results I wanted.

Thanks!!