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
> Keyword Search SQL Help - Where Statement, Access 2016    
 
   
sommersgj
post May 13 2019, 12:05 PM
Post#1



Posts: 6
Joined: 29-November 18



Hello!

I am a beginner to VBA. I have built a database that allows my team to enter trouble tickets, or maintenance actions, on a video network we manage. I distributed the product back in January and we have accumulated over 900 records thus far. I have a SQL keyword search that lives in a form that displays the entire repository and will search the entire repository and return values found in specifically identified fields. This is the code I use for the keyword search (it has been trimmed down a bit for security purposes, but represents the fundamental function):

Option Compare Database
Option Explicit


Private Sub cmdKeywordSearch_Click()
Dim SQL As String

SQL = "SELECT tblTicketDetails.TicketID, tblTicketDetails.Network, tblTicketDetails.Equipment, tblTicketDetails.OpenedDate, tblTicketDetails.OpenedBy, tblTicketDetails.Status, tblTicketDetails.ResolvedBy, tblTicketDetails.Assist1, tblTicketDetails.Assist2, tblTicketDetails.Issue, tblTicketDetails.TaskType, tblTicketDetails.Solution, tblTicketDetails.Time " _
& "FROM tblTicketDetails " _
& "WHERE [Network] LIKE '*" & Me.txtKeywords & "*' " _
& " OR [Status] LIKE '*" & Me.txtKeywords & "*' " _
& " OR [TaskType] LIKE '*" & Me.txtKeywords & "*' " _
& " OR [Issue] LIKE '*" & Me.txtKeywords & "*' " _
& " OR [Solution] LIKE '*" & Me.txtKeywords & "*' " _
& "ORDER BY tblTicketDetails.OpenedDate "

Me.Form.RecordSource = SQL
DoCmd.SetOrderBy "TicketID DESC"
Me.Requery
Me.txtKeywords = ""

DoCmd.GoToControl "txtKeywords"
End Sub

I have an additional form that is essentially a carbon copy of the repository form, but its primary purpose is to display only open tickets. I would like to employ the same keyword search but will only display open tickets. The open tickets subform uses a query, qryOpenTickets, as its record source. The Open Tickets query is based on the same record source as the repository but the Status = 'Open.' When the Open Tickets form is initially called, only open tickets is displayed because it draws them from the associated query, so good to go. However, when the keyword search is launched the record source of the results comes from the SQL code, which returns both open and closed tickets.

Therein lies my problem-when I type in a keyword to be searched, the results include closed tickets as well. I would like to modify the SQL record source to be some thing like this (I will butcher this, thus my breakpoint):

Option Compare Database
Option Explicit


Private Sub cmdKeywordSearch_Click()
Dim SQL As String

SQL = "SELECT tblTicketDetails.TicketID, tblTicketDetails.Network, tblTicketDetails.Equipment, tblTicketDetails.OpenedDate, tblTicketDetails.OpenedBy, tblTicketDetails.Status, tblTicketDetails.ResolvedBy, tblTicketDetails.Assist1, tblTicketDetails.Assist2, tblTicketDetails.Issue, tblTicketDetails.TaskType, tblTicketDetails.Solution, tblTicketDetails.Time " _
& "FROM tblTicketDetails " _
& "WHERE [Status] = "Open" & '*" & Me.txtKeywords & "*' " _
& " OR [Network] LIKE '*" & Me.txtKeywords & "*' " _
& " OR [TaskType] LIKE '*" & Me.txtKeywords & "*' " _
& " OR [Issue] LIKE '*" & Me.txtKeywords & "*' " _
& " OR [Solution] LIKE '*" & Me.txtKeywords & "*' " _
& "ORDER BY tblTicketDetails.OpenedDate "

Me.Form.RecordSource = SQL
DoCmd.SetOrderBy "TicketID DESC"
Me.Requery
Me.txtKeywords = ""

DoCmd.GoToControl "txtKeywords"

I do not understand SQL Where statements well enough to step through this nor can I find viable examples online. I hope I was clear enough. Bottom line is the Open Tickets form's keyword search should filter out all closed tickets and return the desired values only within the open tickets. Any help is greatly appreciated!

Joe
Go to the top of the page
 
theDBguy
post May 13 2019, 12:10 PM
Post#2


Access Wiki and Forums Moderator
Posts: 75,297
Joined: 19-June 07
From: SunnySandyEggo


Hi Joe,

Welcome to UtterAccess!
welcome2UA.gif

I think you could make your life easier if you replace the original SQL where you used the table's name with a new one where you would use the query's name. For example, rather than...
CODE
SQL = "SELECT tblTicketDetails.TicketID...FROM tblTicketDetails...
you would instead use...
CODE
SQL = "SELECT NameOfOpenTicketsOnlyQueryHere.TicketID...FROM NameOfOpenTicketsOnlyQueryHere...
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
 
LeeAnn
post May 13 2019, 12:25 PM
Post#3



Posts: 1,264
Joined: 2-February 00
From: Mississippi USA Central Time Zone


I see one issue you might be having, instead of the where clause (that is if I understand what your issue is):

WHERE [Status] = "Open" & '*" & Me.txtKeywords & "*' " _
& " OR [Network] LIKE '*" & Me.txtKeywords & "*' " _
& " OR [TaskType] LIKE '*" & Me.txtKeywords & "*' " _
& " OR [Issue] LIKE '*" & Me.txtKeywords & "*' " _
& " OR [Solution] LIKE '*" & Me.txtKeywords & "*' " _
& "ORDER BY tblTicketDetails.OpenedDate "

You may want to have :

WHERE [Status] = "Open" _
& " AND ( [Network] LIKE '*" & Me.txtKeywords & "*' " _
& " OR [TaskType] LIKE '*" & Me.txtKeywords & "*' " _
& " OR [Issue] LIKE '*" & Me.txtKeywords & "*' " _
& " OR [Solution] LIKE '*" & Me.txtKeywords & "*' ") _
& "ORDER BY tblTicketDetails.OpenedDate "

That will filter out any records with the status = open. Also I do not think that you would need & '*" & Me.txtKeywords & "*' " in the Status field unless that field as something after "Open" then include it.

--------------------
Lee Ann Davidson
Go to the top of the page
 
sommersgj
post May 14 2019, 07:38 AM
Post#4



Posts: 6
Joined: 29-November 18



Hello!

Thank you DBGuy. I got it figured out with a co-worker....then I popped in here to call off the dogs. We did exactly what you recommended, changed the 'SELECT tblTicketDetails... to 'qryOpenTickets...' and voila!

Thank you, Lee Ann. What you recommended was my first thought to solve the issue, but it did not work. The syntax is good because the debugger did not pick it up, but in practice I was still getting 'Closed' tickets populating the form.

However we are all good! Thank you for taking a look and replying! Have a great day!

Joe
This post has been edited by sommersgj: May 14 2019, 07:41 AM
Go to the top of the page
 
theDBguy
post May 14 2019, 09:51 AM
Post#5


Access Wiki and Forums Moderator
Posts: 75,297
Joined: 19-June 07
From: SunnySandyEggo


Hi Joe. Glad to hear you got it sorted out. LeeAnn and I were happy to assist. 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    22nd May 2019 - 01:48 AM