Crystal XI - Convert number to text

[marisam]

I need to convert an oracle number field to a text field in CR.
The field name is {Sheet1_.Alphanumeric}

I tried To Text but it did not work.

 

[lbass]

The field name would suggest that it is a string already, not a number. Please show samples of the field.

-LB

 

[synapsevampire]

Try:

if isnumeric({table.field})then
totext({table.field},",",2)
else
"Blah..."

Saying totext did not work is akin to telling a doctor that you have a pain when you go like this...

State specifics, this is a technical forum, not a personal diary or blog. Did it produce an error? Did it return the wrong results, did it scare you because you didn'tunderstand it and now you're in therapy and suing crystal ?

The totext has switches the "," says the thousands seperator, the 2 is number of decimal places.

-k

 

[Turkbear]

Hi,
"Did not work" is not very informative - (did it provide an error message, blow up the PC, etc. )

Are you sure
{Sheet1_.Alphanumeric}
is an actual Oracle Number(x) data type or is it a
Varchar2 type that may have numeric-looking data?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[marisam]

I have a list of alphanumeric and numeric values.

3936032
398990
411
HN02005
HN02007
HN02044
HN02073
88/010
A020010

I extracted the alphanumeric/nmeric numbers from oracle and placed them into excel. I changed the column to text but none of the alphanumeric values are coming across. All I get is numeric. Why?
Please help

3936032
398990
411

 

[marisam]

I'm creating the report from an excel spreadsheet. It should read it as text but it's not. The field type in CR is number. How can I change it?

 

[synapsevampire]

Because Excel sux as a data source.

An external system will ask Excel what the column is, Excel looks at the first row and reports it's data type based on that.

Move the HN02073 to the top row and try again...

I know, it's hokey, but you're the one that used a spreadsheet as a database

If you want all values you'll be set, and you won't need totext.

-k

 

[marisam]

Thanks I tried the formula but it doesn't seem to work.
if isnumeric({alphanumeric)then
totext({alphanumeric},",",2)
I get a string is required error.

 

[synapsevampire]

Right, well that's because you don't understand Excel, Crystal or what data is.

Not to worry, we all started there, but you should be posting example data and expected output up front and asking for design help, not stating what you need as a totext has nothing to do with your problem, at least until you understand the theory and the tools you're working with.

Follow my last posts instructions.

-k

 

[marisam]

Hi synapsevampire I just get 0 in place of the alphanumeric character. I need to use excel b/c the alphanumeric's comments are external to the db. It's easier to link them this way rather than have someone enter hundereds of comments.

Thanks

 

[marisam]

Next to each alphanumeric is a comment

HN02005 comment
HN02007 comment

 

[synapsevampire]

I see and it's easier to not move the row as suggested?

This is a standard fix to your proiblem, you're wasting everyone's time now not doing as suggested.

Also there's no easy way to say this, you have difficulties conveying meaningful information. The technical arena might not be a good choice foryou, unless you slow down and give more thought to problem solving.

A suggestion is made, and you come back with that post?

What does having a comment have to do with anything???

Why didn't you try what was later suggested and respond back with your findings???

The totext you were initially asking about, is used for numerics, your problem is PRIOR to the data getting into Crystal it is being stripped because Excel is reporting the column as a numeric. I gave a solution but of course you hadn't correctly conveyed the problem.

So move an alpha to the top, and Excel will correctly report it as an alpha field.

Problem solved.

-k

 

[marisam]

I did try your fix, that is moving one of the alpha's to the top of the spreadsheet. I thought I had stated tha earlier.

 

[synapsevampire]

And once you save it and then read the file do you get all rows now?

Make sure that you issue a Database->Verify database

Again, Exxcel looks at it's first row and states that as it's data type, which is really lame.

Sowhat you were experiencing is that the first rowwas a numeric so when the data was read in (prior tomaking it to Crystal), it tossed out rows that were not numeric.

Once you learn the trick you can use Excel as a data source however you're better served to use real databases or text files.

Consider MS Access in the future as a temp database.

-k