Search code examples
ms-accesscrystal-reportssql-like

Crystal Reports LIKE [a-z][0-9][a-z][a-e]

I'm creating a report to select records in a table where a string field matches a certain pattern.

The records that I need are displayed in a grid, so I can see that there are around 50 records that should be getting selected. The query to get the records from Access uses WHERE Product_Description_Short LIKE '[a-z][0-9][a-z][a-e]'

When I then try to put this into a Crystal selection formula, no records are returned.

"{Product_Costs.Product_Description_Short} LIKE '[a-z][0-9][a-z][a-e]'"

How do I need to adapt the selection formula to allow me to select the same records as in the SQL query?

Removing the selection formula completely shows all records from the table, so it's definitely the query going wrong.

Solution

  • Crystal Reports does not support regex, but you can use some string functions to achieve what you want.
    Also there was a similar question with a link to a regex-library:
    Crystal reports: is there a way to regex in crystal reports?

    The following formulas use some of the basic string functions of Crystal Reports:

    Example 1:

    This formula works with character ranges. The problem here is that also other characters are included (for example umlauts like ä ö ü). If this is not what you want then "Example 2" should work.

    stringVar productDesc := "a3xZ";

Len(productDesc)=4 And
Left(productDesc,1) In "a" To "z" And
Mid(productDesc,2,1) In "0" To "9" And
Mid(productDesc,3,1) In "a" To "z" And
Right(productDesc,1) In "a" To "z"

    Example 2:

    This formula works with ASCII-codes and thus can exclude umlaut-characters.

    ASCII-codes 48 to 57 are numbers [0-9]
    65 to 90 are upper case characters [A-Z]
    97 to 122 are lower case characters [a-z]

    stringVar productDesc := "a3xZ";

Len(productDesc)=4 And
Asc(Left(productDesc,1)) In [97 To 122, 65 To 90] And
Asc(Mid(productDesc,2,1)) In 48 To 57 And
Asc(Mid(productDesc,3,1)) In [97 To 122, 65 To 90] And
Asc(Right(productDesc,1)) In [97 To 122, 65 To 90]

    So with the second example, the selection formula would look like this:

    Len({Product_Costs.Product_Description_Short})=4 And
Asc(Left({Product_Costs.Product_Description_Short},1)) In [97 To 122, 65 To 90] And
Asc(Mid({Product_Costs.Product_Description_Short},2,1)) In [97 To 122, 65 To 90] And
Asc(Mid({Product_Costs.Product_Description_Short},3,1)) In 48 To 57 And
Asc(Right({Product_Costs.Product_Description_Short},1)) In [97 To 122, 65 To 90]