Query for books outstanding for one week 1

[bajanJedi]

Hey,

I have this library database I'm working on and I have a problem. I need to run a query to find out the books that are outstanding for one week from the current date. I have the visitor's info, the date borrowed and the date due (mm/dd/yyyy) in this query. Any suggestions?


Lisa

 

[SteveR77]

Lisa,

You could use the DateAdd function on the overdue date and compare the value with the result of the Date function.

Outstanding: DateAdd("d", 7, "[DueDate]&quot

Your criteria for the field above would be Date()

HTH,

Steve

 

[TheAceMan1]

How are ya bajanJedi . . . .

I not sure which date you want to use, but use the following as criteria:

< (Now()-7)


TheAceMan

 

[jgoodman00]

SELECT *
FROM SomeTable
WHERE DateDiff('d', [DateDue],Date()) > 7


James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures

 

[bajanJedi]

Steve, the code u gave me a 'data type mismatch in criteria section' error.

AceMan, [I'm doing fine, thanks for asking] the code you gave me runs, but the data is incorrect. I placed the criteria code under the DateDue field. I have books borrowed going back to January 10th. The lastest due date is in March.

James, I am not quite sure I understand what you have there. Sorry.

 

[jgoodman00]

Ok, I have basically written a query which will return all data for records in the table [SomeTable] where the Difference in days between TodaysDate & the field [DueDate] is greater than 7.



James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures

 

[bajanJedi]

Ok thanks for explaining that. But what I should have also asked was where do I put that code?

The query has to produce records of books overdue for one week from the current date only. So if books are overdue since January, they shouldn't show up.

I have in the query the DateDue field which I am using. I wanted to know if "DateDiff('d', [DateDue],Date()) > 7" was a new field or if it goes under the criteria of the DateDue field.


Lisa

 

[jgoodman00]

Ahh ok.

In that case you need something like:
WHERE DateDiff('w', Date(), [DateDue]) = 1

This is the WHERE clause which is in SQL. Switch to the SQL pane & paste this in...

James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures

 

[bajanJedi]

OK, I maybe I should also mention I know nothing in SQL so I had to retrieve the help files. I placed the code in the criteria section [as it said in the help file] along with the select and from. Well I can tell you it [finally] runs. But it's only showing one record and the record is not the even the right one.

Lisa

 

[jgoodman00]

Ok, in order to troubleshoot the DateDiff create a calculated column in the query. We will then set the criteria on this:

So, in an empty 'field' column enter:
OverDueBy: DateDiff('ww',[DateDue],Date())

This should then add a column to your query output, with the calculated 'OverDueBy' column, which should be the number of weeks by which any given book is overdue. You might need to swap the DateDue & Date() order around in the DateDiff function to get the output you require (i.e. not negative).

Once the output value is correct, simply add your criteria to the criteria part (i.e. = 1) & uncheck the 'show' entry in the query design grid...

James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures

 

[bajanJedi]

Ok, I had to change around the DateDue and Date(). I ran the query to make sure it would work and the calculations are right. Now when I place thIS bit of code in the criteria

(SELECT DateDue
FROM tblUserLoans
WHERE DateDiff('ww', [DateDue], Date()) = 1)

I get this message - 'At most one record can be returned by this subquery.' And there are at least 6 records that should show up.

Lisa

 

[jgoodman00]

You criteria for the calculated field is simply 1; the number of weeks by which a book is overdue.

Otherwise you are creating a subquery which is like a query within a query, which I dont think you are after.



James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures

 

[bajanJedi]

Ohhhh.... I see it. I deleted the "sub-query" from the DateDue criteria and placed the 1 in the calculated field criteria. It's working now. Much thanks.

I was also wondering if you knew anything about the ActiveX Date Picker control. I have these reports that have to be generated showing the frquency of books loaned daily, weekly, monthly and yearly. I got the daily one done, skipped the weekly and tackling the monthly.

I copied the same date picker form I used for the daily report. I added two date pickers - one for the start date (beginning of the month) and end date (ending of the month). Is there anyway I can set the start date to the first of the month and the end date to the end of the month? I'm working with the field DateBorrowed and is a date field (mm/dd/yyyy).

There is a command button on this form as well that opens a preview of the report.

 

[jgoodman00]

It might be best to post this as another question:


If you are using two date pickers then why do you need any others? In theory you can select any date range with two (from 1 day through to any number of days/months/years).

If you want static reporting based on BookLoans by month, you should be able to use the DatePart function to isolate the Month any given book was borrowed. This could also be applied to day/week/year etc...

James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures