1. Registered User
Join Date
Jan 2004
Posts
106

I have written 2 queries, the first one finds out how many days late a book is and the second one calculates a fine. the second query runs off the first one

The SQL for query one is:

SELECT LOAN.[Book Number], LOAN.[Date Returned], IIf(DateDiff("d",[Date Due Back],[Date Returned])<=0,0,DateDiff("d",[Date Due Back],[Date Returned])) AS [Days Late], LOAN.[Date Due Back]
FROM LOAN
ORDER BY LOAN.[Date Due Back];

and the SQL for query two is:

UPDATE LOAN, 1a SET LOAN.Fine = 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))))))
WHERE ((([1a].[Book Number])=[Please Enter Book Number]));

when i run query two it asks for the book number as its supposed to but the updates all the records in the "loan" table and changes the "fine" field to £0.00. i want the query to update only the record with the book number as specified by the user and the correct fine needs to be displayed

I have attatched a screenshot of the design view of the two queries to help:

2. Registered User
Join Date
Jan 2004
Location
Romania - Bucharest
Posts
50
Try changing the WHERE clause in your second query to:

WHERE ((([1a].[Book Number])=[LOAN].[Book Number]));

3. Registered User
Join Date
Sep 2003
Location
The extremely Royal borough of Kensington, London
Posts
778
update table t1
set t1.value = x
where t1.id = [param]

I would recommend creating a function to calculate the fine, which can then be called as an expression an SQL query.
Last edited by r123456; 02-19-04 at 07:29.

4. Registered User
Join Date
Jan 2004
Posts
106
thanks for the help but i dont understand the last post. could you please either explain it or paste a code that i can copy? i would appreciate this very much

5. Registered User
Join Date
Jan 2004
Posts
106
when i run the query now, it updates the table with nothin in it at all. the SQL for query 1 is:

SELECT LOAN.[Book Number], LOAN.[Date Returned], IIf(DateDiff("d",[Date Due Back],[Date Returned])<=0,0,DateDiff("d",[Date Due Back],[Date Returned])) AS [Days Late], LOAN.[Date Due Back]
FROM LOAN
ORDER BY LOAN.[Date Due Back];

and the SQL for query 2 is:

UPDATE LOAN, 1a SET LOAN.Fine = 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))))))
WHERE ((([1a].[Book Number])=[Please Enter Book Number]));