Return records based on third number in field 1

[alexlc]

Hi,

Our accounts system groups our sales into nominal codes, where the third number = sector.

Is there a way I can write a Selection Formula to only return records from SL_Analysis.SACODE where the third number = 3 (for example).

I've currently trimmed the field of the first 2 digit & am querying as "startswith 3", but sondered if there was a faster way.

Cheers Alex

 

[fisheromacse]

i would likely use:

MID({SL_Analysis.SACODE},3) = 3

 

[alexlc]

Thanks, but it doesn't like that & is returning no records at all. The number 3 may be used more than once in a single code, would that be affecting it?

Forgot to mention I'm on Crystal 9 & querying an SQL database via OLE.

Cheers, Alex

 

[lbass]

Assuming the field is a string, if you use mid, it should be:

MID({SL_Analysis.SACODE},3,1) = '3'

It would be more efficient if you used a SQL expression {%digit3} instead:

{fn substring(`SL_Analysis`.`SACODE`,3,1)}

Then you could use a selection formula like this:

{%digit3} = '3'

Again, this assumes the field is a string.

-LB

 

[fisheromacse]

doh!
thanks LB.

 

[alexlc]

Brilliant, first one worked perfectly Will have a play with the second one on a quiet day.

Thanks both.