Results 1 to 6 of 6
  1. #1
    Join Date
    May 2009
    Location
    Crete, Greece
    Posts
    90

    Unanswered: Calculated field on form: 'Iif' statements including dates

    Hi everyone.

    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.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I would put this in a table and link the table. This is just data - it doesn't belong in code.

    The syntax is different as this is SQL Server, however the principle is the same (CASE is very similar to Iif):
    Data belongs in your tables -- not in your code
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    May 2009
    Location
    Crete, Greece
    Posts
    90
    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?

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    No, you can't do calculations in a table.

    You can have a calculated control with:

    =IIf([Agency] = "StarTours", IIf([Hotel] = "Atrium", IIf([dates] between [Arrival_Date] and [Departure_Date],"guarantee","allotment"),"Allotment"), "Allotment")

    Or something similar... in the control source.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    May 2009
    Location
    Crete, Greece
    Posts
    90
    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.

    many thanks

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Well, hopefully it will help someone else
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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