Results 1 to 13 of 13
  1. #1
    Join Date
    Dec 2003
    Posts
    15

    Question Unanswered: Query -IIf Statement Problem

    Hi everyone

    I'm having problems with the criteria in a query.

    What I'm trying to do is to create a conditional criteria for a query with an IIf condition.

    There is a record that has a walue of Yes or No. This value is selected by the user in the Table. In the case of this DB it is Overnight Use Designated, Yes/No.

    In the query I have been trying to use the following Criteria:

    IIf ([Forms]![Forms1]![From Time] > TimeValue("20:00"),Yes,"")
    or
    IIf ([Forms]![Forms1]![From Time] < TimeValue("08:00"),Yes,"")
    or
    IIf ([Forms]![Forms1]![To Time] > TimeValue("20:00"),Yes,"")
    or
    IIf ([Forms]![Forms1]![To Time] < TimeValue("08:00"),Yes,"")

    But it dosn't seem to work.

    What I am Trying to achieve in simpler terms is

    If From_Time.Value > 8:00 PM then Criteria is "Yes" else No Criteria

    and so on.

    I keep getting an error saying that this contradicts the previous Criteria or something like that.

    If you could help it would be great

    Thanks

    Chris

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    try nesting the iifs:

    IIf ([Forms]![Forms1]![From Time] > TimeValue("20:00"),Yes,IIf ([Forms]![Forms1]![From Time] < TimeValue("08:00"),Yes,IIf ([Forms]![Forms1]![To Time] > TimeValue("20:00"),Yes,IIf ([Forms]![Forms1]![To Time] < TimeValue("08:00"),Yes,"")))
    )

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Dec 2003
    Posts
    15
    Izy

    You've been a great help, The last formula you gave me worked great it saved the records without a hitch. I'll tell you how this one will go.

    Thanks again, your 1 in a 1000000

    Chris

  4. #4
    Join Date
    Dec 2003
    Posts
    15

    Exclamation

    Hi Izy

    It didn't work, it gives me the records that have been selected as true, what I need is records not selected as true, I tried a few variations but it dosn't work, if I use true instead of false it gives all records, same as using not or <>. Any ideas why or how it is possible to fix this problem, and again thanks.

    Chris

  5. #5
    Join Date
    Dec 2003
    Posts
    15

    Exclamation

    Just keeping it alive

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    your original was
    IIf ([Forms]![Forms1]![From Time] > TimeValue("20:00"),Yes,"")
    or
    IIf ([Forms]![Forms1]![From Time] < TimeValue("08:00"),Yes,"")
    or
    IIf ([Forms]![Forms1]![To Time] > TimeValue("20:00"),Yes,"")
    or
    IIf ([Forms]![Forms1]![To Time] < TimeValue("08:00"),Yes,"")
    nesting the iifs took this to the immediate-if equivalent of:
    Code:
    If [Forms]![Forms1]![From Time] > TimeValue("20:00") then
     Yes
    Else
      If [Forms]![Forms1]![From Time] < TimeValue("08:00") then
        Yes
      Else
        If [Forms]![Forms1]![To Time] > TimeValue("20:00") then
          Yes
        Else
          If [Forms]![Forms1]![To Time] < TimeValue("08:00")
            Yes
          Else
            ""
          Endif
        Endif
      Endif
    Endif
    ...so this should have addressed the contradictory criteria issue.
    the nested version returns a single Yes or ""
    Yes when [FromTime] -OR- [To Time] is between 20:00...08:00

    i'm sitting on my sql server at the moment so i don't have Access to play with, but if i remember correctly, you can't set a criteria in the way you are attempting ( TRUE/"")

    i can't think of the correct way to do this at the moment, but there is an ugly way to cheat. given that access uses FALSE = 0 and TRUE = NOT FALSE = -1 (this cheat IS ugly because not all environments use 0/-1 so the symbolic TRUE/FALSE is more robust) you could try setting the criteria to:

    < 1 if you want all
    < 0 if you want true only

    -or-

    >-99 if you want all
    >-1 if you want false only

    you have to choose one of the two "pairs" since the ">" or "<" will be outside your iif. i.e. with this ugly workaround you can have criteria for TRUE/ALL or FALSE/ALL but not for TRUE/FALSE/ALL

    so, using the second pair as an example, your criteria:
    > IIf ([Forms]![Forms1]![From Time] > TimeValue("20:00"),-99,IIf ([Forms]![Forms1]![From Time] < TimeValue("08:00"),-99,IIf ([Forms]![Forms1]![To Time] > TimeValue("20:00"),-99,IIf ([Forms]![Forms1]![To Time] < TimeValue("08:00"),-99,-1))))


    or depending on the logic you are seeking

    > IIf ([Forms]![Forms1]![From Time] > TimeValue("20:00"),-1,IIf ([Forms]![Forms1]![From Time] < TimeValue("08:00"),-1,IIf ([Forms]![Forms1]![To Time] > TimeValue("20:00"),-1,IIf ([Forms]![Forms1]![To Time] < TimeValue("08:00"),-1,-99))))


    be warned - this is ugly!!

    anyhow - this will serve as another "keep alive". there is a much better way to do this but i can't remember it for the moment.

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Dec 2003
    Posts
    15

    Talking

    Thanks izy

    I will try this solution and see what the result is. If your interested I will send you the DB when it is finished, as you have put your part in it.

    Thanks again and see you soon.

  8. #8
    Join Date
    Dec 2003
    Posts
    15

    Question

    Hi Izy

    Tried using the Ugly method adn still won't work.

    I will try to explain the situation:

    I'm using a table and query to perform the job, In the table is a check box with the label "Overnight Allocated", when this is selected by the Admin, They are saying that this vehicle has been allocated for overnight use for every day of the year. This usually would be selected if the car is a take home car. Now what I am trying to achieve is that when the user books a vehicle, they will get a list in a combo box of all cars available on that date and time they have chosen. If the user chooses a time that begins before 8:00am or after 8:00pm the allocated vehicle shouldn't appear in that list. It's like it has already been booked.

    I have tried to use the following formula that you gave me and it works when wanting to eliminate the allocated vehicles but when you choose dates inside the 8am & 8pm block it dosn't show any vehicles. I know that this formula is correct, but I believe it is missing something. The "" dosn'y work either because it gives an error saying that the formula is too complex & Bla, Bla, Bla. Also anytime I put something in "" it says that I am reseting the previouse criteria, or something like that. So as you can see I am stuck. Well heres what I am using at the moment:

    IIf ([Forms]![Forms1]![From Time] > TimeValue("20:00"),No,IIf ([Forms]![Forms1]![From Time] < TimeValue("08:00"),No,IIf ([Forms]![Forms1]![To Time] > TimeValue("20:00"),No,IIf ([Forms]![Forms1]![To Time] < TimeValue("08:00"),No,""))).

    If the Time is outside the 8am & 8Pm block it shows unalocated vehicles.
    (Right)
    Inside the Block I get no Vehicles at all. (Not right)

    Thanks Izy

  9. #9
    Join Date
    Oct 2003
    Posts
    706

    Thumbs down

    I say in a very friendly manner that "if I were your manager on this project, I'd squash that approach like a bug." Because "a bug" it will soon be ... incomprehensible to everyone, scattered all over the project, and un-maintainable. Not to mention inefficient. (Forcing the query-engine to execute a Visual Basic function as part of selection-criteria torpedoes its chance to do anything but a full-table-scan.)

    I'd "order you back to the drawing board" to come up with a visually-equivalent way of accomplishing the desired task, using a visual basic Module that populates a temporary table.

    The code you're writing, and I say this politely and professionally but also as a stern warning, is "pasta in the making." Abandon All Hope, Ye Who Enter There... jm2cw.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    sundialsvcs is right, of course!

    anyway, why do this iif() stuff in the query?
    the proposed iif() return depends only on the values you have on your form so you can do the manipulation in the form rather than the query.

    Code:
    dim strSQL as string
    dim aBool as boolean
    If [From Time] > TimeValue("20:00") then
     aBool = TRUE
    Else
      If [From Time] < TimeValue("08:00") then
        aBool = TRUE
      Else
        If [To Time] > TimeValue("20:00") then
          aBool = TRUE
        Else
          If [To Time] < TimeValue("08:00")
            aBool = TRUE
          Else
            aBool = FALSE
          Endif
        Endif
      Endif
    Endif
    if aBool then
      strSQL = "SELECT this FROM whatever WHERE something = FALSE;"
    else
      strSQL = "SELECT this FROM whatever;"
    endif
    yourCombo.rowsource = strSQL
    yourCombo.requery
    (probably i got the some/all of the TRUE/FALSE the wrong way round, but you get the picture)

    izy
    currently using SS 2008R2

  11. #11
    Join Date
    Dec 2003
    Posts
    15
    Thanks Guys, I Owe you

    I'll give it a try and tell you how it goes


    Chris

    & Happy New Year

  12. #12
    Join Date
    Jan 2004
    Posts
    1
    Hi guys, I'm a bit confused about this line

    strSQL = "SELECT this FROM whatever WHERE something = FALSE;"
    else
    strSQL = "SELECT this FROM whatever;"

    Can you please explain.


    and Happy New Year

    Chris (Ossito212 is my work Nick)

  13. #13
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i can't guess what your real data looks like, so here's an assumption

    you have a table tblPoolCars containing fields:
    strLicencePlateNumber
    boolIsOutOvernight

    then:

    strSQL = "SELECT strLicencePlateNumber FROM tblPoolCars WHERE boolIsOutOvernight = FALSE;"
    ...will pull the licence plates for those cars that are not flagged as being out overnight,

    and

    strSQL = "SELECT strLicencePlateNumber FROM tblPoolCars;"
    ...will pull all cars.

    if you are populating a multi-column combo from
    tblPoolCars:
    strLicencePlateNumber
    boolIsOutOvernight
    intHorsepower
    intSeats
    boolDropTop

    then it's:

    strSQL = "SELECT strLicencePlateNumber, intHorsepower, intSeats, boolDropTop FROM
    ....and so on

    izy
    currently using SS 2008R2

Posting Permissions

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