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
> Help With Search Procedure, Any Versions    
 
   
LilAnnCC1
post Aug 9 2019, 06:11 AM
Post#1



Posts: 854
Joined: 31-May 04
From: Wisconsin, USA


Hello, all! I'm using Access 2016 as a front end and SQL Server 2017 as a back end.

I've created a search procedure listed below. It works as expected, but I need to add a date restriction to it. On the form where I am using this procedure, I have a check box called ckHistory. When the form loads, it restricts the data to the last 6 months. But when the users search, it searches all of the data and want to be able to search just the last 6 months unless the box is checked. I realize I need to pass a parameter for this, but I don't know where in my procedure that I can put it. I am restricting the data on a column called RTS (datetime).

Here is my procedure:

CODE
Alter PROCEDURE [dbo].[spSearchSales]
@SearchSOList nvarchar(max) = ''

AS
BEGIN

DECLARE @SearchSO TABLE
(
    WildCards nVARCHAR(255)
)
INSERT INTO @SearchSO ( WildCards ) SELECT * FROM string_split(@SearchSOList,',')

DECLARE @FoundAll Int = 0
SELECT @FoundAll = Count(*) FROM @SearchSO


Select SaleID, [Sales Order], OldNo, Account, [Customer PO], OBO, [Order Date], [Due Date], RTS, Project, Contract, CustomerID, OrderStatus, Job, StoreNo,
StoreID, Item, OrderQTY, OrderPrice, Total, PartID, StatusID, LineStatus
FROM vwSearchSales
--Count(*) as matches, @FoundAll AS TermCount
OUTER APPLY
(
SELECT WildCards FROM @SearchSO
) ST


WHERE vwSearchSales.[Sales Order] LIKE ('%' + St.WildCards + '%')
OR vwSearchSales.OldNo LIKE ('%' + St.WildCards + '%')
OR vwSearchSales.Account LIKE ('%' + St.WildCards + '%')
OR vwSearchSales.[Customer PO]  LIKE ('%' + St.WildCards + '%')
OR vwSearchSales.Item LIKE ('%' + St.WildCards + '%')
OR vwSearchSales.Job LIKE ('%' + St.WildCards + '%')
OR vwSearchSales.LineStatus LIKE ('%' + St.WildCards + '%')
OR vwSearchSales.OrderQTY LIKE ('%' + St.WildCards + '%')
OR vwSearchSales.OrderPrice LIKE ('%' + St.WildCards + '%')
OR vwSearchSales.[Order Date] LIKE ('%' + St.WildCards + '%')
OR vwSearchSales.[Due Date] LIKE ('%' + St.WildCards + '%')
OR vwSearchSales.RTS LIKE ('%' + St.WildCards + '%')
OR vwSearchSales.StoreNo LIKE ('%' + St.WildCards + '%')
OR vwSearchSales.Project LIKE ('%' + St.WildCards + '%')
OR vwSearchSales.Contract LIKE ('%' + St.WildCards + '%')

GROUP BY SaleID, [Sales Order], OldNo, Account, [Customer PO], OBO, [Order Date], [Due Date], RTS, Project, Contract, CustomerID, OrderStatus, Job, StoreNo,
StoreID, Item, OrderQTY, OrderPrice, Total, PartID, StatusID, LineStatus
HAVING COUNT(*) =  @FoundAll
ORDER BY SaleID, [Sales Order], OldNo, Account, [Customer PO], OBO, [Order Date], [Due Date], RTS, Project, Contract, CustomerID, OrderStatus, Job, StoreNo,
StoreID, Item, OrderQTY, OrderPrice, Total, PartID, StatusID, LineStatus  
END
GO


I tried adding a @DateRestricted parameter, but I couldn't get it to work, so I took it out. This procedure works again as is.

All help and suggestions are truly appreciated!


--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 
Akudey
post Aug 9 2019, 09:03 AM
Post#2



Posts: 37
Joined: 17-August 13
From: Ghana


Hi LilAnn, sorry for the late response.

I think parameter query could be of help, where Form is used to pass parameter (criteria) to a query to filter or manipulate the query resultset.
Check this old link from the code archives:
Using a form to control parameter queries
Using a generic query parameter selection form
This post has been edited by Akudey: Aug 9 2019, 09:04 AM
Go to the top of the page
 
LilAnnCC1
post Aug 9 2019, 09:10 AM
Post#3



Posts: 854
Joined: 31-May 04
From: Wisconsin, USA


Thank you, Akudey.

I am using a parameter pass through query for the procedure, but I need to pass the @DateRestricted to it as well. I just don't know how to write the procedure to restrict the RTS date to the last 6 months if the @DateRestricted =0, and if it's 1, then let all the records come through.

In other words, I want to search on just the last 6 months of data or I want to search on all the records.

Does this make sense?

How to I write the where statement to include the @DateRestricted parameter?

--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 
cheekybuddha
post Aug 9 2019, 09:43 AM
Post#4


UtterAccess Moderator
Posts: 11,915
Joined: 6-December 03
From: Telegraph Hill


A couple of potential options:
CODE
-- ...
Select SaleID, [Sales Order], OldNo, Account, [Customer PO], OBO, [Order Date], [Due Date], RTS, Project, Contract, CustomerID, OrderStatus, Job, StoreNo,
StoreID, Item, OrderQTY, OrderPrice, Total, PartID, StatusID, LineStatus
FROM (
  SELECT
    *
  FROM vwSearchSales
  WHERE RTS = @DateRestricted
) ss
--Count(*) as matches, @FoundAll AS TermCount
OUTER APPLY
(
SELECT WildCards FROM @SearchSO
) ST
WHERE SS.[Sales Order] LIKE ('%' + St.WildCards + '%')
OR SS.OldNo LIKE ('%' + St.WildCards + '%')
OR SS.Account LIKE ('%' + St.WildCards + '%')
OR SS.[Customer PO]  LIKE ('%' + St.WildCards + '%')
OR SS.Item LIKE ('%' + St.WildCards + '%')
OR SS.Job LIKE ('%' + St.WildCards + '%')
OR SS.LineStatus LIKE ('%' + St.WildCards + '%')
OR SS.OrderQTY LIKE ('%' + St.WildCards + '%')
OR SS.OrderPrice LIKE ('%' + St.WildCards + '%')
OR SS.[Order Date] LIKE ('%' + St.WildCards + '%')
OR SS.[Due Date] LIKE ('%' + St.WildCards + '%')
OR SS.RTS LIKE ('%' + St.WildCards + '%')
OR SS.StoreNo LIKE ('%' + St.WildCards + '%')
OR SS.Project LIKE ('%' + St.WildCards + '%')
OR SS.Contract LIKE ('%' + St.WildCards + '%')
-- ...

or:
CODE
-- ...
Select SaleID, [Sales Order], OldNo, Account, [Customer PO], OBO, [Order Date], [Due Date], RTS, Project, Contract, CustomerID, OrderStatus, Job, StoreNo,
StoreID, Item, OrderQTY, OrderPrice, Total, PartID, StatusID, LineStatus
FROM vwSearchSales
--Count(*) as matches, @FoundAll AS TermCount
OUTER APPLY
(
SELECT WildCards FROM @SearchSO
) ST
WHERE vwSearchSales.RTS = @DateRestricted
AND (vwSearchSales.[Sales Order] LIKE ('%' + St.WildCards + '%')
OR vwSearchSales.OldNo LIKE ('%' + St.WildCards + '%')
OR vwSearchSales.Account LIKE ('%' + St.WildCards + '%')
OR vwSearchSales.[Customer PO]  LIKE ('%' + St.WildCards + '%')
-- ...
)
GROUP BY SaleID, [Sales Order], OldNo, Account, [Customer PO], OBO, [Order Date], [Due Date], RTS, Project, Contract, CustomerID, OrderStatus, Job, StoreNo,
-- ...


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
LilAnnCC1
post Aug 9 2019, 09:48 AM
Post#5



Posts: 854
Joined: 31-May 04
From: Wisconsin, USA


Thank you, David!

Just to clarify. I am passing the @daterestricted as either a 1 or a 0.

So I need to declare another variable to mean that

RTS=DATEADD(month,-6, getdate())

correct?


--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 
MadPiet
post Aug 9 2019, 10:00 AM
Post#6



Posts: 3,367
Joined: 27-February 09



You could just branch the two statements

(@DateRestricted BIT)

IF @DateRestricted THEN
BEGIN
… WHERE SomeDate >= DATEADD(-6,Month,EventDate)
END
ELSE
BEGIN
… no date restriction
END
Go to the top of the page
 
LilAnnCC1
post Aug 9 2019, 10:12 AM
Post#7



Posts: 854
Joined: 31-May 04
From: Wisconsin, USA


Okay, we're close!

This is what I have, but I get an error at the OUTER APPLY

Here is my partial code:

CODE
Alter PROCEDURE [dbo].[spSearchSales]
@SearchSOList nvarchar(max) = ''
,@DateRestricted bit=0
AS
BEGIN

DECLARE @SearchSO TABLE
(
    WildCards nVARCHAR(255)
)
INSERT INTO @SearchSO ( WildCards ) SELECT * FROM string_split(@SearchSOList,',')

DECLARE @FoundAll Int = 0
SELECT @FoundAll = Count(*) FROM @SearchSO
  
  IF @DateRestricted=0
  BEGIN

        Select SaleID, [Sales Order], OldNo, Account, [Customer PO], OBO, [Order Date], [Due Date], RTS, Project, Contract, CustomerID, OrderStatus, Job, StoreNo,
        StoreID, Item, OrderQTY, OrderPrice, Total, PartID, StatusID, LineStatus
        FROM (SELECT * FROM vwSearchSales WHERE RTS=DATEADD(month,-6,getdate())) ss
    END
ELSE
    BEGIN
        Select SaleID, [Sales Order], OldNo, Account, [Customer PO], OBO, [Order Date], [Due Date], RTS, Project, Contract, CustomerID, OrderStatus, Job, StoreNo,
        StoreID, Item, OrderQTY, OrderPrice, Total, PartID, StatusID, LineStatus
        FROM (SELECT * FROM vwSearchSales) ss
    END
    

OUTER APPLY-----------------------------------[color="#FF0000"]-Red Line and it says incorrect syntax near the keyword OUTER as well as near the ST[/color]
(
SELECT WildCards FROM @SearchSO

) ST


WHERE ss.[Sales Order] LIKE ('%' + St.WildCards + '%')
OR ss.OldNo LIKE ('%' + St.WildCards + '%')
OR ss.Account LIKE ('%' + St.WildCards + '%')

--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 
Akudey
post Aug 9 2019, 10:12 AM
Post#8



Posts: 37
Joined: 17-August 13
From: Ghana


oh I see, then you really have to head towards using Filter.
You are on the right path, what need to do is to filter the recordset behind the Form. Your query is returning all the data which you don't want, so do this:
1. Filter the recordset to include only the six month
2. Now you have a sub recordset which include six month data to search again.


if you are using DAO Then this thread might be of help
Filter a DAO recordset in VBA?
Go to the top of the page
 
LilAnnCC1
post Aug 9 2019, 10:25 AM
Post#9



Posts: 854
Joined: 31-May 04
From: Wisconsin, USA


Thank you Akudey.

Yes the @DateRestricted is a filter on the form called ckHistory. When the Access form loads, it is only loaded the last six months of data--not all the records. But, if I search the records and send the pass through query back to the server--it returns all the records having the search criteria. I want it limited to just the last 6 months. So my procedure needs to be able to return data from the last six months meeting the search criteria OR return data for all records meeting the search criteria if the ckHistory=1.

The recordset of the form is based on a view vwSearchSales limited to the last six months. But the pass through query isn't filtering for just the last six months--so that's why I'm trying to change the search procedure.

--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 
cheekybuddha
post Aug 9 2019, 10:39 AM
Post#10


UtterAccess Moderator
Posts: 11,915
Joined: 6-December 03
From: Telegraph Hill


[NB. Edited...]

An alternative (untested) crude hack:
CODE
-- ...
Select SaleID, [Sales Order], OldNo, Account, [Customer PO], OBO, [Order Date], [Due Date], RTS, Project, Contract, CustomerID, OrderStatus, Job, StoreNo,
StoreID, Item, OrderQTY, OrderPrice, Total, PartID, StatusID, LineStatus
FROM (
  SELECT
    *
  FROM vwSearchSales
  WHERE RTS = DATEADD(month,-6, getdate())
     OR @DateRestricted = 0
) ss
--Count(*) as matches, @FoundAll AS TermCount
OUTER APPLY
(
SELECT WildCards FROM @SearchSO
) ST
WHERE SS.[Sales Order] LIKE ('%' + St.WildCards + '%')
OR SS.OldNo LIKE ('%' + St.WildCards + '%')
OR SS.Account LIKE ('%' + St.WildCards + '%')
OR SS.[Customer PO]  LIKE ('%' + St.WildCards + '%')
OR SS.Item LIKE ('%' + St.WildCards + '%')
OR SS.Job LIKE ('%' + St.WildCards + '%')
OR SS.LineStatus LIKE ('%' + St.WildCards + '%')
OR SS.OrderQTY LIKE ('%' + St.WildCards + '%')
OR SS.OrderPrice LIKE ('%' + St.WildCards + '%')
OR SS.[Order Date] LIKE ('%' + St.WildCards + '%')
OR SS.[Due Date] LIKE ('%' + St.WildCards + '%')
OR SS.RTS LIKE ('%' + St.WildCards + '%')
OR SS.StoreNo LIKE ('%' + St.WildCards + '%')
OR SS.Project LIKE ('%' + St.WildCards + '%')
OR SS.Contract LIKE ('%' + St.WildCards + '%')
-- ...


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
LilAnnCC1
post Aug 9 2019, 11:13 AM
Post#11



Posts: 854
Joined: 31-May 04
From: Wisconsin, USA


I tried your untested hack, David. It didn't work. It saved okay, but any criteria I put into the search form, brings 0 records.

So, in Access vba I can create a SQL string. I tried doing it in SQL server, but I don't know the correct syntax.

It boils down to filtering the records based on the RTS date. So if the @DateRestricted = 0 then the WHERE statement needs to include RTS=DATEADD(month,-6, getdate()) and if it =1, then I want all records to be searched.

I tried using a CASE WHEN @DateRestricted =0 on the WHERE statement, but I got the same results ZERO when actually doing a search.

I guess I'll just make 2 stored procedures based on the ckHistory check box and see if that will work.

Thank you all for your help! I appreciate your time!

--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 
MadPiet
post Aug 9 2019, 12:10 PM
Post#12



Posts: 3,367
Joined: 27-February 09



I wouldn't use dynamic SQL here. Just not necessary.

IF @DateRestricted
BEGIN
-- query with DateRestricted filter
END
ELSE
BEGIN
-- query without DateRestricted filter
END

Go to the top of the page
 
LilAnnCC1
post Aug 9 2019, 12:28 PM
Post#13



Posts: 854
Joined: 31-May 04
From: Wisconsin, USA


Thanks, MadPiet.

I did what you suggested earlier--see my last post with code and I got errors around the OUTER APPLY area and didn't know what I was doing wrong.


I couldn't get the data to filter on the date at all, so I wound up making 2 views and 2 stored procedures. One of each with the date filter and one of each without the date filter. It seems incredibly stupid to me that I can't figure out how to get the stored procedure to work the way I want it to, but the form works in the way that I wanted now and it's giving me the correct information every time, so I guess that's a bonus!

It's just that I have to do the same thing with Purchases, Work Orders, Receipts and Invoices. Oh well, copy and paste works wonders!

--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 
MadPiet
post Aug 9 2019, 01:24 PM
Post#14



Posts: 3,367
Joined: 27-February 09



Some days, the way I see writing T-SQL is "at least it works - I know it's terrible, but it gets the job done" that's not to say you can't turn around and fix it later. But one big thing to keep in mind is how often you run crummy T-SQL. If you run once stored procedure that's terrible once a month, that's one thing. If you run the same horrible code every second, that's quite another.
Go to the top of the page
 
LilAnnCC1
post Aug 9 2019, 02:56 PM
Post#15



Posts: 854
Joined: 31-May 04
From: Wisconsin, USA


Great news, all!

David's solution worked! THANK YOU, THANK YOU!

Off to delete my unwanted redundant views and store procedures!

Here is the finished code:

CODE
ALTER PROCEDURE [dbo].[spSearchSales]
@DateRestricted bit =0,
@SearchSOList nvarchar(max) = ''

AS
BEGIN

DECLARE @SearchSO TABLE
(
    WildCards nVARCHAR(255)
)
INSERT INTO @SearchSO ( WildCards ) SELECT * FROM string_split(@SearchSOList,',')

DECLARE @FoundAll Int = 0
SELECT @FoundAll = Count(*) FROM @SearchSO

    
            Select SaleID, [SalesOrder], OldNo, Account, [CustomerPO], OBO, [Order Date], [Due Date], RTS, Project, Contract, CustomerID, OrderStatus, Job, StoreNo,
            StoreID, Item, OrderQTY, OrderPrice, Total, PartID, StatusID, LineStatus
            FROM (SELECT * FROM vwSearchSales WHERE RTS>= Dateadd(Month, Datediff(Month, 0, DATEADD(m, -6, current_timestamp)), 0) or @DateRestricted=1) ss    

OUTER APPLY
(
SELECT WildCards FROM @SearchSO

) ST


WHERE ss.[SalesOrder] LIKE ('%' + St.WildCards + '%')
OR ss.OldNo LIKE ('%' + St.WildCards + '%')
OR ss.Account LIKE ('%' + St.WildCards + '%')
OR ss.[CustomerPO]  LIKE ('%' + St.WildCards + '%')
OR ss.Item LIKE ('%' + St.WildCards + '%')
OR ss.Job LIKE ('%' + St.WildCards + '%')
OR ss.LineStatus LIKE ('%' + St.WildCards + '%')
OR ss.OrderQTY LIKE ('%' + St.WildCards + '%')
OR ss.OrderPrice LIKE ('%' + St.WildCards + '%')
OR ss.[Order Date] LIKE ('%' + St.WildCards + '%')
OR ss.[Due Date] LIKE ('%' + St.WildCards + '%')
OR ss.RTS LIKE ('%' + St.WildCards + '%')
OR ss.StoreNo LIKE ('%' + St.WildCards + '%')
OR ss.Project LIKE ('%' + St.WildCards + '%')
OR ss.Contract LIKE ('%' + St.WildCards + '%')
--AND ss.RTS >= Dateadd(Month, Datediff(Month, 0, DATEADD(m, -6, current_timestamp)), 0)

GROUP BY SaleID, [SalesOrder], OldNo, Account, [CustomerPO], OBO, [Order Date], [Due Date], RTS, Project, Contract, CustomerID, OrderStatus, Job, StoreNo,
StoreID, Item, OrderQTY, OrderPrice, Total, PartID, StatusID, LineStatus
HAVING COUNT(*) =  @FoundAll
ORDER BY RTS DESC, [SalesOrder]
END
GO


Again, thank you all for sticking with me!

--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 
cheekybuddha
post Aug 10 2019, 09:04 AM
Post#16


UtterAccess Moderator
Posts: 11,915
Joined: 6-December 03
From: Telegraph Hill


Well done LilAnn!

I'm glad you got there and it worked for you, and that you took the time to work out the logic for yourself!

I got the @DateRestricted switch the wrong way round - being in a hurry and too lazy to double check what you had written earlier, I assumed that you would restrict the records when the variable = 1 and require to return all when the variable = 0 - doh! blush.gif

However, it's much more satisfying when you get it to work yourself!

thumbup.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    14th December 2019 - 06:03 AM