Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2004
    Posts
    19

    Unanswered: automatic date fill and fine calculation

    Hi

    I have a Video Shop Database.

    One of the tables is the Loans table. The 4 last fields are:

    Date Taken Date Due Date Returned Fine

    When I type the Date taken, I want my Date Due to be automatically filled in as 2 days later i.e. if I type 09/11/04 for the former, then the latter should automatically display 11/11/2004. I know I have to write a validation rule but I simply dont know how to!

    Also, Fine needs to be automatically updated when I add the Date Returned. The difference between the Date Due and the Date Returned needs to be calculated and then multiplied by, say, 1.

    Any help will be much appreciated

    Sykes

  2. #2
    Join Date
    Oct 2004
    Posts
    19
    ok, forget the automatic update of Date Due.

    In a query, if I already have my

    --- Date Due --- Date Returned ---

    information filled in, can I calculate a fine?

    How would I write the command to calculate the DateDiff between those two fields and then multiply this number (by 1) for the fine?

    Thank you

  3. #3
    Join Date
    Oct 2004
    Posts
    19
    Mission accomplished.

    A little trial and error gets you there in the end
    Attached Thumbnails Attached Thumbnails LateFine.jpg  

  4. #4
    Join Date
    Oct 2004
    Posts
    19
    (just incase anyone was wondering, my fine was 1 per day so the code in the "Fine " field ended with ...*1

    Sleep time

  5. #5
    Join Date
    Oct 2004
    Posts
    19
    Hello ppl...

    Well, I'm kinda happy with the result last night but I need some help.

    As you can see above, the last fine is -1, which is due to the film being bought back a day earlier than it was due.

    I want to apply

    if DaysLate < 1 Fine = 0

    but I don't know the correct syntax to put in the design view, in the Fine field, of the query.

    TIA
    Attached Thumbnails Attached Thumbnails fines.jpg  

  6. #6
    Join Date
    Oct 2004
    Posts
    19
    No, I'm not intentionally talking to myself...

    But here's what I did:

  7. #7
    Join Date
    Oct 2004
    Posts
    19
    (Soz, 4got to attach it b4...)
    Attached Thumbnails Attached Thumbnails fines2.jpg  

  8. #8
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    Sykes,

    The solution to set your criteria for Days Late > 0 works if you only want to show the Loans that were late. If you want all Loans but you don't want negative fines, change your 'Fine ' field to =iif([DaysLate]<0,0,[DaysLate]*{whatever your daily fine is})

    TD

  9. #9
    Join Date
    Oct 2004
    Posts
    19
    Thanks for that

    Another question:

    If I want to create a query that will bring up those who have not yet returned a film i.e. have a blank "Date Returned" field, and the film is overdue, i.e. "Date Due" is <Date(), how do I do this?

    What criterion do I have for Date Returned?

    Attached Thumbnails Attached Thumbnails notRet.jpg  

  10. #10
    Join Date
    Oct 2004
    Posts
    19
    I only just found it!

    Criteria for Date Returned is:

    Is Null

    And you probably knew that.

    So thanks.

  11. #11
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    Put "Is Null" (without the quotation marks) in the Criteria filed for DateReturned.

    TD

Posting Permissions

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