Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    153
    Thanks
    3
    Thanked 1 Time in 1 Post
    Ive a field [Current or Ex] in a query, the field contains a C if theyre a current employee & X if theyre an ex employee.

    All I wanted was a pop up box [oldnew] that allowed someone to enter C for current employees, X for previous employees or A for all employees.

    Im quite happy for the default to be all, basically if the user puts in anything other than X or C they get all.

    In the criteria for field [Current or Ex] Ive tried:
    IIf([oldnew] Like "C","C",IIf([oldnew] Like "X","X","*"))
    IIf([oldnew] Like "C","C",IIf([oldnew] Like "X","X",chr(42)))
    IIf([oldnew] Like "C","C",IIf([oldnew] Like "X","X",chr(42)&))

    I even tried:

    IIf([oldnew]="C","C",IIf([oldnew]="X","X",IIf([oldnew]="A",Like "C" Or Like "X")))

    If the user enters C or X it works fine, but when they enter something else I get an error.

    I know it's going to be a "Duh" moment when I see the solution but, what am I doing wrong?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can't use Like within an IIf function (or rather, it won't do what you expect it to do).

    You could instruct the users to enter * if they want all employees. You can then use

    Like [oldnew]

    in the Criteria line.

    Alternatively, you could instruct the users to leave the parameter prompt blank if they want all employees. You can then use

    [oldnew] Or [oldnew] Is Null

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    153
    Thanks
    3
    Thanked 1 Time in 1 Post
    [quote name='HansV' post='798128' date='15-Oct-2009 14:15']You can't use Like within an IIf function (or rather, it won't do what you expect it to do).

    You could instruct the users to enter * if they want all employees. You can then use

    Like [oldnew]

    in the Criteria line.

    Alternatively, you could instruct the users to leave the parameter prompt blank if they want all employees. You can then use

    [oldnew] Or [oldnew] Is Null[/quote]


    Thank you, It wasn't a "Duh" moment, I didn't know you can't use Like within a IIF!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •