QUERYING ON PART OF A FIELD!

[jamesaarnold]

I am using access 97..I have a table with a field that has 2 pieces of information in a single field..

1. supplier name
2. engineer name

example.> basf-fred smith

I'm trying to query on the second part of the data only

example.. tell me all suppliers for fred smith

I know it would probably be easier to split the data into two seperate fields.., But the data is imported from another database that I do not control.So unfortunatly, I cannot do this without spending an enormus amount of time splitting the data into 2 seperate fields.

Any Help would be greatly appreciated!

James

 

[cuok]

---Posted by Dev Ashish---

--------------------------------------------------------------------------------------
Strings: Parsing character separated string into individual components
--------------------------------------------------------------------------------------

(Q) I have string which contains values separated by a comma/colon/semi colon/space. How can I extract each value from that string?

(A) You can use these two functions provided by Microsoft to retrieve each value. Note that the functions are written for comma separated values but can easily be modified to work with any other character. Use the Sub Test as an example

'******************* Code Start ****************
Function CountCSWords(ByVal s) As Integer
'Counts the words in a string that are separated by commas.
Dim WC As Integer, Pos As Integer
If VarType(s) <> 8 Or Len(s) = 0 Then
CountCSWords = 0
Exit Function
End If
WC = 1
Pos = InStr(s, &quot;,&quot
Do While Pos > 0
WC = WC + 1
Pos = InStr(Pos + 1, s, &quot;,&quot
Loop
CountCSWords = WC
End Function

Function GetCSWord(ByVal s, Indx As Integer)
'Returns the nth word in a specific field.
Dim WC As Integer, Count As Integer
Dim SPos As Integer, EPos As Integer

WC = CountCSWords(s)
If Indx < 1 Or Indx > WC Then
GetCSWord = Null
Exit Function
End If
Count = 1
SPos = 1
For Count = 2 To Indx
SPos = InStr(SPos, s, &quot;,&quot + 1
Next Count
EPos = InStr(SPos, s, &quot;,&quot - 1
If EPos <= 0 Then EPos = Len(s)
GetCSWord = Trim(Mid(s, SPos, EPos - SPos + 1))
End Function

Sub Test()

Dim strAString As String
Dim I As Integer
Dim intCnt As Integer

strAString = &quot;This,calls,the,two,functions,listed,above&quot;

'Find out how many comma separated words
'are present
intCnt = CountCSWords(strAString)


'Now call the other function to retrieve each one in turn
For I = 1 To intCnt
Debug.Print GetCSWord(strAString, I)
Next
End Sub
'******************* Code End ****************

good luck
CUOK

 

[rdagey]

I would use an SQL statement, with the LIKE operator.

It would go something like:
SELECT * FROM [tableName] WHERE [fieldName] LIKE 'fred smith'

That would create a query where fieldName has the value 'fred smith' in it.

For instance, SELECT * FROM [tableName] WHERE [fieldName] LIKE 'a'
woudl return all records where fieldName has the letter a in it. You can do some more research on SQL statements and the LIKE operator if you like.

Hope this helps.

--Allen