Query to delete characters after first hypen

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

 

[Krzysztof Pozorek [MVP]]

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

Split(Expression,"-")(0)

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

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

 

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

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

 

[raskew via AccessMonster.com]

Hi -

You can use the Instr() function to locate the first hyphen.
Example from the debug (immediate) window:

x = "NGBFE-434142-EUR-B"
? left(x, instr(1, x, "-")-1)
NGBFE

HTH - Bob

 

[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

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.

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

 

[Krzysztof Pozorek [MVP]]

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.

Split(Expression,"-")(2)

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

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

;-) KP

 

[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

 

[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!)