Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jan 2004
    Posts
    106

    Unanswered: Calculations PLEASE HELP

    i have created a query to calculate fines for overdue library books and it doesnt bloody work! below is the formula:

    Days Late: DateDiff("d",[Date Due Back],Now())

    then in teh next box:

    IIf([Days Late]<7,0) Or IIf([Days Late]<14,0.3) Or IIf([Days Late]<21,0.6) Or IIf([Days Late]<28,1) Or IIf([Days Late]<56,1.5) Or IIf([Days Late]>56,3)

    can somebody please tell me why when i run the query it asks for "Days late" as shown in the attatched image.

    Thanks!
    Attached Thumbnails Attached Thumbnails a.bmp  

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,420
    Provided Answers: 7

    Lightbulb

    Take out the Groupby out for the

    IIf([Days Late]<7,0) Or IIf([Days Late]<14,0.3) Or IIf([Days Late]<21,0.6) Or IIf([Days Late]<28,1) Or IIf([Days Late]<56,1.5) Or IIf([Days Late]>56,3)


    Line

    and change it to

    Expression

    cause you want access to work out the day count first before doing the
    iif thing
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Jan 2004
    Posts
    106
    hey thanks but how do i do that?

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Craig,

    Also look CAREFULLY at your IIf statements ... You're missing a parameter - the False part. IIf([Expression],TruePart ,FalsePart)

  5. #5
    Join Date
    Jan 2004
    Posts
    106
    whats the false part when its at home? can you give me an example please?

  6. #6
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,420
    Provided Answers: 7
    just Edit the Query

    and change the Totals Row form Groupby to Expression


    M Owen yes you are right

    but it will not Error cause of that

    the Falsepart can be optional
    but can cause a lot of problem

    age = 12

    aaa = iif(age>13,"Teen","Not Teen")

    aaa = "Not Teen"
    Last edited by myle; 02-03-04 at 16:02.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by craig_dixon
    whats the false part when its at home? can you give me an example please?
    Ex: IIf (([Days Late]<7,0,0)

    I was just looking at your code again and noticed that you have some bad logic here ... You're attempting to OR numbers there ... What are you looking to have with that snippet of code? A single multiplier for a late fee?

    If that's the case, you'll need to nest the IIf's ...

  8. #8
    Join Date
    Jan 2004
    Posts
    106
    i dont understand all this about the groupby and falsepart


    arhhh

  9. #9
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by myle
    just Edit the Query

    and change the Totals Row form Groupby to Expression


    M Owen yes you are right

    but it will not Error cause of that

    the Falsepart can be optional
    Stephan,

    To Quote:

    IIf Function


    Returns one of two parts, depending on the evaluation of an expression.

    Syntax

    IIf(expr, truepart, falsepart)

    The IIf function syntax has these named arguments:

    Part Description
    expr Required. Expression you want to evaluate.
    truepart Required. Value or expression returned if expr is True.
    falsepart Required. Value or expression returned if expr is False.

    The False part IS REQUIRED.

  10. #10
    Join Date
    Jan 2004
    Posts
    106
    yeah, i need to calculate a fine but the more days the book is late, the higher the fine is. how do i do that then?

  11. #11
    Join Date
    Jan 2004
    Posts
    106
    how do i include the false part then?

  12. #12
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by craig_dixon
    yeah, i need to calculate a fine but the more days the book is late, the higher the fine is. how do i do that then?
    Nest your IIf's ... Ex:

    IIf([Days Late]<7,0, IIf([Days Late]<14,0.3, IIf([Days Late]<21,0.6, IIf([Days Late]<28,1, IIf([Days Late]<56,1.5, IIf([Days Late]>56,3))))))

    You may want to cosider reordering them ... You'd most likely lose one conditional if you do ...

  13. #13
    Join Date
    Jan 2004
    Posts
    106
    hye thanks all, uve been v helpful

  14. #14
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,420
    Provided Answers: 7
    can use the

    DaysOver = DateDiff("d",[dateback],now())
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  15. #15
    Join Date
    Jan 2004
    Posts
    106
    i have used that formula, but that only works out how many days the book is late. i need to work out the fine from that.

    the other formula still doesnt work. it produces the same problem (see attatchment)

Posting Permissions

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