SUBSTRING function

[mbfan]

What is the proper syntax for using the SUBSTRING function in an Access SQL
query? I am I am trying to remove last name from first name concatenated
together in the same field.

Many thanks in advance.

 

[John Spencer]

Take a look at Left, Right, and Mid functions. Access in its native mode
does not have a substring function. Mid probably comes closest.

LEFT(SomeString,N)
returns the left most n characters of a field, variable, or string
Right(Somestring,N) returns the rightmost n characters
Mid(SomeString,N,N1) returns N1 characters starting at the Nth position.

You might find Instr handy also. It can be used to determine the
position of a specific character (such as a comma or a space) in a string.

If you had a field with Spencer, John
Left([Yourfield], Instr(1,[YourField],",")-1)
could be used to return Spencer

And
MID([YourField,Instr(1,[YourField],",")+2),50)
could be used to return John
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[MGFoster]

What is the proper syntax for using the SUBSTRING function in an Access SQL
query? I am I am trying to remove last name from first name concatenated
together in the same field.

Many thanks in advance.

Access (JET) SQL doesn't have the SUBSTRING function. You'll have to
use the InStr$(), Mid$(), Left$() and Right$() functions.