help with formula 1

[Durango122]

Hi folks
I need your help on this one
Cr XI
access database

here's my problem
i have this report and it contains these 3 fields
example

Key CMSI ID Dun ID
1 12-34-56
2 45-56-78
3 12-45-67 37-65-89
4 32-65-89 32-65-89

i made 2 formula to remove the "-"

formula 1 @CMSI
replace({cmsiID},"-","")
formula2 @DunID
replace({dunID},"-","")
that works fine
result i get this now
Key CMSI ID Dun ID
1 123456
2 455678
3 124567 376589
4 326589 326589
now in the Report-> record selection-> records
i have this
replace({cmsiID},"-","") <> replace({dunID},"-","")
that return all that are not equal to each other that ok
works fine i get key 3
heres my problem
i have a key number 1 to 4
if
key 1 and cmsi id has data and dun id is null they dont show
the same if key 2 dun id has data and cmsi is null they dont show
i need to show the key and the data for cmsi id and dun id if one has data and the other is null
key 3 does show for there not the same
if the key 4 has the same data in cmsi id and dun id they dont show thats ok since there the same value.i have that in the first filter
replace({cmsiID},"-","") <> replace({dunID},"-","")
can someone help me please

thank you


Durango122

 

[lbass]

The simplest solution might be to go to file->report options->check "convert database null values to default".

-LB

 

[Madawc]

If you don't want to dump your null values, use IsNull ({your.field}). Make sure that you test a field for nulls before trying to do anything else with it. Formula fields stop if they hit a null without IsNull.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[Durango122]

hi
i have this little problem
i have these 2 fields value showing up

787176916 787176916
they look identical they should not show
i tried trim(replace({cmsiID},"-","")) also
left(trim(replace({cmsiID},"-",""))
on both fields and they still show up
they should not show for there identical
Not sure what's going on
Can some help me please
thanks



Durango122

 

[lbass]

Try a formula for each and see if they match:

len({table.string})

-LB

 

[Durango122]

hi lbass
i have this result return
1 is
@Org_dun @CMSI_Num
10.00 and the other is 9.00
so there something in it that has an extra space somewhere
but where

thanks

Durango122

 

[lbass]

Are you sure you don't have a space between the quotes in your replace formula? Trim() will remove any spaces before or after the digits.

-LB

 

[Durango122]

hi lbass
i tried it and it move 10 to 9 like the other field
trim((replace({Query_Dun_Tax_Pg.duns_number},"-","")))
and i have this to the other field
trim(replace({Query_Dun_Tax_Pg.DUNS #},"-",""))
but they still show
mystery :-(



Durango122

 

[Durango122]

hi
found this in one of the filed

some of the number dont have a "-" betwwen the number but a blank space

this remove the "-"
trim((replace({Query_Dun_Tax_Pg.duns_number},"-","")))
and i have this to the other field
trim(replace({Query_Dun_Tax_Pg.DUNS #},"-",""))

but not if there a blank space instead of "-"


Durango122

 

[lbass]

Then try:

replace({@Org_dun}," ","")

...and do the same for the other. Or to text for spaces, try:

replace({@Org_dun}," ","X")

-LB

 

[Durango122]

thanks lbass that did it
ur the man

Durango122