Late Fees, How Do I Properly Code Them Using IIF

[chosch]

hey, my first question on these forums. i've had a look around but can't find a completely relevant solution to my question.
for example, i have a book that is borrowed on the 07/08/08, it has a one week maximum borrowing time meaning it HAS to be returned before 14/08/08 otherwise you incur a $5 late fee, how can i properly code this so that it will say under the "Late Fees Incurred" field either "$5" or "None"... i thought it would be straightforward, but it isn't. the relevant fields are "Borrow Date" being 07/08/2008, "Due date" being 14/08/2008 and "Returned Date" being 15/08/2008. any help would be greatly appreciated

 

[namliam]

What do you have that is not straingforward and/or not working?

We can give you what you need, but that doesn learn you anything...

 

[boblarson]

We'll start with a hint -

Look into using the DateDiff function.

 

[Steve R.]

I have not used this website, but it lists database models. See #80 for libraries.

 

[raskew]

Hi -

... it HAS to be returned before 14/08/08...

If the DueDate = 14/08/08, why would it have to be returned before that date?

Is the $5.00 late fee a one-time thing, or is it $5.00 per day?

Bob

 

[chosch]

sorry, il clarify your questions. it must be returned on the day or before hand or the person is fined $5 (not per day, just one fee), i thought you would use it like so: Iif("Returned Date">"Due Date",$5,No Fees) but obviously not.

 

[boblarson]

You would not use quotes around your fields:

=(Nz([Returned Date])>[Due Date],"$5","No Fees")

Now, that will not be able to be used in calculations as No Fees is not a number. But, if you want it used in a calculation (to add the fee on) then you would use:

=(Nz([Returned Date])>[Due Date],5,0)

 

[FizzyFish]

Using the IIF structure, you are on the right track, but the syntax is a little off. Replace the quotations around your fields with Square Brackets.

If the values you want returned are just text values then putting quotations around them is fine.

 

[chosch]

cheers fellas, the code works perfect.
by entering

Late Fees Incurred: IIf([Returned Date]>[Due Date],5,0)

into queries, it works perfect. how come it cannot read the returned date from the table... it prompts you (i know thats what happens with square brackets, i'm quite aware of that).

 

[boblarson]

how come it cannot read the returned date from the table... it prompts you (i know thats what happens with square brackets, i'm quite aware of that).

No, that ISN'T what the square brackets are for in this case. In this case it is to designate that it is a FIELD NAME and if you are getting prompted then you didn't type the correct field name in. Make sure the field names are EXACT (including any spaces or underscores).

 

[chosch]

ahhhhh, thanks again. yep problem solved.

 

[boblarson]