Truncating fields

[Delius]

I can't help but think that this ought to be an easy one, but I just don't know how to achieve it:

I have a linked table within my database, the source table for which contains a field called Disc%, and the values for this field are in the format #.#%, e.g. 5.0%. I need to use this number in a calculation (just as the number 5 in this example) but I am having trouble trying to convert the Text field into an integer - as the figure after the decimal is always 0, I just need to truncate the field to extract whatever comes before the decimal point as a number.

Since I do not have control over the source table I am hoping I can do this in Access - preferably via a link specification, but if not then perhaps via a make-table query as the data needs slimming down anyway. All advice gratefully received!

 

[JMG]

Look up CINT expression
converts a string to integer

 

[Pat Hartman]

Try the Val() function. It strips off leading numeric characters. You will then need to divide the result by 100.

Val(YourField)/100

 

[Delius]

Re:

Thanks both, I have ended up using the Val() function; however this is only working across 95% of my data, since it turns out not every field is #.0 and where I have a decimal value other than zero (e.g. 3.1%) Val() returns Error#. I know I said previously that the figure after the decimal point was always zero, but this database has over 50,000 records and I guess I didn't know them all intimately enough until now .

I tried using a combination of the two functions, e.g. CInt(Val([field])) but this was just guesswork and didn't make any difference to the result. For absolute accuracy it looks like I would need to produce all the numbers both before and after the decimal place, but I'd be happy with a rounded figure so long as it works!

Thanks again
Delius

 

[Mile-O]

Then CSng() should be fine.

 

[Delius]

Re:

Trying CSng is just giving me error results - seems it requires a purely numeric field, whereas I need to extract (for example) the "3.2" portion of an entry of "3.2%".

 

[Mile-O]

CSng(Left([MyField], InStr(1,[MyField],"%") - 1))

 

[Delius]

CSng(Left([MyField], InStr(1,[MyField],"%") - 1))

Thanks very much indeed - all working now.