1. Registered User
Join Date
Jan 2004
Posts
106

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!

Join Date
Feb 2004
Location
New Zealand
Posts
1,482
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

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

4. Grand Poobah
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. Registered User
Join Date
Jan 2004
Posts
106
whats the false part when its at home? can you give me an example please?

Join Date
Feb 2004
Location
New Zealand
Posts
1,482
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.

7. Grand Poobah
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. Registered User
Join Date
Jan 2004
Posts
106
i dont understand all this about the groupby and falsepart

arhhh

9. Grand Poobah
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. Registered User
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. Registered User
Join Date
Jan 2004
Posts
106
how do i include the false part then?

12. Grand Poobah
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?

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. Registered User
Join Date
Jan 2004
Posts
106
hye thanks all, uve been v helpful

Join Date
Feb 2004
Location
New Zealand
Posts
1,482
can use the

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

15. Registered User
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
•