[saiyanslayer]

Hey there, long time reader first time poster!

(If you know of a great advanced tutorial on Access, please let me know. I know how to set up the basics, now I'd like to know how the program really runs)

I'm currently working on a form that allows users to search for equipment. The category of the equipment is slectable through a combo box, and the searched text is entered in a text field. The results are displayed in a list box.

What've I've tried is using a 'Find' button to enter this sql query into the RowSource of the list box:

Private Sub Equipment_Find_Click()

Me.Equipment_List.RowSource = "SELECT Equipment.Type, Equipment.Manufacturer, Equipment.Model FROM Equipment WHERE (((Equipment." & strQuote & Me.Equipment_Search_Category.Column(0) & strQuote & ")=" & strQuote & Me.Equipment_Find_Text & strQuote & "))ORDER BY Equipment.Type DESC;"

End Sub

The result i get is a pop up window with a text field. If i re-enter the text in that feild the info pops up nicely. What am I doing wrong, and how do i get rid of that popup?

[OBP]

The Pop up is usually in response to the Syntax not being quite correct but is acceptable to the Query that is produced, however it dosn't match your Form field.
It could be the way that strQuote is used with the & statement.
normally the 2 parts of the Where statement are Joine by word And rather than an &.
like this
"WHERE Date1 <= #" & Format(Me.Date, "mm/dd/yyyy") & "#" & _
"And Date2 > #" & Format(Me.Date, "mm/dd/yyyy") & "#"
ou can also use the ' instead of strquote

[saiyanslayer]

I made such changes but i got:

The record source '~sq_cSearch_Equipment~sqcEquipment_Search_Category' specified on this form or report does not exist.

I edited the line to:

Me.Equipment_List.RowSource = "SELECT Equipment.Type, Equipment.Device_Name, Equipment.Model_Number FROM Equipment WHERE (((Equipment." & "'" & Me.Equipment_Search_Category & "')='" & Me.Equipment_Text_Find & "'))ORDER BY Equipment.Type DESC;"

[OBP]

Can you post a zipped copy of the database?
There is another alternative and that is to create a query for the List box and use the Form Fields as the Criteria for the query, this may give you the correct syntax for your SQL statement.
Are you Filtering by One field or 2 Fields in the List Data?

[saiyanslayer]

unfortunately i can't send a zipped copy of the database There's a lot of sensitive info i imported to it to test this.

I'll try your suggestions on Monday, but I'm pretty sure there's no filtering being used. I'll try doing the query again with the wizard... last time in a different form it didn't work but I think I just needed to requery. I'll let you know how it goes

[OBP]

You can always take a copy and delete the records and then compact & repair, Zip it and then post it.
I don't need data to get the SQL working, I can just create a couple of dummy records.

[saiyanslayer]

nevermind! The error was due to a typo in another field. The form works beautifully now! The only issue I have with it is that you have to type the exact word for it to earch it correctly. Is there a short solution to this?

[OBP]

I prefer to use Combo boxes wherever possible, however for descriptions you could use the
Like "*" & [textfieldname] & "*" for pattern matching.

This version builds a separate SQL string but you should get the idea
sWHERE = "not isnull(ClinicID)"
If Not IsNull(Me.CLINIC) Then sWHERE = sWHERE & " AND CLINIC like '*' & '" & Me.CLINIC & "' " & " & '*' "

[saiyanslayer]

Well, I must be confusing the sql commands and the VBA syntax. I tried adding "*" to my query but it hasn't helped. Here's the code that works (with exact matchs):

Private Sub Equipment_Find_Click()

Me.Equipment_List.RowSource = "SELECT Equipment.Type, Equipment.Device_Name, Equipment.Model_Number, Equipment.Manufacturer FROM Equipment WHERE (((Equipment." & Me.Equipment_Search_Category & ")='" & Me.Equipment_Text_Find & "')) ORDER BY Equipment.Type DESC;"

End Sub

Equipment_Search_Category is the combo box
Equipment_Text_Find is the text box

I tried:
(((Equipment." & Me.Equipment_Search_Category & ")=' * " & Me.Equipment_Text_Find & " * '))...

(((Equipment." & Me.Equipment_Search_Category & ")='" & "*" & Me.Equipment_Text_Find & "*" & "'))...

Looking at it now, I think you added 'LIKE' to it as well, but I'm getting a bit confused. Is it possible to stuff 'LIKE' into a sql query?

[OBP]

You need the LIKE in the statement as well i.e.
Like "*" & [Fieldname] & "*"

Use it in a Query Criterai row and you will see what I mean.

[saiyanslayer]

Awesome. Got it working:

Me.Equipment_List.RowSource = "SELECT Equipment.EquipmentID, Equipment.Type, Equipment.Device_Name, Equipment.Model_Number, Equipment.Manufacturer FROM Equipment WHERE (((Equipment." & Me.Equipment_Search_Category & ") LIKE '" & "*" & Me.Equipment_Text_Find & "*" & "')) ORDER BY Equipment.Type DESC;"

So i just had to replace the '=' with 'LIKE' and use stars for wildcards.

(response is for those who had similar issues)

Thanks a bunch, your help was great! I'll start another thread when I'm ready to move on.