UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
> Split Form 2nd Query Doesn't Work, Access 2016    
 
   
NewToAccess2019
post Nov 14 2019, 02:17 PM
Post#1



Posts: 37
Joined: 21-March 19



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)
Attached File  error2.png ( 34.88K )Number of downloads: 5
 
Go to the top of the page
 
 
Start new topic
Replies
theDBguy
post Nov 14 2019, 02:21 PM
Post#2


UA Moderator
Posts: 78,125
Joined: 19-June 07
From: SunnySandyEggo


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

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
NewToAccess2019
post Nov 14 2019, 02:31 PM
Post#3



Posts: 37
Joined: 21-March 19



I just attached the screenshot.

The Query in concern:
task1 = "select * from Rolls where (" & strCriteria1 & ") and (" & strCriteria2 & ") and ([total]>0) order by [date_in]"
Go to the top of the page
 
theDBguy
post Nov 14 2019, 02:36 PM
Post#4


UA Moderator
Posts: 78,125
Joined: 19-June 07
From: SunnySandyEggo


Hi. Thanks. Try changing your code to this:
CODE
...
strCriteria2 = "([description] = '" & Me.DESCRIPTION & "')"
...
Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
dashiellx2000
post Nov 14 2019, 02:37 PM
Post#5


UtterAccess VIP
Posts: 9,859
Joined: 11-March 05
From: Maryland


I would recommend you check out Dynamic Query Examples

hth

--------------------
William
“We're run by the Pentagon, we're run by Madison Avenue, we're run by television, and as long as we accept those things and don't revolt we'll have to go along with the stream to the eventual avalanche"
Go to the top of the page
 
NewToAccess2019
post Nov 14 2019, 02:44 PM
Post#6



Posts: 37
Joined: 21-March 19



@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.
Go to the top of the page
 
theDBguy
post Nov 14 2019, 03:22 PM
Post#7


UA Moderator
Posts: 78,125
Joined: 19-June 07
From: SunnySandyEggo


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

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 

Posts in this topic



Custom Search


RSSSearch   Top   Lo-Fi    31st May 2020 - 02:34 PM