Joins don't help

[FMDC]

I have a database with a field called account which links to an account field in a database with the account (General Ledger account numbers) and descriptions of the G/L accounts.

For some reason the software adds the characters "-C" to the end of the account number of the record if the transaction is flagged to be a change order invoice. The problem is that the report ignores all the records where the account number has an appended -C after the alphanumeric account number. Suppose the account number for telephone expense was 7320, the record pulls the description from the other database, say "Telephone expense" However, if the record has an account number 7320-C the record will be ignored. Presumably because there are no account numbers in the second database with -C in the account number.

I have tried every single join I can think of and nothing seems to work. I thought there may be a way to make a formula that truncated the -C from an account number field if it contained a -C but I can'f figure how to do that as the account number may have 1 to 6 characters in length. IF that were an option I'm not familiar with how to link the formula to the field in the database with account numbers and descriptions.

Thank you.

 

[dgillz]

This requires a sub report.

Use your formula to get rid of the "-C". Write a report with only data from that one table.

Write a separate report from the other table. Insert the first report as a subreport, linking the database field and the formula field.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[lbass]

The formula would be:

if instr({table.acct}) > 0 then
left({table.acct},instr({table.acct},"-")-1) else
{table.acct}

You can then link the subreport to the main report by linking this formula to the account number.

-LB

 

[dgillz]

I think the first line of LB 's formula should be:

if instr({table.acct},"-")>0 then

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[lbass]

Oops, Don is correct.

-LB