Selecting based on part of the characters from a field 1

[crystalpro]

The account number can is 15 characters (numbers and letters). The last three digits of the account number have been reserved for the department number.

I set up a prompt for the department.

I need to select a transaction when the last 3 digits of the account number for that transaction equal the entry made from the department prompt.

I set up some formulas:
Account Number = ToText({JrnlRow.GLAcntNumber})
Department = (@Account Number}, 3)

The prompt is a string that asks for the department. It is named ?Department Mask

I should be able to have a selection statement that says
{@Department} = {?Department Mask}

But I don't get any data. If I take out the above selection statement I get 1+ pages of data.

Please advise how I can solve this.

 

[synapsevampire]

Drop department into the report when you have data so that you can learn what it's returning.

You might need to trim the field, I would use:

right(ToText(trim({JrnlRow.GLAcntNumber}),0,"&quot,3)={?Department Mask}

-k

 

[crystalpro]

I put {@Department} in the details section and it only returns zeroes 000. However when I see the data I can see the account 5310.0.0.00.001

If I add the selection criteria as you stated above, I get
no data.

 

[MJRBIM]

OK, I used you sample to create the following sample table data.

AccountNumber
=================
"5310.0.0.00.001"
"8880.0.0.00.001"
"1234.0.0.00.002"
"5678.0.0.00.003"
"9012.0.0.00.003"
"5311.0.0.00.001"
=================

I used the following formula/parameter combination to return the Format below.

Name: {@Department}
Formula: Right ({Account_txt.AccountNumber},3 )

Parameter: {?Department}
Type: String
Edit Mask: 000
DefaultValues: 001
002
003

Record Selection Formula : {@Department} = {?Department}

The results below are based on a parameter entry of "001".

- - - - - - - - - - - - - - - - - - - - - - - -
AccountNumber Department
- - - - - - - - - - - - - - - - - - - - - - - -
5310.0.0.00.001 001
8880.0.0.00.001 001
5311.0.0.00.001 001
- - - - - - - - - - - - - - - - - - - - - - - -

This will work for a small data set, but long term as your account list grows you would be MUCH better served by creating a Database View that extracts Department as it's own reportable field.

Hope it helps...

 

[crystalpro]

Thank you so much for your help. I tried your suggestion in the last post and it worked like a charm.