strIng expression

  • Thread starter Thread starter chico
  • Start date Start date
C

chico

HELLO, IM TRYING TO PARSE EVERYTHING AFTER THE HYPHEN IN A
A COLUMN BUT WANT TO RETURN THE WHOLE RECORD IF A HYPHEN
DOESNT APPEAR IN A RECORD.
EXAMPLE
EXAMPLE RETURN
101 101
102 102
103-2 103
1035-2 1035
1056-5 1056
20345 20345
A45-2 A45
11111 11111
 
There may be other ways to do this but this works for me
(Access 2000)

Alias: Left([myField],IIf(InStr([myField],"-")=0,Len
([myField]),(InStr([myField],"-")-1)))

You want to use the Left() function to return the first
part of the field but we don't know what character to stop
on.

Use the IIF() function to determine if "-" exists in the
field. If it doesn't we want to end the Left() function on
the last character in the field which the Len() function
will tell us. If the field does contain a "-" we want to
end the Left() function one character before the "-" which
we get my using the InStr() function minus 1.

HTH
 
Chico,

SELECT YourTable.AField,
IIf(InStr([AField],"-")>0,Left([AField],InStr([AField],"-")-1),[AField]) AS
NewField
FROM YourTable
WHERE (((YourTable.AField) Is Not Null));
 
Back
Top