Finding a value then if not found find the value -1 character and

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm not sure if this can be done in a query by creating a function first.
Here's an example: I need to find NICKJM in the "HIER" field. If there is
none, then it would look for NICKJ, and if there is no value it would look
for NICK. It will do this until it has matched. Is this poosible to do in a
query?

NICKJM
 
Alex said:
I'm not sure if this can be done in a query by creating a function first.
Here's an example: I need to find NICKJM in the "HIER" field. If there is
none, then it would look for NICKJ, and if there is no value it would look
for NICK. It will do this until it has matched. Is this poosible to do in a
query?

NICKJM
Click to expand...

Hi Alex (NICKJM?)

Seems like you could do something like that in a query, though offhand I
can't think of the perfect way. A VBA function would add flexibility and
eliminate some redundant coding, but this might do the trick.

Try UNIONing a series of SELECTs where each section uses fewer and fewer
characters of the string to match. You could identify which iteration
produces hits by adding a field to keep track of which section you are
in. This works for me:

SELECT
"Pass 0" AS PASS
, <other required fields>
FROM MyTable
WHERE
HIER = <SearchText>

UNION
SELECT
"Pass 1" AS PASS
, <other required fields>
FROM MyTable
WHERE
HIER = LEFT(<SearchText>,LEN(<SearchText>) - 1)

UNION
SELECT
"Pass 2" AS PASS
, <other required fields>
FROM MyTable
WHERE
HIER = LEFT(<SearchText>,LEN(<SearchText>) - 2)
;

Replace <SearchText> with your literal in 'quotes', or a field reference.

Experiment with UNION and UNION ALL to see if one works better than the
other for you.
 
I'm not sure if this can be done in a query by creating a function first.
Here's an example: I need to find NICKJM in the "HIER" field. If there is
none, then it would look for NICKJ, and if there is no value it would look
for NICK. It will do this until it has matched. Is this poosible to do in a
query?

NICKJM
Click to expand...

I assume you wish to return the actual text "NICK" if there is no
value "NICKJM" or "NICKJ" in the field, not the entire field.

Create a new function in a module first:

Function FindText(StrIn As String, LookFor As String) As String
Dim intOK As Integer
Dim strFound As String
strFound = LookFor
Dim intX As Integer
intX = 1

intOK = InStr(StrIn, LookFor)
Do While intOK = 0
strFound = Left(strFound, Len(strFound) - intX)
intOK = InStr(StrIn, strFound)
Loop

FindText = strFound

End Function
________________

Then in your query, add a new column.
TextFound:FindText([HIER],"NICKJM")
 
Fred

The function doesn't appear to be working. I keep getting undefined
Function, but I've created the module and saved it as you posted.

fredg said:
I'm not sure if this can be done in a query by creating a function first.
Here's an example: I need to find NICKJM in the "HIER" field. If there is
none, then it would look for NICKJ, and if there is no value it would look
for NICK. It will do this until it has matched. Is this poosible to do in a
query?

NICKJM
Click to expand...

I assume you wish to return the actual text "NICK" if there is no
value "NICKJM" or "NICKJ" in the field, not the entire field.

Create a new function in a module first:

Function FindText(StrIn As String, LookFor As String) As String
Dim intOK As Integer
Dim strFound As String
strFound = LookFor
Dim intX As Integer
intX = 1

intOK = InStr(StrIn, LookFor)
Do While intOK = 0
strFound = Left(strFound, Len(strFound) - intX)
intOK = InStr(StrIn, strFound)
Loop

FindText = strFound

End Function
________________

Then in your query, add a new column.
TextFound:FindText([HIER],"NICKJM")
Click to expand...
 
No. I need this to be automated. There will be a field called hier
containing different values, ex nick, mabn, etal, etc. I need to be able to
search another field, hier2 to see if these are located in there, perphaps
like [hier]*. But when the find that value in hier 2 will place the hier
value in the new created field.
 
actually Fred he code somewhat works, except when it searches for the value
it has to be in the beinning of the value of the other field. Also returning
Null values even when I put criteria "is not null" Its returning with results
such as: Searching for GON, returns BRGOD, RAXGON, and GOBAC. It should only
return with GOBAC.

fredg said:
I'm not sure if this can be done in a query by creating a function first.
Here's an example: I need to find NICKJM in the "HIER" field. If there is
none, then it would look for NICKJ, and if there is no value it would look
for NICK. It will do this until it has matched. Is this poosible to do in a
query?

NICKJM
Click to expand...

I assume you wish to return the actual text "NICK" if there is no
value "NICKJM" or "NICKJ" in the field, not the entire field.

Create a new function in a module first:

Function FindText(StrIn As String, LookFor As String) As String
Dim intOK As Integer
Dim strFound As String
strFound = LookFor
Dim intX As Integer
intX = 1

intOK = InStr(StrIn, LookFor)
Do While intOK = 0
strFound = Left(strFound, Len(strFound) - intX)
intOK = InStr(StrIn, strFound)
Loop

FindText = strFound

End Function
________________

Then in your query, add a new column.
TextFound:FindText([HIER],"NICKJM")
Click to expand...
 
Back
Top