Access Functions

[Bob Quintal]

=?Utf-8?B?QmVnaW5uZXIgQWNjZXNzIFVzZXI=?= <Beginner Access
(e-mail address removed)> wrote in

I am trying to find a function in Access which is similar to
"find" function in Excel. I have a field in a table which
contains a unique character and I want to find out the address
of the special character (location within the field).

instr()

 

[Guest]

I am trying to find a function in Access which is similar to "find" function
in Excel. I have a field in a table which contains a unique character and I
want to find out the address of the special character (location within the
field).

 

[fredg]

I am trying to find a function in Access which is similar to "find" function
in Excel. I have a field in a table which contains a unique character and I
want to find out the address of the special character (location within the
field).

What special character?
If your field contains "This is my string" and you wish to find the
first position of the character "m" within the field ...

Position = InStr([FieldName],"m")
Position = 9

Is that what you are looking for?

 

[Jeff Boyce]

Do you mean something like "where in this string is the first instance of
the letter 'g'"? (20th character in, right?)

If so, look at Access HELP for the exact syntax on using the InStr()
function.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

I am trying to find a function in Access which is similar to "find" function
in Excel. I have a field in a table which contains a unique character and I
want to find out the address of the special character (location within the
field).

What special character?
If your field contains "This is my string" and you wish to find the
first position of the character "m" within the field ...

Position = InStr([FieldName],"m")
Position = 9

Is that what you are looking for?

This was very useful. One additional question... If the field contains
"This is my string" and I want to find the position of the character "i" as a
second instance. ( answer should be 6). How do I use the function?

 

[George Nicholson]

To find the 2nd instance, you'd have to find the first instance and then
call Instr again, supplying a new starting position (just like you would
with FIND).

Below is a function that you can paste into a general code module in either
Access or Excel. One difference between it and Find is that it will return 0
if there is no specified instance rather than #VALUE if not found.

Of course, you did start this off by saying "unique"

Public Function FindInstance(strToFind As String, strFindIn As String,
iInstance As Integer) As Integer
Dim iPos As Integer
Dim i As Integer

iPos = 0
For i = 1 To iInstance
iPos = InStr(iPos + 1, strFindIn, strToFind)
Next i
FindInstance = iPos
End Function

FindInstance("i","This is my string",2) 'Returns 6


HTH,

I am trying to find a function in Access which is similar to "find"
function
in Excel. I have a field in a table which contains a unique character
and I
want to find out the address of the special character (location within
the
field).

What special character?
If your field contains "This is my string" and you wish to find the
first position of the character "m" within the field ...

Position = InStr([FieldName],"m")
Position = 9

Is that what you are looking for?

This was very useful. One additional question... If the field contains
"This is my string" and I want to find the position of the character "i"
as a
second instance. ( answer should be 6). How do I use the function?