How to display SQL query results

[jailin]

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 As String

If txtSearch3.Value Like "[A-Z]" Then SQL = "SELECT * FROM tblcustdet WHERE customername LIKE " & "*" & strSEARCH & "*" Or Street Like " & " * " & strSEARCH & " * " OR Suburb LIKE " & "*" & strSEARCH & "*"

If txtSearch3.Value Like "[0-9]" Then SQL = "SELECT * FROM tblcustdet WHERE 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 result into a text box table like this one on imgur . com / rfehRRJ

Maybe I over complicating things I honestly have no idea.

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?

[jonh]

Maybe I over complicating things I honestly have no idea.

I think so.

Bind the recordset to a form and filter it (then the user can navigate records and edit them using the built in tools.)


me.filter = strFilter
me.filteron=true

where strFilter is the WHERE string.

Your code seems to be checking if a field is numeric or text. Text fields require quotes around values.


where mytextfield like '*somevalue*'

If a value is numeric but the field datatype is text your code will error.


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?

Since you're using OR, only 1 field needs to match. If you used AND they would all have to match.