SQL syntax question

Discussion in 'ASP General' started by Hugh Welford, Oct 11, 2004.

  1. Hugh Welford

    Hugh Welford Guest

    Hi - I am trying to include a conditional WHERE into an ACCESS query using
    IIF, with the false-part a wildcard.

    The expression SELECT details.patid, details.type FROM details WHERE
    (((details.type)=IIf(1=1,2,(details.type) Like "*"))); returns all records
    with type = 2, but

    SELECT details.patid, details.typeFROM detailsWHERE
    (((details.type)=IIf(1=2,2,(details.type) Like "*"))); returns NO records.

    However, the unconditional expression SELECT details.patid, details.type
    FROM details WHERE (((details.type) Like "*")); returns ALL records.

    Can anyone help me with this?

    Thanks Hugh
     
    Hugh Welford, Oct 11, 2004
    #1
    1. Advertising

  2. Hugh Welford wrote:
    > Hi - I am trying to include a conditional WHERE into an ACCESS query
    > using IIF, with the false-part a wildcard.
    >
    > The expression SELECT details.patid, details.type FROM details WHERE
    > (((details.type)=IIf(1=1,2,(details.type) Like "*"))); returns all
    > records with type = 2, but
    >
    > SELECT details.patid, details.typeFROM detailsWHERE
    > (((details.type)=IIf(1=2,2,(details.type) Like "*"))); returns NO
    > records.
    >
    > However, the unconditional expression SELECT details.patid,
    > details.type FROM details WHERE (((details.type) Like "*")); returns
    > ALL records.
    >
    > Can anyone help me with this?
    >
    > Thanks Hugh


    IIF returns a value. It does not allow you to specify a statement. Let''s
    look at your IIF statement:

    IIf(1=2,2,(details.type) Like "*")

    This will attempt to return "(details.type) Like "*"", but the fact that you
    failed to delimit that string , and the quotes contained in that string,
    should cause it to bomb. Open your database in Access. Press ctrl-g to bring
    up the debug window, and paste

    ?IIf(1=2,2,(details.type) Like "*")

    into the Immediate window. When you press Enter, you should get an error.

    This should work better:

    WHERE details.type = iif(1=2,2,details.type)

    Bob Barrows

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], Oct 11, 2004
    #2
    1. Advertising

  3. Hugh Welford

    Hugh Welford Guest

    Thanks Bob - You are a genius - works fine now

    Hugh


    "Bob Barrows [MVP]" <> wrote in message
    news:%...
    > Hugh Welford wrote:
    > > Hi - I am trying to include a conditional WHERE into an ACCESS query
    > > using IIF, with the false-part a wildcard.
    > >
    > > The expression SELECT details.patid, details.type FROM details WHERE
    > > (((details.type)=IIf(1=1,2,(details.type) Like "*"))); returns all
    > > records with type = 2, but
    > >
    > > SELECT details.patid, details.typeFROM detailsWHERE
    > > (((details.type)=IIf(1=2,2,(details.type) Like "*"))); returns NO
    > > records.
    > >
    > > However, the unconditional expression SELECT details.patid,
    > > details.type FROM details WHERE (((details.type) Like "*")); returns
    > > ALL records.
    > >
    > > Can anyone help me with this?
    > >
    > > Thanks Hugh

    >
    > IIF returns a value. It does not allow you to specify a statement. Let''s
    > look at your IIF statement:
    >
    > IIf(1=2,2,(details.type) Like "*")
    >
    > This will attempt to return "(details.type) Like "*"", but the fact that

    you
    > failed to delimit that string , and the quotes contained in that string,
    > should cause it to bomb. Open your database in Access. Press ctrl-g to

    bring
    > up the debug window, and paste
    >
    > ?IIf(1=2,2,(details.type) Like "*")
    >
    > into the Immediate window. When you press Enter, you should get an error.
    >
    > This should work better:
    >
    > WHERE details.type = iif(1=2,2,details.type)
    >
    > Bob Barrows
    >
    > --
    > Microsoft MVP - ASP/ASP.NET
    > Please reply to the newsgroup. This email account is my spam trap so I
    > don't check it very often. If you must reply off-line, then remove the
    > "NO SPAM"
    >
    >
     
    Hugh Welford, Oct 11, 2004
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. VB Programmer

    SQL syntax question

    VB Programmer, Feb 3, 2006, in forum: ASP .Net
    Replies:
    3
    Views:
    384
    Ken Cox - Microsoft MVP
    Feb 4, 2006
  2. ecoolone
    Replies:
    0
    Views:
    766
    ecoolone
    Jan 3, 2008
  3. gabriele renzi
    Replies:
    2
    Views:
    205
    gabriele renzi
    Dec 31, 2005
  4. Ken Bloom
    Replies:
    3
    Views:
    213
  5. Good Night Moon
    Replies:
    9
    Views:
    284
    Rick DeNatale
    Jul 25, 2007
Loading...

Share This Page