How to display SQL query results

[jailiin]

I've been trying to build a search form in Access 2013 that takes the value of a single text box and searches it across a table.

What I would like it to do is search the table and if it finds a result, load the record into text boxes on the form for updating/exporting to another form/deleting.

After searching online I am using this search string:

 
dim strSEARCH as string
dim SQL ass string
 
SQL = "select * from tblcustdet where ((customername like ""*" & strSEARCH & "*"") or (Street like ""*" & strSEARCH & "*"") or (Suburb like ""*" & strSEARCH & "*"") or (PostCode like ""*" & strSEARCH & "*"") or (LandlineNumber like ""*" & strSEARCH & "*"") or (MobileNumber like ""*" & strSEARCH & "*"") or (EmailAddress like ""*" & strSEARCH & "*""))"
 

It doesn't scream any errors at me when I run the string but I don't know how to make it display any results

Id love it to display a message box saying there are X results then load the first results into this table (http://imgur.com/rfehRRJ)

Maybe I over complicating things I honestly have no idea.

Edit: Does an SQL search look for the whole record or part? Like if you have first and last name as 1 field and you only enter the persons first name, will it still return a result?

Thanks

[PhilOfWalton]

I'm not going to type the whole thing out, but you are missing a load of ampersands and too many double quotes.

Follow this pattern

WHERE ((customername like " & "*" & strSEARCH & "*")
 OR (....))
 

Phil

[jailiin]

Thanks for the tip Phil.

Edit: why does it always, always generate new errors when putting the "correct" code in.... Time to find why it wants the end of a statement..

[jforbes]

I think part of your question is on how to display the results when you have them. Typically, for this, people use a Filter. This might help you: Example Filtering on a Form.

[NeoPa]

Phil:
Follow this pattern

Or perhaps not exactly ;-) There appears to be an extraneous double-quote and ampersand (&) prior to the first asterisk string ("*"). Otherwise it should work. Jet/ACE SQL handles double-quotes (") as if they were proper SQL quotes (').

A generic template would be something like :

WHERE ([FieldName] Like '*XXX*')
   OR (...)

where XXX represents the search item you want to filter on.

When creating your SQL string in VBA it will help use to use the standard SQL quotes (') as that won't be confused with the VBA string quotes (").

PS. There's nothing wrong with extra parentheses around your filter items, but it isn't necessary for SQL. If it helps you follow or read it then go for it. I find it easier to work without.

[jailiin]

Thanks Neo

As I have been researching I found people creating sockets to their SQL DBs using declarations like:

Dim daMyName As New SqlDataAdapter
Dim dsMyName As New DataSet
Dim myConnection As New SqlConnection(myConnString)
Dim myCommand As New SqlCommand(mySelectQuery, myConnection)
 

but from what I can gather they are making external connections, IE connections to different servers. If everything is contained within 1 DB and not using an external SQL server, do I still need to envoke a SQL server connection to localhost?

[NeoPa]

No idea. That's a separate question - and a good illustration of why only one should be included in a thread.

I would guess that's ADODB related, and I use that only when I have to. Post a new question and someone with that type of experience can pick it up and you won't need to rely on me for something I can't help with.

In DAO, for which there are no current plans to deprecate, as long as you're using local or linked tables, there would be no need to set up any type of connection to a separate database or server in your code.

[jailiin]

Thanks a lot Neo. This is my first rodeo with SQL/DB programming.
Wish me luck

[NeoPa]

Good luck Jailiin!!