CODEWITHSUNDEEP
expressionms-access-2010
Muhnamana
Muhnamana

Reputation: 1044

Extract Whole Number From Decimal In Access Expression

I'm having trouble extracting the whole number from a number field in the expression builder in Access.

Here's what I thought would work:

Left([NumField],InStr(1,[NumField],".")-1)

This does not work. Upon running my query, I get a message box popping up stating "Invalid Call Procedure"

Field before the expression = 1.4

Field after the expression = 1

Thus taking the whole number and extracting that. Of cource the whole number could be 3, 30 or 300. The length will be different at all times, so you can't use trim left for a length of 1.

Suggestions?

Upvotes: 1

Views: 7078

Answers (1)

HansUp
HansUp

Reputation: 97131

You appear to be treating your numeric value as a string in order to extract the whole number part. Suggest you consider the Int() function instead.

Int([NumField])

There is also a related function, Fix(). From the Access help topic:

The difference between Int and Fix is that if number is negative, Int returns the first negative integer less than or equal to number, whereas Fix returns the first negative integer greater than or equal to number. For example, Int converts -8.4 to -9, and Fix converts -8.4 to -8.

I assumed you wanted a number returned by the query. If you actually want the number as a string, you could use CStr(Int([NumField])) or Format(Int([NumField]), "#").

Upvotes: 2

Related Questions