Wildcard issue (1 Viewer)

[Petros]

I have posted the same question in an other forum also because of its "emergency"..


I am using below code in my new Access 2007 db. Backend on a SQL. Exactly same code runs in my previous DB with the same backend. However, the wildcard search is not executed correctly in the new DB..but works prefectly in the older version...

I have posted the same question in an other forum

[A] = nvarchar(50)
SearchX = unbound control

What am i doing wrong?


Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "FRUITS1"

If DCount("*", "dbo.FRUIT_BASKET", "[FRUITS] = '" & Me!SearchX & "*'") > 0 Then
DoCmd.OpenForm stDocName, , , "[FRUITS] like '" & Me!SearchX & "*'"
Else

MsgBox "Sorry, this FRUIT does not match any record in the DB"

End If

 

[nanscombe]

Dim stDocName As String
Dim stLinkCriteria As String

  stDocName = "FRUITS1"

  If DCount("*", "dbo.FRUIT_BASKET", "[FRUITS] = '" & Me!SearchX & "*'") > 0 Then
    DoCmd.OpenForm stDocName, , , "[FRUITS] like '" & Me!SearchX & "*'"
  Else
    MsgBox "Sorry, this FRUIT does not match any record in the DB"
  End If

Assuming your table is called dbo.FRUIT_BASKET wouldn't it be ...

Dim stDocName As String
Dim stLinkCriteria As String

 stDocName = "FRUITS1"

  If DCount("*", "dbo.FRUIT_BASKET", "[FRUITS] [COLOR="Red"]like[/COLOR] '" & Me!SearchX & "*'") > 0 Then
    DoCmd.OpenForm stDocName, , , "[FRUITS] like '" & Me!SearchX & "*'"
  Else
    MsgBox "Sorry, this FRUIT does not match any record in the DB"
  End If

This will only find records where the search string is at the beginning of field FRUITS. If you wanted to find any occurance of SearchX it would be

[FRUITS] like '*" & Me!SearchX & "*'"

 

[Petros]

Absolutly correct nanscombe...i am blind ..

I am much obliged..thanks!

 

[nanscombe]

It's good to have access (no pun intended) to a second pair of eyes.

How many times do we see what we expect to see rather than what is actually there?

 

[Petros]

I could not agree more!