CODEWITHSUNDEEP
sqlms-accessuser-inputiif
cixelsyd
cixelsyd

Reputation: 97

SQL WHERE in IIF with User Input

I'd like to combine separate queries that prompt for user input into a single query.

Q1 Prompts User to enter 9 digit ITEM (PN):

SELECT 
STUFF,
MORE_STUFF
FROM dbo_X
WHERE (RIGHT(dbo_X.ITEM, 9)=[PN:])
ORDER BY dbo_X.COMPONENT

Q2 Prompts User to enter some portion of ItemDesc (Description):

SELECT 
STUFF,
MORE_STUFF
FROM dbo_X
WHERE dbo_x.ItemDesc LIKE '*'&[Description:]&'*'
ORDER BY dbo_X.COMPONENT

My thinking is to use IIf to execute a single WHERE statement based upon user input:

SELECT 
STUFF,
MORE_STUFF
FROM dbo_X
IIf ([Description{D}PartNumber{P}:]='D',
    WHERE dbo_X.ItemDesc LIKE '*'&[Desc:]&'*',
    WHERE (RIGHT(dbo_X.ITEM, 9)=[PN:]))
ORDER BY dbo_X.COMPONENT

Not currently working and not sure if I have syntax issues or am attempting to use IIf/When improperly.

Upvotes: 0

Views: 133

Answers (2)

Parfait
Parfait

Reputation: 107687

Consider adjusting your workflow and use a dedicated user interface such as MS Access' forms and not rely on query parameter pop-ups which in best design is not the standard way of receiving values of named parameters.

  1. Create a form of three text boxes for Description(D), PartNumber(P), Desc:, and PIN: values which can be validated for character length conditionally linked (locked/not visible) by first box.

  2. Save both queries pointing to form controls:

    SELECT 
STUFF,
MORE_STUFF
FROM dbo_X
WHERE (RIGHT(dbo_X.ITEM, 9)=Forms!myform![PN:])
ORDER BY dbo_X.COMPONENT

SELECT 
STUFF,
MORE_STUFF
FROM dbo_X        
WHERE dbo_x.ItemDesc LIKE '*'& Forms!myform![Description:] &'*'
ORDER BY dbo_X.COMPONENT

  3. On same new form, place a command button to output queries and have that button via VBA or macro to call the corresponding query depending on Description(D), PartNumber(P). And with this approach, no pop-ups will occur as all parameters are satisfied on form.

    If Forms!myform![Description(D), PartNumber(P)] = 'D' Then
    DoCmd.OpenQuery "DescriptionQuery"
Else
    DoCmd.OpenQuery "PinQuery"
End If

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270523

iif() is an expression, not a clause. So, you cannot use it that way.

On the other hand, you can do:

SELECT STUFF, MORE_STUFF
FROM dbo_X
WHERE ([Description{D}PartNumber{P}:] = 'D' AND dbo_X.ItemDesc LIKE '*'&[Desc:]&'*') OR
      ([Description{D}PartNumber{P}:] <> 'D' AND RIGHT(dbo_X.ITEM, 9) = [PN:]))
ORDER BY dbo_X.COMPONENT;

Oh, and that didn't even require iif().

By the way, the expression is a wee bit more complicated if [Description{D}PartNumber{P}:] could have a NULL value (just use the nz() function).

Upvotes: 0

Related Questions