Sql query based on input box


Im just starting to get to grips with transferring our access db's into sql, and im creatign teh necearry stored procs and views for converting my queries and form basis to run from all going well and using access as a front end client for end users.

I have run into a stubmling block though when using input boxes in queries to set parametres. IN teh below example i am using a between function for dates specified by the user.

I dont want to run teh risk of pulling the whole table to access  and doing the processing client side as some of the pcs re pretty old and naff, but i am unsure how i could write this query as a view or stored procedure and using an input box to pass through teh parametres. im guessing i could use a pass through query into a stored proc but this is where my knowledge is struggling.

Please help!!
SELECT [TBL_delivery details].[Patient Name], [TBL_delivery details].[Ship to], [TBL_delivery details].Unit, [TBL_delivery details].Treatment, [TBL_delivery details].[Delivery date], Format([Delivery date],"dddd") AS [Day], [TBL_delivery details].[Special Reason], [TBL_delivery details].[Delivery not required], [TBL_delivery details].[Quantity Required]
FROM [TBL_delivery details]
WHERE ((([TBL_delivery details].[Delivery date]) Between [Please enter date from] And [Please enter date to]) AND (([TBL_delivery details].[Delivery not required])=False))
ORDER BY [TBL_delivery details].Unit, [TBL_delivery details].[Delivery date];

Open in new window

LVL 25
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

slam69Connect With a Mentor Author Commented:
sorry im still at a loss for how to generate the parametres in access.

If i write a passthrough query in access how do i get that to accept user input variables

if you can post some syntax for the access based query and then the stored proc for example would be a huge help ;o)
I guess you knw the answer. Stored Procedure.
All you need to do is create a sp that will take in the parameters & process the entire thing in the background & return the resultset.
View the Books Online for the syntax.

Bharat Butani.

All Courses

From novice to tech pro — start learning today.