"Like" formula

[Leesha]

Hi,

I've used "Like" in queries where I'm looking for like items in the beginning of the cell. I have a cell that can contain data that will look like the following example:

.5-hhrg:C2F2S1-hipps:1BGKS-wi:1.1398-cmw:0.8221

I need to do two things. First I need to filter the query bassed on the information after the first colon, IE
C2F2S1-hipps

"hipps" is the portion that will always be standard so I thought that I could use this is the "Like" filter.

The second thing I need to do is create another column in the query that contains the first portion of the hipps phrase. In this example the result would be:
C2F2S1.

To do the first part I tried using the following but it didn't work:

Like "*hipps"

The control name that I'm trying to filter is [bill_desc]

Thanks!
Leesha

[JohnH]

Like "*hipps" would only work if the field ended with hipps

Try: Like "*hipps*"

For your calculated column, do you want to return what is between the colon and hipps? You can use the InStr function to find the position of each, then the Mid function to return what is between those two positions.

[HansV]

In your query, you could use this:

MiddlePart: Mid([bill_desc], InStr([bill_desc], ":")+1, InStr(InStr([bill_desc], ":")+1, [bill_desc], "hipps")-InStr([bill_desc], ":")-1)

[Leesha]

Both of these worked great!!!! I'm tickled. One more question. Is is possible to return the value without the hyphen at the end? IE C2F2S2 VS C2F2S2-
If not, the user will just have to live with it! I'm thrilled to get this piece.
Thanks,
Leesha

[HansV]

You can change the expression to

MiddlePart: Mid([bill_desc], InStr([bill_desc], ":")+1, InStr(InStr([bill_desc], ":")+1, [bill_desc], "hipps")-InStr([bill_desc], ":")-2)

i.e. -2 near the end instead of -1.

[Leesha]

Perfect!! What a day!

Thanks,
Leesha