MS Access Queries-in particular, dates 1

[Marthaot]

I am doing a project for college which entails creating a database for a video rental store. I must create a list of overdue tapes. To do so , I have attempted to create a query with the criteria :
show video title where "date_returned" > "Date_rented" + 1
I know that this does not work, but it explains what I want to do. The video is classed as overdue if it is returned more than one day later. Does anyone have any ideas? I'd really appreciate it, as I want to get a good grade!!!

 

[Craig0201]

Martha....

This is pretty easy stuff.....

Put this in the criteria of the Date_Returned....

> [Date_Rented] + 1

But.....

Wouldn't it be more useful to have that on the form for returns rather than a query??

Craig

 

[Marthaot]

Craig,

Thanks for the help.

However, I need to generate a list of overdue tapes aswell.
I like the suggestion you made about the form. Can a list be generated from a form?

Martha

 

[Craig0201]

Ok....

Well....

Firstly, think you're missing out from your list items which are overdue and have not been returned.....

So let's split it......

In your Date_Rented put the criteria <Date()-1

This means that only those items that could be overdue will be returned......

Now lets narrow it down.....

In Date_Returned put the criteria >Date_Rented + 1

And on the next line down, put the criteria Is Null

Now because we've got an OR situation, you'll need to put the Date_Rented criteria in twice.....once for each separate Date_Returned criteria....

Now, the other bit was designed to highlight that a tape was overdue when returned......highly useful when it comes to collecting fines!!!

You could set up a form to run off the query we've defined.....but a report would be more useful.....

Craig