LEFT or LEFTB with Access

  • Thread starter Thread starter RobertM
  • Start date Start date
R

RobertM

Excel has a feature LEFT or LEFTB which only accepts a certain # of
characters from the left onward. It will turn 3115-TEYPl into 3115 by only
accepting the first four characters. Is there any way to do this with an
access query?

Thank you,
Robert
 
Excel has a feature LEFT or LEFTB which only accepts a certain # of
characters from the left onward. It will turn 3115-TEYPl into 3115 by only
accepting the first four characters. Is there any way to do this with an
access query?

Thank you,
Robert
Click to expand...

For the first four characters (whatever is in the field) use

Left([fieldname], 4)

as a calculated field in your query.

For the characters up to but not including the hyphen (e.g. 31-YXZ becomes 31,
3115-TEYPI becomes 3115) use Raskew's suggestion.
 
John W. Vinson said:
Excel has a feature LEFT or LEFTB which only accepts a certain # of
characters from the left onward. It will turn 3115-TEYPl into 3115 by only
accepting the first four characters. Is there any way to do this with an
access query?

Thank you,
Robert
Click to expand...

For the first four characters (whatever is in the field) use

Left([fieldname], 4)

as a calculated field in your query.

For the characters up to but not including the hyphen (e.g. 31-YXZ becomes
31,
3115-TEYPI becomes 3115) use Raskew's suggestion.
Click to expand...

Actually, you could also just use the Val function.

?Val("31-YXZ")
31
?Val("3115-TEYPI")
3115
?Val("31-123")
31
 
Val would not necessarily be a good idea.

VAL("0038-xxys") returns 38 and not 0038



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

John W. Vinson said:
Excel has a feature LEFT or LEFTB which only accepts a certain # of
characters from the left onward. It will turn 3115-TEYPl into 3115 by only
accepting the first four characters. Is there any way to do this with an
access query?

Thank you,
Robert
Click to expand...
For the first four characters (whatever is in the field) use

Left([fieldname], 4)

as a calculated field in your query.

For the characters up to but not including the hyphen (e.g. 31-YXZ becomes
31,
3115-TEYPI becomes 3115) use Raskew's suggestion.
Click to expand...

Actually, you could also just use the Val function.

?Val("31-YXZ")
31
?Val("3115-TEYPI")
3115
?Val("31-123")
31
Click to expand...
 
Back
Top