update query with left function

[Repox]

Can anybody help me with the following problem.
I have a table with a field containing strings like: 'v-122'
Always two caracters and the followed by a number.
Now I want to make an update query that puts only the number in an other
field.
So I can sort the other by number.
I think I have to use the left function but I can't get it to work.
Thanx

 

[Ken Snell]

You can sort on this field's partial contents without putting the number
into another field. Create a query and put a calculated field in the query:
SortingValue: Mid([FieldName],3)
Then set the sorting order based on this calculated field.

 

[Repox]

Thanx
but this gives me an invalid syntax error
SortingValue: Mid([Number],3)

You can sort on this field's partial contents without putting the number
into another field. Create a query and put a calculated field in the query:
SortingValue: Mid([FieldName],3)
Then set the sorting order based on this calculated field.

--
Ken Snell
<MS ACCESS MVP>

Can anybody help me with the following problem.
I have a table with a field containing strings like: 'v-122'
Always two caracters and the followed by a number.
Now I want to make an update query that puts only the number in an other
field.
So I can sort the other by number.
I think I have to use the left function but I can't get it to work.
Thanx

 

[Phobos]

Ken Snells method works for me, but it extracts the numbers as text. Is
this a problem?

P

 

[Douglas J. Steele]

Having it as text can be a problem if you're trying to sort. 10 will sort
between 1 and 2, for example, not after nine.

To get around that, wrap the CLng function around the Left function:

SortingValue: CLng(Mid([FieldName],3))

 

[Ken Snell]

Thanks, Doug...I was in too much of a hurry earlier when I wrote the reply!

--
Ken Snell
<MS ACCESS MVP>

Having it as text can be a problem if you're trying to sort. 10 will sort
between 1 and 2, for example, not after nine.

To get around that, wrap the CLng function around the Left function:

SortingValue: CLng(Mid([FieldName],3))


--
Doug Steele, Microsoft Access MVP

Ken Snells method works for me, but it extracts the numbers as text. Is
this a problem?

P