Substring

CoffeeGuru

Registered User.
Local time
Today, 17:08
Joined
Jun 20, 2013
Messages
121
I have a query and one of the fields currently contains alphnumerics:

4 units
5 units
8 units etc.

I want my query to just bring back the numbers 4, 5, 8 etc

the highest value is 60 units so in Excel I would have the option of using the formula:
Left(a1,2)*1
By multiplying the result by 1, would ensure the resulting value was numeric. How can Access do this?
 
Hello CoffeeGuru, Welcome to AWF.. :)

The simple method would be Val.. as the first value appears to be the number..

EDIT: Just read your question completely.. Left is a valid function in Access too.. i.e. using that would give the same result..
 
You could use the Val() function.

Dim MyValue
MyValue = "2457" ' Returns 2457.
MyValue = " 2 45 7" ' Returns 2457.
MyValue = "24 and 57" ' Returns 24.

It takes a string as an argument and returns a number based on what it finds in a string. It ignores spaces and stops when it hits a non-numeric character.
 
OK guys
you may have guessed by now I am a dangeriously new user of Access.
Field MinOrdQty:Val([fieldname]) seems to work was that what you had in mind?
 

Users who are viewing this thread

Back
Top Bottom