Help With "trimming" text field

C

Chas

Hello all,
I am pulling a query via an ODBC/easysoft/borland DB into access.
The field that I am pulling is in text format like this: 1546-000 or this
1546-002 the key thing that I want to trim is just the number 1546 part the
-000 I need to lose in the queries results. As the key portion that I need to
extract is really everything before the -

So basically I am converting the records in a text field into a general
number.

When i use the query like so: =ltrim([membernumber],4) command I still get
numbers above my queries specified paramaters and they simply do not sort
correctly. I will need to do this for about 12,000 records within the borland
db. Any ideas how I can make this happen and keep my sorting status.

Any ideas, comments or concepts would be greatly appreciated..
 
C

Chas

I have tried the replace function as well and this trimmed it down but now I
get a 1546.003 instead of just the number 1546 (keep in mind that there are
sub records attached to this by the data following the - so 1546-001 and
1546-002 are all part of record 1546).

I just need to be able to query the "main" number and then be able to
select a range.
 
K

KARL DEWEY

Try this --
Left([YourTextField], InStr([YourTextField],"-")-1)


Chas said:
I have tried the replace function as well and this trimmed it down but now I
get a 1546.003 instead of just the number 1546 (keep in mind that there are
sub records attached to this by the data following the - so 1546-001 and
1546-002 are all part of record 1546).

I just need to be able to query the "main" number and then be able to
select a range.

Chas said:
Hello all,
I am pulling a query via an ODBC/easysoft/borland DB into access.
The field that I am pulling is in text format like this: 1546-000 or this
1546-002 the key thing that I want to trim is just the number 1546 part the
-000 I need to lose in the queries results. As the key portion that I need to
extract is really everything before the -

So basically I am converting the records in a text field into a general
number.

When i use the query like so: =ltrim([membernumber],4) command I still get
numbers above my queries specified paramaters and they simply do not sort
correctly. I will need to do this for about 12,000 records within the borland
db. Any ideas how I can make this happen and keep my sorting status.

Any ideas, comments or concepts would be greatly appreciated..
Click to expand...
Click to expand...
 
J

John Spencer

LTrim does not return part of the field, it only trims of spaces at the
beginning (Left) of the field.

Left([MemberNumber],4) would give you the first four characters as a string.

You could try using Val([MemberNumber]) if you are trying to get the first
part of the [MemberNumber] as a numeric value. This will convert the string
to a number that is equal to the first section of the [MemberNumber] field.
It will drop leading zeroes, so 0410-000 becomes 410.
 
C

Chas

Karl you are a genius :)

This worked flawlessly :)

Chas

KARL DEWEY said:
Try this --
Left([YourTextField], InStr([YourTextField],"-")-1)


Chas said:
I have tried the replace function as well and this trimmed it down but now I
get a 1546.003 instead of just the number 1546 (keep in mind that there are
sub records attached to this by the data following the - so 1546-001 and
1546-002 are all part of record 1546).

I just need to be able to query the "main" number and then be able to
select a range.

Chas said:
Hello all,
I am pulling a query via an ODBC/easysoft/borland DB into access.
The field that I am pulling is in text format like this: 1546-000 or this
1546-002 the key thing that I want to trim is just the number 1546 part the
-000 I need to lose in the queries results. As the key portion that I need to
extract is really everything before the -

So basically I am converting the records in a text field into a general
number.

When i use the query like so: =ltrim([membernumber],4) command I still get
numbers above my queries specified paramaters and they simply do not sort
correctly. I will need to do this for about 12,000 records within the borland
db. Any ideas how I can make this happen and keep my sorting status.

Any ideas, comments or concepts would be greatly appreciated..
Click to expand...
Click to expand...
Click to expand...
 
Top