AlphaPress Publishing
P.O. Box 5942 Albany, NY 12205
Tel: 518-250-3890
Email: alphapress@hotmail.com  


  
Click to buy this book from Amazon

Section Menu 

News

Pindaro is now working on a comprehensive book on SQL Server 2014.  For any comments you can email the author at alphapress@hotmail.com.

  

 

 

Define the order of the parameter prompts in a parameter query

To contact the author click here

This article is from the book "Access 2007 Pure SQL

To download the sample database click here


To download the sample database click here

 

Define the order of the parameter prompts

Retrieve order information using orderdate, state, and ordertotal as parameters

Discussion:

Using multiple parameters might present a problem if we enter them directly in the query design grid in Access 2007.  In this case, Access will present the parameters from left to right according to the lineup of the columns in the grid.  Some sources suggest entering parameters in the "Parameters" sheet in the "Show/Hide" group of the "design" tab to force order.  It is really not necessary to type our parameters a second time.  In our SQL statement, we just type our parameters in the order we would like them to appear. 

However, always think about the logic behind the parameter lineup.  In this example with the AND operator, the order will not make a difference in the result set since all three parameter values will apply concurrently on the fields for which they are specified.  However, there might be cases in which we want a certain date range to be specified first and then, use operators to search for data within that range using operators different from AND. 

In this example, the parameters are: NY, 1/1/2009, and $200. 

Code:

SELECT OrderID, OrderDate, City, State

FROM Qry_Parameters_Base

WHERE ((((State)=[Enter State:]) AND (orderdate)>[Enter Date Later Than:]) AND (totalorder)>[Enter Amount Bigger Than:])
 

Result: