Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Feb 2003
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Status Field (2000)

    I'm making this way too complicated. Please help!

    I've created two fields in my query. One has the Manufacture's weight limit [MftWeight] and the other has the Status of what weight is currently booked [StatusWeight].

    My next field needs to say something like this: If the [StatusWeight] is less than the [MftWeight] then say the ride is Available but IF the [StatusWeight] is greater than the [MftWeight] then say the ride is Overbooked but IF the [StatusWeight] is within 100 lbs of the [MftWeight] then say that it's Booked because very few of our passengers weight less than 100.

    This is what I have put into separate fields thinking that I can combine them in the end:

    AvailableStatus_Weight: IIf([StatusWeight]<[MftWeight],"Available",Null)
    OverbookedStatus_Weight: IIf([StatusWeight]>[MftWeight],"Overbooked",Null)
    Note: I don't know how to write the Booked condition at all. Rookies! Don't you love us. <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

    This only goes to show that a little knowledge can . . . leave you dangling.
    Thank you for your support, you folks are wonderful.

  2. #2
    New Lounger
    Join Date
    Feb 2003
    Location
    Suburban Maryland
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Status Field (2000)

    Here's my solution where the underlying table is called "TableName"

    SELECT [MftWeight], [StatusWeight], ([MftWeight] - [StatusWeight]) AS [DeltaWeight], IIf([DeltaWeight] > 100, "Available", IIf([DeltaWeight] <= 100 AND [DeltaWeight] >= 0, "Booked", "Overbooked")) FROM TableName];

    I think the key here is get clear about the logic of the query before you start typing. Two of your three cases (Booked and Available) overlap (as you describe them). For instance, a ride may have a StatusWeight that is 50 pounds less than its MftWeight. That qualifies it as "Available" by your rule ("If the [StatusWeight] is less than the [MftWeight] then say the ride is Available") and also as "Booked" ("IF the [StatusWeight] is within 100 lbs of the [MftWeight] then say that it's Booked "). I decided that your intention is to say it's Overbooked if the difference between the weights was negative, Booked if that difference lay between 0 and 100, and Available if the difference was greater than 100. I introduced the new field "DeltaWeight" (where delta just means the difference between two quantities) both to simplify the nested IIf clause and to make the logic of the query more obvious.

    I hope this helps

    Sometimes I find it really helps if I write out a truth table or work with Venn diagrams a bit before I start coding. If you're unfamiliar with these tools they are easy to learn and will repay the effort many times.

  3. #3
    New Lounger
    Join Date
    Feb 2003
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Status Field (2000)

    Yikes! Does this go in the expression builder? I'm trying hard to learn all of this but I'm afraid that flew over my head.

    The [MftWeight] field and the [StatusWeight] field have been created in the query. I can't insert a table for the FROM <tableName> because there isn't one. Would I say FROM[Qry2_RideStatus] instead???

    Sorry but can you break it down more. If not, can you tell me how to write an Iif statement (one of the few statements that I know) for the Booked ride? I'm used to jumping through hoops but would prefer to do it the right way. This is what I tried and it wouldn't accept it:

    BookedStatus_Weight: IIf([StatusWeight]<100[MftWeight],"Booked",Null)

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Status Field (2000)

    Should this be

    BookedStatus_Weight: IIf([StatusWeight]<100 + [MftWeight],"Booked",Null)
    Regards
    John



  5. #5
    New Lounger
    Join Date
    Feb 2003
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Status Field (2000)

    It doesn't seem to want to take the <

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Status Field (2000)

    It's getting confused. Try this:

    BookedStatus_Weight: IIf([StatusWeight]< (100 + [MftWeight]),"Booked",Null)
    Charlotte

Posting Permissions

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