Search Form Code

[f430]

hi, i have a master table with all my part information, and it has columns with part number, date, defects,...
and i am trying to write a code for my search form.
i have created a search form on Access, and it has 5 combo boxes for:
part number
defects
assembled by
and the other 2 combo boxes are for "from" and "to" date range. i have made the combo boxes for the date range to display a calendar when clicked and choose a date.

i have been trying to write a code that only requires one of these fields to be filled out and it can filter my master table.
i would appreciate any help on this
Thanks

[NeoPa]

Check out Example Filtering on a Form.

When you've had a play, let us know if there is anything you still feel unsure about. The more clear and specific you can be about what you're struggling with, the more quickly and easily we we be able to help you.

[f430]

Thanks NeoPa for the info, the article provides some helpful hints. however i do have some questions about my code.
1) in my search form i have a date range, which is made up of a combo box at each "from" and "to" when they are clicked a calendar pops up and a date can be picked. my question in regards to this is, how can i do a search of my master table of any entry in between these 2 dates? because i believe that the code u provided above was only meant to pick the exact date entered.

2)in my second combo box, im trying to filter my master table according to the defect entered into the search. however in my master table i have 3 columns for defects (labeled defect 1, 2, 3)since some parts have more than one defect. how can i make my code search all 3 columns for the defect entered into the search field?
would it be something like this:

If Me!Defect > "" Then _
     strFilter = strFilter & _
     " AND ([Defect Code 1, defect code 2, defect code 3]=" & _
     Me!Defect & ")"

thanks

[f430]

one more thing. i have tried manipulating your code to fit mine, but since im using a command button for my search,nothing really seemed to work out. after i tried to fix everything to on click and saved, exited and ran the code. but it didnt work

[NeoPa]

f430: 1) in my search form i have a date range, which is made up of a combo box at each "from" and "to" when they are clicked a calendar pops up and a date can be picked. my question in regards to this is, how can i do a search of my master table of any entry in between these 2 dates? because i believe that the code u provided above was only meant to pick the exact date entered.

There is a Between X And Y construct in a SQL WHERE clause or filter. EG :

...
WHERE [DateField] Between #1/1/2009# And #12/31/2009#

f430: 2)in my second combo box, im trying to filter my master table according to the defect entered into the search. however in my master table i have 3 columns for defects (labeled defect 1, 2, 3)since some parts have more than one defect. how can i make my code search all 3 columns for the defect entered into the search field?
would it be something like this:

If Me!Defect > "" Then _
strFilter = strFilter & _
" AND ([Defect Code 1, defect code 2, defect code 3]=" & _
Me!Defect & ")"

This is a good reason for not storing your data that way of course, but you could use (if exact matches were being looked for) :

strSQL = "SELECT ..." & _
         "WHERE '" & Me.DefectCode & "' In([defect code 1], _
                                           [defect code 2], _
                                           [defect code 3])

[NeoPa]

f430: one more thing. i have tried manipulating your code to fit mine, but since im using a command button for my search,nothing really seemed to work out. after i tried to fix everything to on click and saved, exited and ran the code. but it didnt work

I can't really comment or advise on where you went wrong unless you show me what you've done. I'm clever I know, but not that clever :D

[f430]

i keep getting an error when using this

strSQL = "SELECT ..." & _ "WHERE '" & Me.Defect & "'In([defect code 1], _ [defect code 2], _ [defect code 3])

once i write this statement and move to another line, it highlights [defect code 2] and says compile error:expected: end of statement.

also, im not sure what comes before the code you provided for the date range. and i am also assuming that this would look something like this:

WHERE [DateField] Between #fromcombobox# And #tocombobox#

i will post my complete code after i solve the current issues.

Thank you for your help.

[NeoPa]

As the underline character (_) indicates line continuation, it is not valid to use it as you have within code on a line.

As for your Between code :

WHERE [DateField] Between #fromcombobox# And #tocombobox#

This is not quite correct as dates should be formatted properly for use in SQL (See Literal DateTimes and Their Delimiters (#)). It is pretty close otherwise though. It may even be that you have explicit formatting for the two ComboBoxes which is compatible with SQL. In that case it would not be so wrong to use the code you've posted (Personally I'd always reformat it explicitly, but that's a choice if the code is already working).

[f430]

i have defined my from box as "date1" and to box as "date2"
i am not really sure what you mean by explicitly formatting these two boxes.
i was also wondering what kind of code i should use before the where statement.

Thanks

[f430]

this is the code that i currently have, but it is giving me a lot of problems. i also have not included the code for my date range, because i am not quite sure how to build it.

Private Sub cmdfindrecords_Click()
   Dim strFilter As String, strOldFilter As String
 
   strOldFilter = Me.Filter
 
If Me!productnum > "" Then _
     strFilter = strFilter & _
     " AND ([Part Number]=" & _
     Me!productnum & ")"
 
 
strFilter = "SELECT ..." & _
             "WHERE '" & Me.Defect & "' In([Defect Code 1], [Defect Code 2], _ [Defect Code 3])"
 
 
strFilter = "SELECT ..." & _
             "WHERE '" & Me.associateid & "' In([associate], [associate 2])"
 
 
If strFilter > "" Then strFilter = Mid(strFilter, 6)
If strFilter <> strOldFilter Then
      Me.Filter = strFilter
      Me.FilterOn = (strFilter > "")
End If
 
 
End Sub

when i search using the part number, nothing happens.
when i search using either associate number or defect, i get an error msg: u cant assign a value for this object, and when i go to debug it highlights this code:

Me.Filter = strFilter