Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Format Numbers in a String

Format Numbers in a String

Format Numbers in a String

We are using Crystal to pull information out of our ERP system. I have a field that is used during data entry and is entered as

However, when I pull the field into Crystal it is showing as

The field type is string
Length is 60

I am trying to format the data entry field (with no luck) as it was entered .000001

I have tried RIGHT, LEFT, MID, TRIM, ToNumber (I get that an error that the string is Non-numeric), NumericText and nothing I do works. The field will not allow me to format it either.

Any suggestions?


RE: Format Numbers in a String

Hi Laura.

I'm guessing you have tried it, but if not try the VAL() function. It ignores non numeric characters, and in my test returned 1.0.



If that doesn't work it may be something specific to the ERP system. Is this the 1st time you have identified the behaviour or does it happen everytime when returning this field? What happens if you delete the entry and re-enter it (through the app)?

Which ERP are you using?

Hope this helps.


RE: Format Numbers in a String

Thanks Pete! That returned a whole number of 1.00 even though the field was entered as .000001 (which is what is required) We are using the Lawson/Infor ERP system and we use Crystal Reports as our reporting software. We try to validate the data before releasing into our live system and the report is our double check to make sure that the information is entered correctly.

This field is used when employees work in more than one position. Unfortunately it is a required field and if it's entered as anything but .000001 they get double their vacation time accruals!


RE: Format Numbers in a String

So, does this issue arise only for a single record, or every record where theat particular field is used. If it is only one record, I suggest you looka at exactly what was entered, ie does it include non alpha-numeric characters such as Carriage Returns of Line Feeds. If the latter, deleting that entry via the application and re-entering it would be worth a try.

If it occurs for every record, it must be something to do with the way the record is stored in the database and I'd sugget you contact the software vendor for assistance.

Hope this helps.


RE: Format Numbers in a String

Hi Pete,

Unfortunately it happens with every record where we use that particular field. I may have to back into it this way.




I will have to test both of these to see if either one works. They both appear to with the one record that is currently entered but I will need to commit more records to fully test it.

Thanks for your help! Happy Friday! :)


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close