Thread: Status Field (2000)

1. Status Field (2000)

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. 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. 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. Re: Status Field (2000)

Should this be

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

5. Re: Status Field (2000)

It doesn't seem to want to take the <

6. Re: Status Field (2000)

It's getting confused. Try this:

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

Posting Permissions

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