Why does this if statement not work?

[JamieWarren09]

Hi guys, I am using the current code below, now i'm trying to make it search the database for what is typed in text1.text and then when it finds the results it adds them to listview4.

For some reason when i search it comes back with 'No results found' (The else statement)
even though it is defo in the database.

Anyone have any ideas why this is happening?

thanks
jamie

 Dim db As Database
Dim rs As Recordset
Dim ds As Recordset
Dim WS As Workspace

Dim dbfile As Variant
Dim pwdstring As Variant

 Set WS = DBEngine.Workspaces(0)
    dbfile = ("\\server\Database\maindb.mdb")
pwdstring = "C123"
Set db = DBEngine.OpenDatabase(dbfile, False, False, ";PWD=" & pwdstring)

Set rs = db.OpenRecordset("SELECT * FROM `Leads` WHERE `Company Name` LIKE UCASE('%" & Text1.Text & "%') OR `Contact Name` LIKE UCASE('%" & Text1.Text & "%') OR `Phone Number` LIKE UCASE('%" & Text1.Text & "%') OR `Contact Number2` LIKE UCASE('%" & Text1.Text & "%') OR `Mobile` LIKE UCASE('%" & Text1.Text & "%')")



If Not (rs.EOF And rs.BOF) Then


Do While Not rs.EOF




ListView1.ListItems.Clear



Dim lvwItem8 As ListItem

         
  
      
   
        Set lvwItem8 = ListView1.ListItems.Add(, , rs.Fields.Item("Contact Name").value)
        lvwItem8.SubItems(1) = rs.Fields.Item("Company Name").value
       
lvwItem8.SubItems(2) = rs.Fields.Item("Status").value
       
lvwItem8.SubItems(3) = rs.Fields.Item("Phone Number").value


lvwItem8.SubItems(4) = rs.Fields.Item("leadid").value
             



lvwItem8.SubItems(5) = rs.Fields.Item("Calling From").value
             


            
    rs.MoveNext
Loop
rs.Close

  Else
MsgBox "No results found"
    
    End If

[TysonLPrice]

Take the SQL as it would be converted when it is passed to SQL and see if it works in Native SQL (Enterprise manager in my case). It would be easier to do that if you changed it to:

Dim strSQL As string
strSQL = "("SELECT * FROM `Leads` WHERE `Company Name` LIKE UCASE('%" & Text1.Text & "%') OR `Contact Name` LIKE UCASE('%" & Text1.Text & "%') OR `Phone Number` LIKE UCASE('%" & Text1.Text & "%') OR `Contact Number2` LIKE UCASE('%" & Text1.Text & "%') OR `Mobile` LIKE UCASE('%" & Text1.Text & "%')")"

Set rs = db.OpenRecordset(strSQL)

Then get the SQL being passed by going to the immeadiate window and typing:

?strSQL

Cut and paste that into native SQL and run it.

I'm sure you'll find that even though the data is there as you say your query is not retreiving it.

[Doogle]

Perhaps you also need to Uppercase the Column(s) data as well eg.

strSQL = "SELECT * FROM Leads WHERE " & _
            "UPPER( [Company Name])      LIKE '%" & UCase(Text1.Text) & "%' OR " & _
            "UPPER( [Contact Name])      LIKE '%" & UCase(Text1.Text) & "%' OR " & _
            "UPPER( [Phone Number])      LIKE '%" & UCase(Text1.Text) & "%' OR " & _
            "UPPER( [Contact Number2])   LIKE '%" & UCase(Text1.Text) & "%' OR " & _
            "       [Mobile]             LIKE '%" & Text1.Text & "%'"

[techgnome]

Ahh.... I think it's your wild cards... Access, uses the asterisk ... * for its wildcard... so it should be " LIKE '*" & ...

-tg

[Doogle]

Nice catch tg.

[techgnome]

It sticks out to me because I had the opposite problem many moons ago.... going from Access's * to SQL Server's % .... many... many moons ago...

-tg

[JamieWarren09]

Hi Doogle, I get the error Undefined function 'UPPER' in expression.

Any ideas why?

Thanks
Jamie

[JamieWarren09]

On the code



Set rs = db.OpenRecordset("SELECT * FROM Leads WHERE " & _
"UPPER( [Company Name]) LIKE '%" & UCase(Text1.Text) & "%' OR " & _
"UPPER( [Contact Name]) LIKE '%" & UCase(Text1.Text) & "%' OR " & _
"UPPER( [Phone Number]) LIKE '%" & UCase(Text1.Text) & "%' OR " & _
"UPPER( [Contact Number2]) LIKE '%" & UCase(Text1.Text) & "%' OR " & _
"[Mobile] LIKE '%" & Text1.Text & "%'")

[Doogle]

Because I got it wrong, in SQL Server UPPER is the Function to Upper Case something in Access it's UCase.
Change UPPER to UCase and it should be OK

[JamieWarren09]

For some reason, the error goes but i still get the else statement popup saying no results found...

Any ideas?

[Doogle]

Read Post #4 and change the % characters to * characters

[TysonLPrice]

Read post #2 and save yourself some time. Basically get it (the SQL) working in a native environment .where you can just run the query, and when that works plug it into your program.