Results 1 to 8 of 8
  1. #1
    cs93 is offline Novice
    Windows XP Access 2000
    Join Date
    Mar 2011
    Posts
    6

    Help with Access - IIf Function

    Hi



    I am doing my AS Level ICT coursework and am required to create a library system. The system needs to be able to automatically calculate a return date for a book 14 days after the date it was loaned out.

    However, the system also needs to take into account school holidays; if the return date for a book is during a school holiday then the return date becomes the first monday after the holiday.

    I have been trying to create a macro in Visual Basic using an iif function to do this. Here is what I've got so far:

    iif([Due Date]=25/10/11,"1/11/10",iif([Due Date]=26/10/10,"1/11/10",iif([Due Date]=27/10/10,"1/11/10",iif([Due Date]=28/10/10,"1/11/10",iif([Due Date]=29/10/10,"1/11/10",iif([Due Date]=30/10/10,"1/11/10",iif ([Due Date]=31/10/10,"1/11/10", =DateAdd("d",14,[Loan Date])))))))

    ("1/11/10" is the first monday after the holiday, all other dates are from during the holiday itself. The false value is the Date Add function to add 14 days on as normal.)

    Visual Basic didnt like my use of greater and less than functions so I have had to list each date separately and create a nested iif function.

    I don't know whether this is correct (I have little knowledge of Visual Basic) but I keep getting a compile error message saying "Expected =". I have no idea what this means.

    Any help would be greatly appreciated.

    Thanks
    cs93

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Does it need to be an IIF function or can you use a User Defined Function (UDF). IE: http://www.mvps.org/access/datetime/date0012.htm

  3. #3
    cs93 is offline Novice
    Windows XP Access 2000
    Join Date
    Mar 2011
    Posts
    6
    Yes i can see how that could work. However, what about having multiple holidays. Would i need multiple versions of the function?

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Your Holiday table contains records for *all* of the Holidays you want to avoid. The same code works.

  5. #5
    cs93 is offline Novice
    Windows XP Access 2000
    Join Date
    Mar 2011
    Posts
    6
    Does that mean theoretically that i could do the opposite and have a table containing dates the school is open. You could then have the database select a value from this table eg. To add on 10 days, select the 10th record down from the loan date.

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I suppose that would work but why? What's wrong with what has already been proposed?

  7. #7
    cs93 is offline Novice
    Windows XP Access 2000
    Join Date
    Mar 2011
    Posts
    6
    Because then you wouldnt need to take holidays into account? Not sure how this would work though, i think every ict student in the country is currently struggling with this.

  8. #8
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    It is your project and you can do it any way you want. There is no "wrong" way as long as it works.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Vlookup function in access
    By rici7 in forum Forms
    Replies: 1
    Last Post: 10-16-2010, 04:41 PM
  2. excel function in access
    By lmp101010 in forum Queries
    Replies: 1
    Last Post: 08-03-2010, 05:02 PM
  3. Load images in access using iif function?
    By sureshfina in forum Forms
    Replies: 1
    Last Post: 12-22-2009, 11:05 AM
  4. Access SQL Query - Aggregate function
    By jack in forum Access
    Replies: 0
    Last Post: 11-10-2009, 08:06 PM
  5. to_char function in Access?
    By Amber_1977 in forum Queries
    Replies: 0
    Last Post: 01-20-2009, 04:45 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums