IIF statement problem for Query Criteria

[ray147]

I have the following IIF statement in the criteria of a query (design view). There are three options: (1) Confirmed (2) Failed (3) Pending. Options 1 and 2 are working fine however when option 3 is selected, no records are returned... not quite sure if I've written the IIF correctly for option 3.

IIf([Forms]![Pending_Bookings]![Confirmed]=-1,"Booking Confirmed") Or IIf([Forms]![Pending_Bookings]![Failed]=-1,"Failed Booking") Or IIf([Forms]![Pending_Bookings]![Pending]=-1,([Status_Codes].[StatusName])<>"Booking Confirmed"<>"Failed Booking")


any help appreciated..tnx

 

[Mile-O]

Am I right in suspecting that you have a table with three checkboxes rather than an option group? If so, consider an option group as you should only need one field for this and, as such, one object to refer to in your query.

 

[ray147]

SJ, you're right I've got three check boxes as I'd like to allow the user to allow more than one option, is this possible?

 

[StevenS]

What should happen when option 3 is TRUE?

 

[Mile-O]

I would delete your three fields, and create a new field called Status. Make it numeric. Then, on your form, you can remove the three checkboxes and replace them with an option group bound to Status. The appropriate value (Pending/Failed/Booked) will be saved to the field. When querying, you need only refer to the OptionGroup as criteria rather than writing lengthy and unnecessary expressions.

I'd like to allow the user to allow more than one option.

I'm not too sure what you mean here. As I see it, something has a status (of which there are three options) and that thing can be one of those three options. Their names suggest a consecutive run - Booked -> Pending -> Failed, etc. so I don't understand why you would want to allow the user to select two (i.e. Booked & Failed) surely this would be either/or rather than both?

 

[ray147]

my idea is to get both ... these three options refer to the status of bookings...some bookings are confirmed, some are pending (awaiting further info) and some has failed....thus i would like the user to have the option to list whichever bookings they'd like to query...

option 3 should list those bookings which are NOT failed and NOT booking confirmed


thanks guys

 

[Mile-O]

option 3 should list those bookings which are NOT failed and NOT booking confirmed

Which is what I've suggested to you.

 

[ray147]

i think i might not have explained myself correctly...

I've got the table 'Bookings' which already has a numeric field called Status. Now this field is linked to anoter table called 'Status Names' to link to the full names, in order to avoid writing the lengthy names with each booking record. There are about 10 different status codes.

Now what I'd like is to create this 'dream' query which will load any of the following bookings:

- confirmed bookings
- failed bookings
- pending bookings


The reason that I have three checkboxes on my form is to allow the user to specify which type of bookings need to be loaded....btw these are not all the statuses available in the system because there are more, but I would like to allow the user to only query these specific three types...Confirmed and Failed bookings are quite straightforward coz they are stored with their respective names..but pending bookings can vary and will be those which are "NOT despatched bookings and NOT failed bokings"..

hope this explains my situation better....and look forward to some feedback...