Crystal Report on MS Access text field

[munda1]

Hello,
I can't search on a text field in using Crystal to read an Access data base. Can you help? Thank you.
Joe

 

[synapsevampire]

Try posting technical information, at least:

Crystal version
Database/connectivity used
Example data
Expected result

Crystal doesn't have a real search function, but it does have the instr and instrrev, which will tell you the location of specific text within a string type field.

You're probably experiencing the 254 character field limitation for formula use within versions Crystal 8.5 and below.

You have 2 simple alternatives, if you're using ODBC to connect to the Access table, a SQL Expression will work, or more appropriately, try creating a Query within Accsss which already performs the check for the text and takes some appropriate action, such as returning a field with the required result, or replacing or...? You can then base the report on this query.

-k

 

[munda1]

Thank you K.
I am using ODBC to connect to Access.
The data item e.g. "RE111-205J1206" is a data type of text.
The field is called Part Number.
Can I search for this in Crystal? I think you have said no.
The only fields that seem searchable are numeric types.
The reason I am trying to do this is that Crystal just reads the Access and won't (by user error) obliterate the data in the data base.

 

[synapsevampire]

String fields are searchable, those greater than 254 become text types, at least in older versions of Crystal, something you still hold as a closely guarded secret .

You can truncate the string to 254 characters using a SQL Expression, but that may not prove optimal if the text you are searching exceeds 254 chars, but if you know that not to be the case, then use the following in the SQL Expression substituting your field name:

mid(`Customer`.`Customer Name`,1,254 )

Now the SQL Expression will show up in the list of fields that can be use in formulas.

Again, use an Access Query or a SQL Expression for the fastest and most accurate results.

-k

 

[munda1]

Thank you for your reply.
I spoke with my local Crystal guy and he told me to reformulate my question as I am unexperienced in Crystal as follows:
Crystal 7.0
ODBC Connectivity


Trying to use the select expert in Crystal Report Designer, and 'Text' type fields from an Access Database are not an option to restrict on. They are also not an option to sort by.



Perhaps this makes more sense. Does it?

joe

 

[munda1]

Hi k, maybe this is clearer.

Crystal 7.0
ODBC Connectivity

Trying to use the select expert in Crystal Report Designer, and 'Text' type fields from an Access Database are not an option to restrict on. They are also not an option to sort by.

Joe

 

[synapsevampire]

SQL Expressions are available in Crystal 7.

Note they are a different formula field type, and although you might be able to create them from the CR 7 select expert IDE (I can't recall, you're working with a product that's about 7-8 years old), look under the formula fields for SQL Expression Fields. Once you create this SQL Expression, you can then use it in the select expert. I don't use the select expert, I edit those formulas directly.

At least hit F1 and search for SQL Expressions before responding again.

-k