Query to delete characters after first hypen

R

Rachel

I'm looking to return all the text before the first hypen

IBDT-1209-USD-B
INGBFE-434142-EUR-B
F/AGD-4079-NOK-B
ADE-434129-EUR-B

I would like to see:

IBDT
INGBFE
F/AGD
ADE

Thanks for the assistance.
 
K

Krzysztof Pozorek [MVP]

U¿ytkownik "Rachel said:
I'm looking to return all the text before the first hypen

IBDT-1209-USD-B
INGBFE-434142-EUR-B
F/AGD-4079-NOK-B
ADE-434129-EUR-B

I would like to see:

IBDT
INGBFE
F/AGD
ADE
Click to expand...


Split(Expression,"-")(0)

Test in immediate window:
?Split("IBDT-1209-USD-B","-")(0)
IBDT

K.P. Poland
www.access.vis.pl
 
F

fredg

I'm looking to return all the text before the first hypen

IBDT-1209-USD-B
INGBFE-434142-EUR-B
F/AGD-4079-NOK-B
ADE-434129-EUR-B

I would like to see:

IBDT
INGBFE
F/AGD
ADE

Thanks for the assistance.
Click to expand...

=Left([FieldName],InStr([FieldName],"-")-1)
 
S

Sally

Hi,
What do I use to return the USD or EUR currency code, I have tried changing
the -1 or +1 etc but I do not get just the currency returned.
--
Thanks
Sally


fredg said:
I'm looking to return all the text before the first hypen

IBDT-1209-USD-B
INGBFE-434142-EUR-B
F/AGD-4079-NOK-B
ADE-434129-EUR-B

I would like to see:

IBDT
INGBFE
F/AGD
ADE

Thanks for the assistance.
Click to expand...

=Left([FieldName],InStr([FieldName],"-")-1)
Click to expand...
 
K

Krzysztof Pozorek [MVP]

U¿ytkownik "Sally said:
Hi,
What do I use to return the USD or EUR currency code, I have tried
changing
the -1 or +1 etc but I do not get just the currency returned.
Click to expand...


Split(Expression,"-")(2)

Test in immediate window:
?Split("IBDT-1209-USD-B","-")(2)
USD

?Split("ADE-434129-EUR-B","-")(2)
EUR

;-) KP
 
S

Sally

Hi,
There are going to be many variations of USD & EUR, I would not want to add
each variation. Also I wanted to use something in the expression builder
rather than within a module?
thanks
sally
 
D

Douglas J. Steele

Not sure what you mean by "add each variation".

If you look at Krzysztof's solution, Split(Expression,"-")(2) retrieves
what's between the second and third hyphen, so what specific currency
indicator it is is immaterial. If the issue is that you can't be sure that
the currency indicator will always be between the second and third hyphen,
then you have a far more serious issue!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)
 
Top