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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
> Help With Search Procedure, Any Versions    
 
   
LilAnnCC1
post Aug 9 2019, 06:11 AM
Post#1



Posts: 873
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
 

Posts in this topic



Custom Search


RSSSearch   Top   Lo-Fi    5th July 2020 - 01:06 AM