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

Welcome to UtterAccess! Please ( Login   or   Register )

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



Posts: 33
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
 
theDBguy
post Nov 14 2019, 02:21 PM
Post#2


UA Moderator
Posts: 76,906
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: 33
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
 
GroverParkGeorge
post Nov 14 2019, 02:35 PM
Post#4


UA Admin
Posts: 36,207
Joined: 20-June 02
From: Newcastle, WA


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.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
theDBguy
post Nov 14 2019, 02:36 PM
Post#5


UA Moderator
Posts: 76,906
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#6


UtterAccess VIP
Posts: 9,839
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#7



Posts: 33
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
 
NewToAccess2019
post Nov 14 2019, 02:56 PM
Post#8



Posts: 33
Joined: 21-March 19



Thanks GroverParkGeorge for the explanation, it was very helpful.
This post has been edited by NewToAccess2019: Nov 14 2019, 02:57 PM
Go to the top of the page
 
theDBguy
post Nov 14 2019, 03:22 PM
Post#9


UA Moderator
Posts: 76,906
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
 


Custom Search


RSSSearch   Top   Lo-Fi    15th December 2019 - 10:12 AM