I'm no expert in Access, but I can't think of an easy way of doing wildcard
searches and case sensitive.
The only think I can think of is to write a custom function that takes the
search string and the 'To Be Searched' string, then parses the search string
looking for '?' and '*' then uses InStr to search for all the bits:
I have attached a 'First Attempt' at such a function. It is probably a bit
buggy, I haven't given it a thorough testing, but it should give you
somthing to start with. It should work just like InStr.
Cheers,
ChrisM.
Private Function WildCardSearch(startPos As Integer, searchString As String,
toBeSearchedString As String) As Integer
Dim wcPos As Integer
Dim wcStarPos As Integer
Dim partResult As Integer
Dim firstHalf As String
Dim secondHalf As String
Dim firstHalfPos As Integer
Dim secondHalfPos As Integer
wcStarPos = InStr(1, searchString, "*")
If wcStarPos > 0 Then
firstHalf = Left(searchString, wcStarPos - 1)
secondHalf = Mid(searchString, wcStarPos + 1)
firstHalfPos = WildCardSearch(1, firstHalf, toBeSearchedString)
If WildCardSearch(firstHalfPos, secondHalf, toBeSearchedString) > 0 Then
partResult = firstHalfPos
Else
partResult = 0
End If
Else
wcPos = InStr(1, searchString, "?")
If wcPos > 0 Then
firstHalf = Left(searchString, wcPos - 1)
secondHalf = Mid(searchString, wcPos + 1)
firstHalfPos = WildCardSearch(1, firstHalf, toBeSearchedString)
secondHalfPos = WildCardSearch(firstHalfPos, secondHalf,
toBeSearchedString)
If secondHalfPos = firstHalfPos + Len(firstHalf) + 1 Then
partResult = firstHalfPos
Else
partResult = 0
End If
Else
partResult = InStr(startPos, toBeSearchedString, searchString)
End If
End If
WildCardSearch = partResult
End Function
After all that, someone will probably come up with a single Access Function
that does exactly what you need...
C.
Mika Pitkänen said:
Hi!
Thanks this is closer but...
I still can't use wildcars * and ?
have you any ideas for that...
Wildcars is very important because i like to
find rows contains A?a.
Mika
ChrisM said:
Try InStr rather than StrComp:
WHERE (InStr(([uusi].[kentta1],[forms]![haku].[haku1],0) > 0)
will return rows where [uusi].[kentta1] contains the string [haku].[haku1] -
0= BinaryCompare ie case sensitive.
ChrisM
Mika Pitkänen said:
I have sql query like this.
SELECT uusi.Kentta1 AS Lauseke1, uusi.Kentta2 AS Lauseke2,
uusi.Kentta3
AS
Lauseke3, uusi.Kentta4 AS Lauseke4, uusi.Kentta5 AS Lauseke5, uusi.Kentta6
AS Lauseke6, uusi.Kentta8 AS Lauseke7, uusi.Kentta9 AS Lauseke8,
uusi.Kentta10 AS Lauseke9, uusi.Kentta11 AS Lauseke10 INTO uusaba
FROM uusi
WHERE (((StrComp([uusi].[kentta1],[forms]![haku].[haku1],0))=0));
and it works fine.
But i want to make query with wildcards.
How can I do query like this. Query must be case sensitive.
Strcomp doesn't work with wildcards.
For example Aa??? gives no results, but AaAaa
gives...
