[X]My Assistant

[NewToAccess2019]

Hi All,

I have a split form which has two date textboxes and two search buttons.

The first search button filters everything using DateFrom to DateTo textbox entries.

I am trying to build a second search button on the same form.
The second search button is supposed to filter using the same date range and description chosen from the filter on the split form and in addition it should filter based on another column where records are greater than 0.

See the code below:

CODE

Option Compare Database

Private Sub Command301_Click()
' Search button
Call Search
End Sub
Sub Search()
Dim strCriteria, task As String

Me.Refresh
If IsNull(Me.BDateFrom) Or IsNull(Me.BDateTo) Then
   MsgBox "Please enter the date range", vbInformation, "Date Range Required"
   Me.BDateFrom.SetFocus
  

Else
   strCriteria = "([Date_In] >=#" & Me.BDateFrom & "# And [Date_In] <= #" & Me.BDateTo & "#)"
   task = "select * from Rolls where (" & strCriteria & ") order by [Date_In]"
   DoCmd.ApplyFilter task
  
  


End If


End Sub


Private Sub Command481_Click()

Call Search1
End Sub
Sub Search1()
Dim strCriteria1, strCrtiteria2, strCriteria3, task1 As String
Me.Refresh
If IsNull(Me.BDateFrom) Or IsNull(Me.BDateTo) Then
   MsgBox "Please enter the date range", vbInformation, "Date Range Required"
   Me.BDateFrom.SetFocus
  

Else
   strCriteria1 = "([Date_In] >=#" & Me.BDateFrom & "# And [Date_In] <= #" & Me.BDateTo & "#)"
   strCriteria2 = "([description] = " & Me.DESCRIPTION & ")"
   'strCriteria3 = "(([total]= " & Me.Total & ")>0)"
   'strCriteria3 = "(([in_stock]= " & Me.IN_STOCK & ")>([out_stock] = " & Me.OUT_STOCK & "))"
   task1 = "select * from Rolls where (" & strCriteria1 & ") and (" & strCriteria2 & ") and ([total]>0)) order by [date_in]"
   DoCmd.ApplyFilter task1
  
End If


End Sub

However the second search1 sub doesn't work. It is giving error
This post has been edited by NewToAccess2019: Nov 14 2019, 02:21 PM

Attached File(s)

 error2.png ( 34.88K )Number of downloads: 5

 

[theDBguy]

Hi. It might help if you could post a screenshot of the form as well. Just a thought...

[NewToAccess2019]

I just attached the screenshot.

The Query in concern:
task1 = "select * from Rolls where (" & strCriteria1 & ") and (" & strCriteria2 & ") and ([total]>0) order by [date_in]"

[GroverParkGeorge]

This part looks suspicious to me:

[description] = 72"BACKING PAPER FOR FOIL

That's a string and it needs to be delimited with the proper delimiter, which would be the quote mark "

However, your string ALSO has an embedded quote mark representing inches.

That means you have to use a bit more intricate syntax to get the query to run. I'd probably try it with the single quote, i.e, ' , around 72"BACKING PAPER FOR FOIL. That said, I also prefer to avoid storing such characters in text fields for precisely this reason.

In other words, I'd probably use 72 inch BACKING PAPER FOR FOIL as the description so as to avoid such conflicts with string delimiters. If you have other descriptions like that, all of them should be updated.

[theDBguy]

Hi. Thanks. Try changing your code to this:

CODE

...
strCriteria2 = "([description] = '" & Me.DESCRIPTION & "')"
...

Hope it helps...

[dashiellx2000]

I would recommend you check out Dynamic Query Examples

hth

[NewToAccess2019]

@theDBGuy

I feel embarrassed now, it is such a fundamental mistake.
Thank god for this community and all you guys.

Very much appreciate the quick responses.

Thanks again!

' " & Me.Description & " ' worked.

[NewToAccess2019]

Thanks GroverParkGeorge for the explanation, it was very helpful.
This post has been edited by NewToAccess2019: Nov 14 2019, 02:57 PM

[theDBguy]

Hi. Glad to hear you got it sorted out. Good luck with your project.