Switch Function

[montrose]

I have four columns in a query: [OnRent], [DueBack], [OnHold], and [InShop]. They are all "Yes/No" fields, derived from other queries. What I want to happen is if [DueBack] is "Yes", then [OnRent] switches to "No". Then if [InShop] is Yes, [DueBack] switches to "NO". I've searched this forum and found some information about using the Switch Function. I'm unclear as to where to enter this in the query design grid, and exactly how to phrase this.


DueBack: Switch([OnRent]="No&quot Am I getting close? Any help greatly appreciated

 

[JerryDennison]

Are all of these fields calculated controls whose value is not actually being stored in the database? If so, then a simple IIF() statement is all you need:

DueBack: IIF([OnRent], "Yes", "No&quot

 

[montrose]

Thanks so much for your response. These values are not being stored. However, [OnRent] comes from another query where I used an IIf statemet (if the equipment is on rent (date filled in) then the result is "Yes". [DueBack] is obtained in a similar manner from another query (if marked "OffRent"(+/-3 days), then [DueBack} is "Yes" [OnHold] and [InShop}] are derived from input on user forms. All of this is put together in this final query.

In essence then these fields are all derived from other queries/forms. As I said, from looking through forum posts it seems like I need a Switch Function in this query design but I'm not sure where to put it or how to word it. Right now if the equipment has been shown to be coming back, and the user knows it's back and puts it in the shop, then it's showing up as STILL due back AND in the shop! That's why I think I need the Switch Function. Thanks again for any help.

 

[JerryDennison]

It sounds like your database structure may be off just a little. To define whether or not something is in the building you would use transactions to record when the object is checked out and when it is checked in. These should be two seperate transactions and can be in the same table. You would then simply find the last transaction type for the object and that is it's current status. In this way you can never have it both ways. You can even add a little bit of code that prevents the user from issuing something whose last status wasn't checked in(i.e. it's still checked out as far as the system is concerned) and vice-versa.

 

[montrose]

Thanks again. My structure is abolustely horrible(had not a clue about Access when I first designed this mess!), and I'm in the middle of form development for my revised structure. But in the meantime, I've got to make this work so the users can see on screen the "status" of their equipment. The separate dates, as you mentioned, are actually there, but trying to stop the users from entering data prematurely is just about impossible. So I can't base it totally on their data input, but need to make the screen 'look' like it's being miraculously handled in the background. Any other suggestions?!? Thanks.

 

[JerryDennison]

I can't really suggest more than I already did (it doesn't matter if the query is based on another query, etc.) Using the IIF() function will change the status of the field based on another field. I would have to see your exact structure and how you are using it to give you a better idea.

 

[montrose]

Again, your response is appreciated. You are absolutely right, the IIf statment does change the status of the field. Put in DueBack: IIf([Shop]="Yes","No&quot and where Shop had been Yes, DueBack changed to No. Hard to see the forest for the trees sometimes! As always, absoltuely wonderful assistance here at Tek-Tips. Thanks again, JerryDennison

 

[JerryDennison]

You're welcome.