I'm in WAY OVER my head!

[Seabz420]

I have 3 databases:
db1
db2
db3

Each database has a table that I has a field that I need to compare between the three:
db1 - memshp.memshp (number)
db2 - names.acct (character)
db3 - names.acct (character)

What I need to return are all the records from all three databases that have duplicate records between them. I'm envisioning that I need to use subreports. I've setup my main report to shop all the memshp's from db1. I've added a subreport for db2 that shows all the acct's from db2. And I've added a subreport for db3 that shows all the acct's for db3. NOW, how do I convert the acct fields to number for the memshp field to character? When I use totext, the resulting field looks like 1,234,567.00. What I need is for it to look like 1234567. Am I going about this all wrong? Any guidance is appreciated!

 

[Seabz420]

So far, to format my newly converted text field, is:

replace(totext({MEMREL.MEMNBR}),",","")

This removes the commas from the number. Now, how can put a rtrim(,3) into this?

replace(rtrim(totext({MEMREL.MEMNBR}),3),",","") gives me a "too many arguments" where the ,3 is.

 

[lbass]

You can either use the following:

totext({MEMREL.MEMNBR},0,"")

Or, change that field to a number:

val({MEMREL.MEMNBR})

...and then right click on the formula->format field->number and choose
"-1123" from the list to format it to no decimals or commas.

-LB

 

[Seabz420]

I ended up using a second replace funtion to replace the last ".00" in the field.

 

[kskid]

You could also format the number itself by right using the format editor under the number tab and click customize to set it with no commas and decimals

 

[Jacque]

Hi Seaqbz420,
Change your original formula to use Crystal Syntax and try:
replace(Right(toxtext(({MEMREL.MEMNBR}),3),",","")

Jacque