IIf Statement Utilising Yes/No Field (1 Viewer)

[Pauline123]

Hi, I am working on a hired plant database and want to limit the combo box on the hired form to only list items not on hire.

I thought I could do this using the IssueDate and ReturnDate fields within a query with an IIf statement forcing the Yes/No field to choose Yes when there is a date in the IssueDate field and No when the ReturnDate field has a date entered.

Tried a few combinations with no success - am I barking up the wrong tree or does someone have a simple solution.

Hope you can help - most appreciated.

Pauline

 

[jdraw]

?? What is a hired plant database?
We have no idea where IssueDate and ReturnDate fit in your overall picture, or where the combo box fits in all of this. Perhaps you could provide us with a plain English statement about what you are trying to do.
And also a jpg of your tables and relationships.

 

[Pat Hartman]

The criteria should be
Where IssueDate Is Null OR ReturnDate Is Null;

 

[RainLover]

For an item to be in stock then there should be No issue date.

Without an issue date there cannot be a Return Date as the item has not gone out for it to be returned.

If an item has been issued and returned then there is no record.

All this depends on how you have designed your Forms etc.

I wrote a Hire DB years ago, if memory serves me right when an Item was returned I created a new record with the Customer being Warehouse, Repairs or Stock.

Anyway that is what I did. I hope this helps.

 

[Pauline123]

Hi all, many thanks for your comments and sorry I was not clear enough. The Hired Plant database is for equipment like ladders which are issued on a rental basis to customers. I want to limit the combo box "PlantIDcbo" to only list items not Hired Out. I would like to do this using the "IssueDate" (date hire commenced) and "ReturnDate" (date equipment returned) to trigger a Yes/No field I call "Hired" to trigger Yes for when on hire and No when in stock i.e Yes on IssueDate and No on Returndate. Hope this makes sense - have attached a sample database for you to look at. Again many thanks Pauline

 

[RainLover]

Thanks for the explanation.

So exactly where are you having the problem and what result are you getting.

BTW You can't have a default Value in the Foreign Key. Well you can but it is wrong. Just leave it blank. The Value will never be 0 so using 0 as the default is incorrect.

 

[Pauline123]

Hi, I cant figure out how to write it - my thinking was:

IIf(IsNull([ReturnDate],[Hired],True)

but i keep getting an invalid syntax error - tried a couple of variations to no avail.

I am relatively new to all this so would appreciate your help.
Pauline

 

[RainLover]

Hired is never Null. It is either True or False.

Try this but it is untested.

IIf(IsNull([ReturnDate]),IIf([Hired]=False,True))

 

[Pauline123]

Hi, thanks for the code, works great - now just need to figure how to limit the list in the combo box - any ideas ! Pauline

 

[RainLover]

Hi, thanks for the code, works great - now just need to figure how to limit the list in the combo box - any ideas ! Pauline

What do you want.

Do you want to show all records that are True or do you want False.

If either case I would create an extra field in the query with a Name followed by the Statement.

Newfield: IIf(IsNull([ReturnDate]),IIf([Hired]=False,True))

Then in the criteria place either True or False. By or I don't mean to use or. Just use True, either that use False.

Hope you understand.

 

[MSAccessRookie]

Hi, thanks for the code, works great - now just need to figure how to limit the list in the combo box - any ideas ! Pauline

As a rule, a Combo Box is controlled by a Query. I have to wonder if an IIf() is required to make the Query work. The Standard Query will look something like this:

SELECT {YourListofFields}
FROM {YourTable}
WHERE {YourConditionstoMeet}

In your case, IssueDate should be all that you should need to evaluate, as long as a returned Item has its date reset to Null:

You will need to supply the proper values for {YourListofFields}
You will need to supply the proper values for {YourTable}
The value for {YourConditionstoMeet} should be something like this:

IssueDate IS NULL

This is a simple solution and will most likely need to me adjusted to fit your needs, but it should be a good start.

-- Rookie

 

[RainLover]

Rookie

There is more to this than meets the eye. There will never be a record where IssueDate is Null.

If a Ladder is issued then there is an Issue Date. When it is returned there is a Return Date.
A record is only created when the Ladder is issued. Hence a Date. If it is not issued there is no record to reflect that.

Have a think about it and let me know what you think.

 

[MSAccessRookie]

I imagine that you are almost right about that. I suspect that a piece of Equipment that has not been rented yet would be the only one that fit that description.

Perhaps it should be

WHERE IssueDate IS NOT NULL and ReturnDate IS NULL

That combination would be valid as long as issuing a new item clears the ReturnDate Field.

WHERE IssueData

 

[RainLover]

Gets difficult.

What you proposed could return dozens of records as the Ladder has been hired out several times.

My suggestion is that the Ladder is always with someone. It is on hire or in stock. So when the Ladder is returned the return Date is completed then a new record is created with the Customer as Stock. You could have different things other than Stock. eg the other store, Lost, Stolen, with Repairer. Any searching would then be simple.

 

[Pauline123]

Hi, thanks for all your ideas and it has gotten me thinking - wondering if the default value for a plant item is a date, then run a query taking into account the stock date, issue date and return date to show a value, then set the criteria to filter out the -2 result in the "Current" field.

The - 2 represents an item On Hire and therefore should not be listed in the Combo Box.

Have attached the query i have been messing around with - what do you think

 

[RainLover]

Hi, thanks for all your ideas and it has gotten me thinking - wondering if the default value for a plant item is a date, then run a query taking into account the stock date, issue date and return date to show a value, then set the criteria to filter out the -2 result in the "Current" field.

The - 2 represents an item On Hire and therefore should not be listed in the Combo Box.

Have attached the query i have been messing around with - what do you think

Pauline,

I fear that you are rushing things just a little. You have had various suggestions, so I think you should work on a plan to keep track of all these transactions.

In other words you need to address your table structures, with this problem in mind. If you do your Tables correctly then the query will be a breeze.

I made a suggestion in Post #4 and again referred to it in post #14. You have not made a comment. Have you dismissed this idea. I know it works, as I said I have written a DB that does what you are trying to do.

There is no problem in doing it your way but the only thing you have so far is a query that will never work under the current structure. If there was an answer someone would have posted it by now.

 

[Pauline123]

Hi Rainlover, sorry have not dismissed your ideas just didn't know how to go about restructuring my tables but as it seems I am not going in the right direction maybe you could give me a few more pointers.

I have read your comment again and believe I would have to create a Status table denoting: Hired Out / Returned Stock / Damaged / Written Off.

I would then link this into the PlantOrderDetails table to store the selection that would be made via a combobox on the form used when the hire is issued out. The combobox would take the control source from the Status table but store the results in the PlantOrderDetails table.

I would then base a query on the "Status" selection to form the control source for the PlandID combo box and Requery to update the form so only Returned Plant would show in the PlantID combo box list selection.

If I have any of this wrong please could you give me some pointers - would hate to find out I had done this wrong again

Many thanks your help is most appreciated.

Pauline

 

[RainLover]

Hi Pauline.

It can be very frustrating trying to design something when you have a lot of experience if you have never done something like that before. Worse with little experience.

Your latest plan does look like a plan. Before you were simply fixed on just the one query.

A correction to your plan is to have in your Client Table records for Stock Lost Stolen Retired etc. This may not be the best solution but it is what I did. Each time an item is returned you need to automatically create a new record that defaults to Stock. Change it if you wish. The Date Out field becomes the date that the item was put into stock. Back to front I know but that is the way it must be.

I wrote my program when I was using Access 97. At the time, it was the biggest program I had written. There are many wrong things I did especially in the coding which I would not do today. I had great plans on selling this to hire companies. I quickly found that the profitable price made it too dear and the saleable price meant I would have to live on bread and water.

I will give you a copy if you wish as long as you realise that it is bigger than you might think as it has a lot of whistles and bells. Also, the code is not good but it does the trick. This I guess is a testament to Access where you can do wrong things as per standards but it still works. I have never received a complaint from any of the few customers that I sold this to.

So if you want a copy let me know and I will post it for you.

 

[Pauline123]

Hi Rainlover, a copy of the database would be good. My database is for internal use for a Stores department so it only needs to be basic.
My main problem is ensuring that items are not double-booked out when making the selection on the combobox list. If I can resolve this problem then I will have completed the database (phew and just in time - deadline is June).

Having a look at your database would give me some good pointers so would love to see it.

Many thanks again and I may be back to ask more questions - that's if you don't mind me pestering you a bit more lol.
Am enjoying creating this database but really do need to learn more

Many thanks again - Pauline

 

[RainLover]

I have attached.

I am sure I will be hearing from you.

This is in 2003. I have no idea what will happen in later versions.