Help With "trimming" text field

[Guest]

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..

 

[Guest]

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.

 

[Guest]

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

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.

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..

 

[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.

 

[Guest]

Karl you are a genius

This worked flawlessly

Chas

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

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.

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..