Re: If Statement to determine WHERE condition in SQL Query

On Thu, 12 May 2005 07:07:39 -0700, Rhonda Fischer wrote:

>Hello,
>
>The @parameters that I am receiving into my SQL query in SQL Serer 2000
>Reporting Services are based on user selection from a drop down combo box.
>If the user selects a name it will return an integer. An alternative to
>selecting a single name the user can select 'ALL' of which will return a -1
>or maybe '*' if allowed. I would like an if stmt to determine if a criteria
>be added to the query or not. Can I do this, is there anyway of achieving the
>same thing?
>
>SELECT Forename, Username, Description
>FROM Audit
>WHERE Date >= @Param_StartDate
>AND Date < @Param_EndDate
>if @Param_UserKey <> -1
> AND UserID =@Param_UserKey 'continue to add last line of SQL stmt

Hi Rhanda,

Try this one:

SELECT Forename, Username, Description
FROM Audit
WHERE Date >= @Param_StartDate
AND Date < @Param_EndDate
AND ( @Param_UserKey = -1
OR UserID = @Param_UserKey )


>There are two more @Parameters to test if the 'ALL' option is selected also
>not
>sure I can neatly handle this. @Param_RecordType and @Param_ModuleNo.

I'm not sure what you mean. If you don't see how to adapt the above
query to handle this case, then please post more specific information.
(Check out www.aspfaq.com/5006 for the best way to post questions).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
.

Relevant Pages

  • RE: Designing Query - Help Please ASAP
    ... Once I copied the before mentioned langauge in my SQL view, ... query, ... SELECT [tblProject Staffing Resources].ProjectID, [tblProject Staffing ... If I make a selection in the ProjectID box = then the subform will ...
    (microsoft.public.access.queries)
  • Re: Requery Combo Box
    ... The SQL was generated like you said, ... On the same criteria row for each column add the restrictions ... The user selects a name from the first combo box that has a query behind ... After the selection, ...
    (microsoft.public.access.queries)
  • Re: SQL statement result exported to Excel
    ... data in Excel format. ... TransferSpreadsheet exports a table or query, so you need a saved query to ... has created the SQL statement, you can assign it to the SQL property of the ... MsgBox ("You must make at least one selection to Export") ...
    (microsoft.public.access.formscoding)
  • Re: VB Access to Run SQL SELECT query
    ... statements in the SQL view of the query designer in MS Access? ... the form for a user to query a table based on the selected fields. ... TRUE if the checkbox is not selected or FALSE if the checkbox is ... have this "Any" selection, how do I write the code that would SELECT ...
    (comp.databases.ms-access)
  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)