Unanswered: Calculated field on form: 'Iif' statements including dates
Could somebody please prompt me where to start on this?
My tour operator db controls incoming invoices. The invoices can come from up to 24 hotels, stored in a Hotels Table. The invoices reference specific travel agencies, of which there are 4, stored in Agency Table. Each agency contracts to fill a certain amount of rooms in a range of hotels for a specific date period. Invoices that relate to this period are called 'guarantee' invoices, invoices that relate outside this period are called 'allotment'.
I have a simple drop-down list which the user can choose 'guarantee' or 'allotment', but that assumes the user KNOWS which periods relate to which hotels, or can look them up somewhere else. What I would really like is for the form to calculate whether the invoice is a guarantee or allotment.
So I think I need an unbound text box on the form, with an Iif statement, something like:
Iif [Agency] = "StarTours", and [Hotel] = "Atrium", and dates fall on or between [Arrival_Date] and [Departure_Date] then write "guarantee", else "allotment".
Obviously this code isn't going to work, I'm trying to communicate clearly what is required (and I don't know how to write the code).
Any comments most gratefully received - many thanks.
I think I need a bit more help. I can put the data into a table but how can I get it back onto a form? The data is already in a table, why make another one? You can't do calculations in a table, can you?
Thanks StarTrekker, that LOOKED perfect then when I thought about it some more, I realised that it wouldn't work - the information I gave you was not really correct. It's not a critical function, so I have decided to think on it for a while.