Selecting a text string within a record and displaying in Details Section

[0sprey]

CRXI using MSAccess

Each Part record contains a text field {COMMENTS} which is used to store notes and comments about the Part.

I am looking for a specific text sequence anywhere within {COMMENTS} as follows:

“Charge12345678” The word “Charge” with 8 characters following.

i.e. “Charge4567asd8”

How would I write a selection formula to select only those records that contain this condition

And

How would I write a formula to display Charge# for each record selected so that the text would be displayed in the Details Section....

Part# Charge#
235 4567asd8

 

[IanWaterman]

Use Instr({Comments}, "Charge"} this will return number of character C position.

this formula will then return your string

Mid({comments}, Instr({Comments}, "Charge"}+7, 8)
Ian

 

[0sprey]

Tested this these formula and they are working well.
Thank you.
There is another field in the database thst has similar data however it is a MS Access Memo field. Is there any possibility of using Instr function on a Memo fields ?

 

[pmax9999]

Just to demonstrate that there is almost always multiple ways to do the same thing in Crystal Reports, you could also use the Split function, eg:

Left(Split({Comments}, 'Charge')[2], 8)

Not any more efficient, but a little less typing.

As for an approach to use with a Memo field, I'd say it almost certainly won't work, but you could give it a try.

I've never done any reporting against an MS Access database but one alternative might be to convert part of the Memo field to Text with a SQL Expression (or a Command) and use that in your record selection. This will depend on the size of the memo field and where in it you would need to look, and assumes the Access has a Cast or Convert (or similar) that might enable you to convert it to VarChar or similar.

I recall having some success using this approach in the past with an MS SQL Server database, but don't remember the specifics (and might be that I tried it and it didn't work). Might be worth a try though.

Hope this helps.

Cheers
Pete

 

[0sprey]

Thanks for both of these options.
As far as testing instr on the Memo field I cannot do so as the Memo field in not being listed in the formula builder and therefore not selectable for use in a formula. In MS Access I tried to change the data type from long text to short text but it stated that this could result in data loss. So it seems that Memo fields derived from MS Assess can displayed in CR reports only. If anyone knows of a workaround please let me know. Would a newer version up from CRXI help ?

 

[IanWaterman]

Osprey
AS it MS Access I assume it an in house app, can you ask owner to add another Field which splits the Charge /other data out and make your life easier?

Pete
Always forget about Split, generally only use if there are multiple entries and I don't want the first one, but as you say just as good in this scenario.
BTW Assuming you do not have to type the field name as its a single click Intr() is less typing
Ian