Trim Numbers

[Crilen007]

Tried to search for this, maybe I am searching the wrong term.

Anyways.. I have a table linked from a large mainframe, and what I am trying to do is trim any numbers that are 1 million and over, and still keep it a number.

Currently I have this:

VEHICLE_KMS2: IIf([VEHICLE_KMS]>999999,Left([VEHICLE_KMS],6),[VEHICLE_KMS])

What I need is to have it remain as a number, seems that the left function turns it into a string value.

Thanks

 

[ghudson]

Just a quick thought, why not divide the number by 10 or 100 in your calculation.

 

[Pat Hartman]

Left() is a string function. String functions do not always work properly with numeric or date data since numeric data is not a string. ghudson't solution should solve your problem.

 

[Bat17]

Changing to string and back should work as well

VEHICLE_KMS2: IIf([VEHICLE_KMS]>999999,clng(Left(cstr([VEHICLE_KMS]),6)),[VEHICLE_KMS])

Peter

 

[Crilen007]

Hmm, thank you all for your replies =)

 

[Pat Hartman]

Bat17 - String functions are NOT reliable when used on numeric fields. Read help if you don't believe me. If you insist on using string functions, convert the number to a string first, use the function, then convert it back.

 

[Bat17]

convert the number to a string first

Pat - I did didn't I?

Peter

 

[Pat Hartman]

Sorry, my eyes are going bad