By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,009 Members | 2,821 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,009 IT Pros & Developers. It's quick & easy.

Trying to find text within a text string

P: n/a
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.
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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.

Nov 13 '05 #2

P: n/a
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
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.