Search Query sql code 1

[nico7]

i have a search form that has a vba code completed. however this search form is based on a search query and in order for the search form to filter my master table, i have to get the sql code in my search query correct. i am having problems with constructing the sql code.
the following is my vba code:

Private Sub cmdfindrecords_Click()
   Dim strFilter As String, strOldFilter As String
   

   strFilter = ""
    If Not IsNull(Me.productnum) Then _
       strFilter = strFilter & " AND " & _
          "([Part Number]=" & Me.productnum & ")"
   
   If Not IsNull(Me.Defect) Then _
       strFilter = strFilter & " AND " & "('" & Me.Defect & "' In([Defect Code 1]," & "[Defect Code 2]," & "[Defect Code 3]))"
  
   If Not IsNull(Me.associateid) Then _
       strFilter = strFilter & " AND " & "('" & Me.associateid & "' In([Associate]," & "[Associate 2]))"


  
  If Not IsNull(Me.date1) Then
       strFilter = strFilter & " AND ([CreationDate]"
  If Not IsNull(Me.date2) Then
       strFilter = strFilter & _
                " Between " & Format(Me.date1, "\#m/d/yyyy\#") & _
                " And " & Format(Me.date2, "\#m/d/yyyy\#") & ")"
  Else
       strFilter = strFilter & _
                "=" & Format(Me.date1, "\#m/d/yyyy\#") & ")"
  End If
  End If
  
  If strFilter > "" Then strFilter = Mid(strFilter, 6)
  Debug.Print strFilter
      Me.Filter = strFilter
      Me.FilterOn = (strFilter > "")
End Sub

and below i will provide my sql code(not working)for my search query.

SELECT [Master Table].ID, [Master Table].[Defect Code 1], [Master Table].CreationDate, [Master Table].[Defect Code 2], [Master Table].[Quantity Defective 2], [Master Table].[Part Number], [Master Table].[Defect Code 3], [Master Table].Associate, [Master Table].[Associate 2]
FROM [Master Table]
WHERE ((([Master Table].[Defect Code 1])=Forms!search!Defect1) And (([Master Table].CreationDate) Between Forms!Search!date1 And Forms!Search!date2) And (([Master Table].[Part Number])=Forms!Search![Product name])) Or ((([Master Table].[Defect Code 2])=Forms!Search!Defect1));

this sql code seems to be all wrong.

i would appreciate any help on this. thanks

 

[dhookom]

I prefer to write the code like:

Private Sub cmdfindrecords_Click()
  Dim strFilter As String, strOldFilter As String
  strFilter = "1=1 "
  If Not IsNull(Me.productnum) Then
    strFilter = strFilter & " AND " & _
       "[Part Number]=" & Me.productnum & " "
  End If

  If Not IsNull(Me.Defect) Then
    strFilter = strFilter & " AND '" & Me.Defect & "' In([Defect Code 1],[Defect Code 2],[Defect Code 3]) "
  End If
  
  If Not IsNull(Me.associateid) Then
    strFilter = strFilter & " AND '" & Me.associateid & "' In([Associate],[Associate 2])"
  End If
 
  If Not IsNull(Me.date1) Then
    strFilter = strFilter & " AND [CreationDate] >=" & Format(Me.date1, "\#m/d/yyyy\#")
  End If

  If Not IsNull(Me.date2) Then
     strFilter = strFilter " AND [CreationDate] <=" & Format(Me.date2, "\#m/d/yyyy\#")
  End If
  
  Debug.Print strFilter
  Me.Filter = strFilter
  Me.FilterOn = True
End Sub

What do you see in the debug window when running this? Have you tried setting a breakpoint and stepping through the code?

Duane
Hook'D on Access
MS Access MVP

 

[nico7]

i dont have any problems with my vba code, i just put it there for reference. im 100% sure that my vba code is correct.
i just need some help to form my sql code for my search query, that this search form is based on

 

[dhookom]

I'm not sure what you want. Your code filters the form so why do you think you need the search query to also filter the form?

Which method for filtering the form do you want to use?

Duane
Hook'D on Access
MS Access MVP

 

[nico7]

i want to use my search form to filter my data. however i was having problems earlier with my search form, it kept asking me to enter parameter values for the associates, and wasnt sure why, so i looked into this search query and i realized that the associates were not included, now after i added all the search criteria. my form doesnt return any data, and doesnt give me any errors. but it seems like my search form is dependent on that search query. i even tried to delete the query, and the search form doesnt open up after. so i thought if i had a correct sql code for this query my search form would function correctly.

 

[dhookom]

If you want to use your code to filter your data, set the form's record source to:

SELECT [Master Table].ID, [Master Table].[Defect Code 1], [Master Table].CreationDate, [Master Table].[Defect Code 2], [Master Table].[Quantity Defective 2], [Master Table].[Part Number], [Master Table].[Defect Code 3], [Master Table].Associate, [Master Table].[Associate 2]
FROM [Master Table]

Then use the code to create and apply the filter. You might want to save the form with a filter that doesn't return any records.

Duane
Hook'D on Access
MS Access MVP

 

[nico7]

where do i find the form's record source?

 

[dhookom]

In the form's Record Source property.

Duane
Hook'D on Access
MS Access MVP

 

[nico7]

can i link my form with my master table without any code?
i went into the forms record source and choose all the fields in my master table. and tried to search. it looks like the form is doing the search and i can see on the bottom of my form it says how many rows the form matched. but i cant see the data, and nothing is opening up.
how can i view the filtered data

 

[dhookom]

Your form should be a continuous form with the controls such as date1 and date2 in the form header section along with cmdfindrecords.

Duane
Hook'D on Access
MS Access MVP

 

[nico7]

i put the date range and the search button in the header of the form.
i tried searching and i am still not getting anything to pop-up and show me the filtered data. however, it seems like the form is filtering my master table, just not showing the results

 

[dhookom]

I'm not sure what you mean by "pop-up" since the records should display in the current form.

Provide these properties of your form:
Record Source:
Filter:
Data Entry:

Duane
Hook'D on Access
MS Access MVP

 

[nico7]

Record Source: SELECT [Master Table].ID, [Master Table].CreationDate, [Master Table].[Part Number], [Master Table].[Defect Code 1], [Master Table].[Quantity Defective 1], [Master Table].[Defect Code 2], [Master Table].[Quantity Defective 2], [Master Table].[Quantity Defective 3], [Master Table].[Associate 2], [Master Table].Associate FROM [Master Table];

Filter: ([Part Number]=2) AND ('14 - Assembly - Missing Springs' In([Defect Code 1],[Defect Code 2],[Defect Code 3]))

when searching part number id 2, and defect 14

i wasnt sure where the data should be displayed, so i guessed a new window would open up(pop-up)

 

[dhookom]

I would expect you should have bound text boxes in the detail section of the form to display the results.

Duane
Hook'D on Access
MS Access MVP

 

[nico7]

i added bound text boxes for all the fields i am searching for, however it is only showing one line at a time, and i can scroll through the filtered data, but its only showing one line.
How can i get it to show the filtered data as in a spreadsheet view underneath the search.

 

[dhookom]

Put the text box labels in the form header section and arrange the text boxes in a row so they look like a spreadsheet.

This assumes you set your form for continuous as suggested earlier.

Duane
Hook'D on Access
MS Access MVP