Trying to find text within a text string

[Faz]

I am trying to extract text before a certain character appears in a
string. This character is the letter "C".

Here is some sample data - the field is called REFERENCE_2:

REFERENCE_2
10299C17264
9841C17218

I want to extract 10299 for the first line and 9841 for the second
line into a SQL query. I am trying to use the FIND function so I can
get the position of C and do a MID function on this position from the
first character to the position of C - 1, but FIND does not work in
the query builer screen.

I am not proficient with VB and want to use the normal query
functionality.

Many thanks.

[PC Datasheet]

MyNumber = Left([Reference_2],Instr([Reference_2],"C")-1)
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"Faz" <af*****@hotmail.com> wrote in message
news:26************************@posting.google.com ...

I am trying to extract text before a certain character appears in a
string. This character is the letter "C".

Here is some sample data - the field is called REFERENCE_2:

REFERENCE_2
10299C17264
9841C17218

I want to extract 10299 for the first line and 9841 for the second
line into a SQL query. I am trying to use the FIND function so I can
get the position of C and do a MID function on this position from the
first character to the position of C - 1, but FIND does not work in
the query builer screen.

I am not proficient with VB and want to use the normal query
functionality.

Many thanks.

[almish]

af*****@hotmail.com (Faz) wrote in message news:<26************************@posting.google.co m>...

I am trying to extract text before a certain character appears in a
string. This character is the letter "C".

Here is some sample data - the field is called REFERENCE_2:

REFERENCE_2
10299C17264
9841C17218

I want to extract 10299 for the first line and 9841 for the second
line into a SQL query. I am trying to use the FIND function so I can
get the position of C and do a MID function on this position from the
first character to the position of C - 1, but FIND does not work in
the query builer screen.

I believe that Find() is an Excel function - in Access VBA you should use
the InStr() function to retrieve the position of another substring (in
Access 2000 and newer).

Something like this in a query's Field row:

FirstPart: Left$([REFERENCE_2], InStr([REFERENCE_2], "C") - 1)

assuming the REFERENCE_2 never has a Null value - otherwise you'll need to
use the Nz() function or the IIF() function to handle that possibility.

- John Mishefske