updating a field with code

[DangerD321]

Hi me again, last problem this time thoough! :¬)

One table of my database is called Loans, and the fields in question here are
Return Date and Days Late. Basically, what I want is the days late to be worked
out automatically from return date. The user enters the return date in a form
(Add Loan) when creating a new loan. The code that works out the days late will
have to be called whenever the database opens, which I don't know how to do.
What code would I have to use and where would i put it? Also, if the return
date is greater than todays date, i just want days late to be 0. I know in
pseudo code it would be something like

while there are more records

Dim DateDifference As Integer
If ([Return Date] >= Now()) Then
DateDifference = 0
Else
DateDifference = DateDiff("y", [Return Date], Now())
End If

endwhile

Loans.[Days Late] = DateDifference

something like this, I haven't learnt much coding though and this is just what
I have managed to get together from the help files

Thanx for any help, my teacher told me to ask here because he wasn't sure
either.

Donovan

[Fletcher Arnold]

"DangerD321" <da********@aol.com> wrote in message
news:20***************************@mb-m22.aol.com...

Hi me again, last problem this time thoough! :¬)

One table of my database is called Loans, and the fields in question here are Return Date and Days Late. Basically, what I want is the days late to be worked out automatically from return date. The user enters the return date in a form (Add Loan) when creating a new loan. The code that works out the days late will have to be called whenever the database opens, which I don't know how to do. What code would I have to use and where would i put it? Also, if the return date is greater than todays date, i just want days late to be 0. I know in
pseudo code it would be something like

while there are more records

Dim DateDifference As Integer
If ([Return Date] >= Now()) Then
DateDifference = 0
Else
DateDifference = DateDiff("y", [Return Date], Now())
End If

endwhile

Loans.[Days Late] = DateDifference

something like this, I haven't learnt much coding though and this is just what I have managed to get together from the help files

Thanx for any help, my teacher told me to ask here because he wasn't sure
either.

Donovan

Do not use code! Instead, the days overdue should be put into a query
expression so it is always correct, e.g.

SELECT Loans.ID, Loans.DateDue,
IIf((Date()-[DateDue])>0,Date()-[DateDue],0) AS DaysOverdue
FROM tblLoans

This will do what you have asked for, but why not change the query to return
DaysLeft which will could be either a positive or negative number or, if due
today, then zero. More importantly, where is the field to say whether it
has actually been returned or not? The first query will show loans becoming
more and more overdue since there is no field to say they have been
returned.

Fletcher

[DangerD321]

thanx, itll be much easier now, although i really don't understand that query
:¬S

[Fletcher Arnold]

"DangerD321" <da********@aol.com> wrote in message
news:20***************************@mb-m22.aol.com...

thanx, itll be much easier now, although i really don't understand that query :¬S

It's not that difficult:

SELECT Loans.ID, Loans.DateDue,
IIf((Date()-[DateDue])>0,Date()-[DateDue],0) AS DaysOverdue
FROM tblLoans

It simply takes 2 actual fields from the table: tblLoans.ID and
tblLoans.DateDue and calculates a third. This third column is given an
alias of "DaysOverdue" using the keyword "AS".

You could have used simply Date()-[DateDue] AS DaysOverdue but you said you
needed it to show zero if the loan was not actually overdue. To do this you
use the IIF function which you can look up in the help file.

IIf((Date()-[DateDue])>0,Date()-[DateDue],0) AS DaysOverdue

Which says that if (today's date) - (the due date) > 0 then show this
positive amount, otherwise show zero.

Anyway, a better solution might be:

[DateDue]-Date() AS DaysLeft

which would give the number of days left positive, negative (overdue), or
zero (due today)
Fletcher

[DangerD321]

i want the actual value to be stored in the Loans table though.

SELECT IIf((Date()-[Return Date])>0,Date()-[Return Date],0) AS DaysOverdue FROM
Loans;

works but where can i put it if i want it to actually store the value in the
table under Days Late?

Thanx

[Pieter Linden]

da********@aol.com (DangerD321) wrote in message news:<20***************************@mb-m05.aol.com>...

i want the actual value to be stored in the Loans table though.

SELECT IIf((Date()-[Return Date])>0,Date()-[Return Date],0) AS DaysOverdue FROM
Loans;

works but where can i put it if i want it to actually store the value in the
table under Days Late?

Thanx

Bad idea... you'd have to update your table every day. Okay, now that
I've said it's a bad idea, ...

You CAN do it, although it's not advisable... Create a query, like
Fletcher showed, then turn it into an update query, and stuff the
"days overdue" value back into the table.