"Like" formula

Leesha
BronzeLounger
Posts: 1357
Joined: 05 Feb 2010, 22:25

"Like" formula

Post by 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
3StarLounger
Posts: 287
Joined: 09 Mar 2010, 23:16
Location: Canberra Australia

Re: "Like" formula

Post by 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.
Regards

John

User avatar
HansV
Administrator
Posts: 74138
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: "Like" formula

Post by 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)
Regards,
Hans

Leesha
BronzeLounger
Posts: 1357
Joined: 05 Feb 2010, 22:25

Re: "Like" formula

Post by 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

User avatar
HansV
Administrator
Posts: 74138
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: "Like" formula

Post by 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.
Regards,
Hans

Leesha
BronzeLounger
Posts: 1357
Joined: 05 Feb 2010, 22:25

Re: "Like" formula

Post by Leesha »

Perfect!! What a day!

Thanks,
Leesha