Problem with an InStr Query

[ninsignares]

Hi,
I've been trying to make a query from a field with text info and i want to return all words from the left to one word which is in other table.

How can I get it?... I've been trying with a InStr Command but it doesn't work properly...

Thnx

PD: this is the sql command line that I have

SELECT Left([PBS].[Texto Aviso],(InStr((InStr((InStr([PBS].[Texto Aviso]," ")+1),[PBS].[Texto Aviso]," ")+1),[PBS].[Texto Aviso]," ")-1)) AS OPC
FROM PBS, modelo
WHERE (((PBS.[ID Clasificación])=264))
GROUP BY Left([PBS].[Texto Aviso],(InStr((InStr((InStr([PBS].[Texto Aviso]," ")+1),[PBS].[Texto Aviso]," ")+1),[PBS].[Texto Aviso]," ")-1));

on this, I return 3 words at the beggining of the field but it doesn't work properly because some records have the word that i need to be counted to, after the 3rd or 4th blankspace.

 

[raskew]

Hi -

In the future, would you please use the Code option (it's the # button), rather than italics. Makes it a lot easier to read:

SELECT
    Left([PBS].[Texto Aviso],(InStr((InStr((InStr([PBS].[Texto Aviso]," ")+1),[PBS].[Texto Aviso]," ")+1),[PBS].[Texto Aviso]," ")-1)) AS OPC
FROM
   PBS
  , modelo
WHERE
   (((PBS.[ID Clasificación])=264))
GROUP BY
   Left([PBS].[Texto Aviso],(InStr((InStr((InStr([PBS].[Texto Aviso]," ")+1),[PBS].[Texto Aviso]," ")+1),[PBS].[Texto Aviso]," ")-1));

i want to return all words from the left to one word which is in other table.

OK, what word is that? If you know it, a simple Instr() statement will find it -- no need for those multiple InStr().

FROM
PBS
, modelo

'modelo' appears nowhere else in your statement. What's that about?

A little more information would be very helpful in resolving your situation.

Best wishes -- Bob

 

[ninsignares]

Bob,
the first time i did it, i didn't have a MODELO table... but when i identify the problem... i created it.

in the MODELO table i have dates... years to be exact... it beggins at 1930 until 2008.

in the "Texto Aviso" field I have too many words that I don't need to be filtered in my query... but this year appears at 3rd, 4th even 5th word from the beggining of the field. And the words 1, 2, 3... until the year are the query that i need.

 

[raskew]

Sorry for the delayed response.

The following function (I've placed a copy in the Code Repository) returns
the position of the nth occurence of a space/character in a string.

Copy/paste to a standard module then incorporate as shown in the examples.

Public Function fFindNthOccur(ByVal pStr As String, _
                                    pFind As String, _
                                    pNth As Integer) As Integer
'------------------------------------------------------------------
' Purpose:   Return location of nth occurence of item in a string.
' Coded by:  raskew
' Arguments: pStr: The string to be searched.
'            pFind: The item to search for.
'            pNth:  The occurence of the item in string.
' Input:     From the debug (immediate) window:
'            x = "The quick brown fox jumped over the lazy dog"
'            1) ? fFindNthOccur(x, " ", 3)
'            2) ? left(x, fFindNthOccur(x, " ", 3))
'            3) ? mid(x, fFindNthOccur(x, " ", 3)+1)
' Output:    1) 16
'            2) The quick brown
'            3) fox jumped over the lazy dog
'------------------------------------------------------------------
Dim strHold As String
Dim strFind As String
Dim intHold As Integer
Dim intSay  As Integer
Dim intKeep As Integer
Dim n       As Integer

   strHold = pStr
   strFind = pFind
   intHold = pNth
   intKeep = 0
   n = 0

   Do While n < intHold
      If InStr(strHold, strFind) = 0 Then
         fFindNthOccur = 0
         Exit Do
      Else
         intSay = InStr(1, strHold, strFind)
         intKeep = intKeep + intSay
         n = n + 1
         strHold = Mid(strHold, intSay + Len(strFind))
         fFindNthOccur = intKeep
      End If
   Loop

End Function

HTH - Bob

 

[ninsignares]

Bob, I have a big big problem... the year (item to search) doesn't have a fixed position in the "texto aviso" field. So i can't type in the function an integer that i don't know!

If I wrong, then explain to me how i have to do when i use the function.

Thnx!

 

[raskew]

Hi -

You could copy this to a standard module.

Public Function FindYear(ByVal pstr As String, pInt As Integer) As String
Dim strHold As String
Dim strKeep As String
Dim n As Integer

strKeep = pstr
For n = 1 To pInt
  strHold = Mid(strKeep, fFindNthOccur(strKeep, " ", n) + 1)
  If IsNumeric(Left(strHold, 4)) Then
     FindYear = Left(strKeep, fFindNthOccur(strKeep, " ", n + 1) - 1)
     Exit Function
  End If
Next n
FindYear = "Year not found."

End Function

Then test it from the immediate window like this.
Note that pInt is the max number of spaces you want to test.

x = "The quick brown fox 1980 jumped over the lazy dog."
? findyear(x, 6)
The quick brown fox 1980

Is that what you're looking for?

Bob