Trming In MSaccess

[Laura1 via AccessMonster.com]

I have a filed with values such as:

1-9P2N8O-1
1-BUWQOF-3
1-CM4UQR-1-2

I need to get to the second -

Example:

1-9P2N8O-1 need to get to 1-9P2N8O

1-BUWQOF-3 need to get to 1-BUWQOF

1-CM4UQR-1-2 need to get to 1-CM4UQR

I have used trim and it gets me most of the way there but was wondering if
there is a cleaner way to get to the second dash?

I need to store the result of the "trim" to use it later....

THANKS!!!

 

[Bob Barrows]

I have a filed with values such as:

1-9P2N8O-1
1-BUWQOF-3
1-CM4UQR-1-2

I need to get to the second -

Example:

1-9P2N8O-1 need to get to 1-9P2N8O

1-BUWQOF-3 need to get to 1-BUWQOF

1-CM4UQR-1-2 need to get to 1-CM4UQR

I have used trim and it gets me most of the way there but was

It does?? Trim just removes leading or trailing spaces - it does not
seem at all relevant.

wondering if there is a cleaner way to get to the second dash?

A combination of Left and Instr will get you there.

Instr([fieldname],'-')
will return the position of the first dash. Use that position (plus one)
to determine the starting point for the search for the next dash:
Instr(Instr([fieldname],'-') + 1,[fieldname],'-')

You now have the position of the second dash, so use that position
(minus one) to determine the characters to retrieve via the Left
function:
Left([fieldname],Instr(Instr([fieldname],'-') + 1,[fieldname],'-') - 1)

I need to store the result of the "trim" to use it later....

No you don't. It is rarely necessary to store the result of a
calculation. Create a view (saved query) that creates a column based on
this calculation:

select *, Left([fieldname],Instr(Instr([fieldname],'-') +
1,[fieldname],'-') - 1) as truncatedvalue
from tablename

 

[Duane Hookom]

You can use an expression like:
Left([NoFieldNameGiven],InStr(3,[NoFieldNameGiven],"-")-1)
This assumes the first "-" is within the first 3 characters.

 

[John W. Vinson]

I have a filed with values such as:

1-9P2N8O-1
1-BUWQOF-3
1-CM4UQR-1-2

I need to get to the second -

Example:

1-9P2N8O-1 need to get to 1-9P2N8O

1-BUWQOF-3 need to get to 1-BUWQOF

1-CM4UQR-1-2 need to get to 1-CM4UQR

I have used trim and it gets me most of the way there but was wondering if
there is a cleaner way to get to the second dash?

I need to store the result of the "trim" to use it later....

THANKS!!!

Trim() doesn't do this: it just trims off leading and trailing blank
characters.

You'll need the InStr() function to find the position of the hyphen. The
function has a *VERY* peculiar optional *first* argument specifying where to
start, so you can nest the function to start searching at the first hyphen:

Left([field], InStr(InStr([field], "-") + 1, [field], "-") - 1)

to disentangle this, start from the inside out:

InStr([field], "-")

will return 2 for the string "1-CM4UQR-1-2"
Adding 1 to that tells Access to start searching the string at the third byte
(the "C")
The second InStr finds the hyphen after UQR
Subtract 1 to backspace over the hyphen
Left() returns the string up to that position

 

[Laura1 via AccessMonster.com]

Thank you so very much this worked like a charm.....

the straight forward trim was only getting me to the second dash...and
sometimes leaving it behind as the field was not consistent in size.

But the first dash is consitent so the intial three charcter idea was GREAT!!!
! Thank you all very much!!!

You can use an expression like:
Left([NoFieldNameGiven],InStr(3,[NoFieldNameGiven],"-")-1)
This assumes the first "-" is within the first 3 characters.

I have a filed with values such as:

[quoted text clipped - 18 lines]

THANKS!!!