Instr

[Breecy via AccessMonster.com]

I would like to return the left of the % in my field if a % is in the field.
Currently I have the following code in my query:

Formatted_Rate: Left([Security Description],Len([Security Description])-InStr
([Security Description],"%")-1)

The problem is that this return information for every row, reguardless if
there the is % symbol in the field I am querying or not. What am I doing
wrong?

 

[Jeff Boyce]

If the field is formatted as a percentage field, the "%" isn't actually
saved in the field.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[KARL DEWEY]

Use two calculated fields --
Formatted_Rate: Left([Security Description], InStr([Security
Description],"%")-1)

Check_For_%: InStr([Security Description],"%")
Criteria -- >0

 

[John W. Vinson]

I would like to return the left of the % in my field if a % is in the field.
Currently I have the following code in my query:

Formatted_Rate: Left([Security Description],Len([Security Description])-InStr
([Security Description],"%")-1)

The problem is that this return information for every row, reguardless if
there the is % symbol in the field I am querying or not. What am I doing
wrong?

Get sneaky:

Left([Security Description] & "%", Len([Security Description]) + 1 -
InStr([Security Description] & "%","%")-1)

If Security Desription contains a percent it will find it; if not, you've
added one at the end and will return the entire string.

 

[Breecy via AccessMonster.com]

Great idea John! I fixed it yesterday and hadn't had a chance to post back.
What I did what put the % in the criteria for the security description. It
worked like a charm. It was so simple I was a little embarassed that I
didn't think of it sooner.