DateAdd question

[Ryko]

Hello

I'm very new at this - just found out about the DateAdd function today trying to solve my problem, so that you know who you're dealing with.

Let's say Im operating a video rental shop, and want to calculate a DateDue (=when videos need to be returned) based on DateRented and number of videos rented..

DateAdd ("d", 5, [DateRented]) would be appropriate for 5 videos rented at once.

I want to replace 5 here with a number of videos rented, and this number (TAPECOUNT below) is also calculated in a query using count function (to count the number of videos associated with each rental)

Preferably I would end up with a single query consisting of 4 columns:
RENTAL_ID, TAPECOUNT, DATERENTED, DATEDUE, of which both 2nd and 4th column would be calculated, 2nd in combination with 3rd serving as input for 4th.

Can it be done like this and how? Would I need to store TAPECOUNT value first? Is DateAdd even a proper function for my task? I'm open to all suggestions, just keep in mind I'm a newbie.

 

[Ryko]

Still no idea

Here is a query which calculates TAPECOUNT

SELECT COUNT(Tape_Rental.TAPE_ID) AS TAPECOUNT
FROM Tape_Rental, Rental
WHERE Tape_Rental.RENTAL_ID=Rental.RENTAL_ID;

Is it possible to replace number 5 in above example with this? I've tried it and it doesn't work, maybe I'm doing something wrong. I've been trying to work this out for the whole afternoon and I'm getting frustrated as it's probably easy for anyone with some experience. Help greatly appreciated.

 

[gromit]

Hi - Welcome!

I think that I am a little confused - how does the number of videos affect the due date? [Seems like you can have multiple videos, each with its own due date.]

The DateAdd function just adds the given time interval to a date (to save you the trouble of figuring what 23 days past July 28 is, for example). So DateAdd("d", 5, [DateRented]) is just 5 days after the rental date.

So does DateAdd give you the Due Date that you are looking for?

I think then you are next trying to figure out how many videos are due by a certain date. This would be something like -

SELECT COUNT(TapeRental.TapeID) as TapeCount
FROM TapeRental
WHERE (TapeRental.RentalID = [Renter] AND TapeRental.DateRented + 5 < [DateChecked])

A couple of notes
1. I have used [Renter] to indicate the RentalID that you are examining, but you will have to either take this off a form or enter as a parameter.
2. Similarly, [DateChecked] is the date that you are checking against
3. Using underscores "_" can cause problems, so I suggest that you don't use them in your variables.
4. This is just rough sample, so it may need tweaking.

Does that help any?

- g

 

[Ryko]

gromit - thanks for your response

Actually in my case the number of videos directly affect the due date. If you rent 1 video, you must return it a day after, 2 videos in two days and so on.

The TapeCount query I mentioned above calculates the number of tapes in a rental and that number should then be added to a DateRented to give a DateDue.

I've solved my problem by creating a DateDue query (in addition to TapeCount query) with all 4 columns mentioned in my first post just like I wanted.

SELECT Rental.RentalID, TapeCount.TapeCount, Rental.DateRented, [DateRented]+[TapeCount] AS DateDue
FROM TapeCount, Rental;

It works. I wasn't able, however, to perform TapeCount and use it to calculate DateDue in a single query. Some of the errors I received were:

You tried to execute a query that does not include the specified expression <name> as part of an aggregate function.

or

"Circular reference caused by <query reference>"

The closest I came to solving it all in a single query was when I was asked for parameter value for TapeCount after executing the query, but query results were always correct and the same no matter what value I typed

After I chose additional query it all seemed very easy.

 

[gromit]

Glad that you got it sorted out.

Sometimes two queries in sequence just make things work better (or more easily?). There may be some way to combine them into one, but it often isn't worth it.

The message "you tried to execute a query that does not include the specified expression <name> as part of an aggregate function" means that you are summing (or max or avg etc.) on some of the fields, but not on others, so Access doesn't know how to make sense of it.

I have never gotten the circular reference message before, but it makes sense to me now that I understand your due date approach. You can't figure out the due date untill you know the total number of videos.

Good work on getting it figured out!

- g