Microsoft Access Lending Library

Question

I am making a lending library sort of thing, based purely from the template. The template has 99% of the things I want it to do, but I want to add an extra feature. For each item that is listed in the library, I want to see how many days it has been since it was checked in.

Therefore, I want to add a "Return Date" column in the Transactions table and the Assets table. This should be the date that is automatically entered when each item is checked in. 

Then I want to create a query to tell me how number of days since being checked in. This will basically tell me how long an item has been sitting on the shelf for without being loaned out.

Thanks in advance for your help

Answer 1

This query would have an additional column with this in the Field area:

DaysSinceLastCheckin: DateDiff("d",[Checked In Date],Date())

This names the column DaysSinceLastCheckin. Note how unlike MSFT I don't use spaces and other funny characters, which might cause notational difficulties.

Then I call the DateDiff function. Look it up in the help file and you will notice it takes 3 arguments:

"d" says we want to return the difference in days (not in years, or seconds, etc.)

[Checked In Date] is your field in the Transactions table

Date() is a VBA function that returns today's date.

Thanks for the advice Tom. It worked perfectly

I do have one further question though. I realised afterwards that the query would give the results of all items, regardless of whether they are checked out, available, or have been checked out multiple times. So with a library of approximately 120 items, I was receiving over 150 results from the query. Is there a way to filter out all the unavailable items and show only the last return date? 

Hopefully I'm not asking too much. I tried to experiment a little to find the answer, but I couldn't do it. Thanks for your help and your patience with assisting me

Answer 2

Did you try searching on Datediff to find out the syntax? This forum is meant more to give people direction to help them find answers than to give them full answers.

DateDiff is a function like any builtin function. You use it in a query by adding a column with an expression.

ElapsedDays: DateDiff("d",[CheckedOut],IIf(isNull[CheckedIn),Date(),[CheckedIn]))

Answer 3

This query would have an additional column with this in the Field area:

DaysSinceLastCheckin: DateDiff("d",[Checked In Date],Date())

This names the column DaysSinceLastCheckin. Note how unlike MSFT I don't use spaces and other funny characters, which might cause notational difficulties.

Then I call the DateDiff function. Look it up in the help file and you will notice it takes 3 arguments:

"d" says we want to return the difference in days (not in years, or seconds, etc.)

[Checked In Date] is your field in the Transactions table

Date() is a VBA function that returns today's date.

Answer 4

I don't think it's a good idea to add those fields: that's what you have Transactions.[Checked in Date] for.

The query you're thinking of will use the DateDiff function to calculate the number of days between the checked in date and today.

Thanks for your reply Tom,

How do I set up the DateDiff function in a query? I'm very much a beginner with Access so please use layman terms where possible. Thanks

Answer 5

I don't think it's a good idea to add those fields: that's what you have Transactions.[Checked in Date] for.

The query you're thinking of will use the DateDiff function to calculate the number of days between the checked in date and today.