Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2004
    Location
    Barbados
    Posts
    50

    Exclamation Unanswered: Creating a query for books one week overdue

    Hey all,

    I'm currently on job attachment at the Ministry of Agriculture and I'm working on a database for the information services library. I got most of the database working in order, but I'm having some trouble with a number of things, so I'm going to try and tackle them one at a time.

    First problem. I have to create these queries for the library books that are overdue. On the main switchboard I have command buttons that open reports that show the books that are due today and the overall library books due. Those were simple ones. The one I'm having trouble with is the one that shows the library books that are a week overdue.

    I have the dateBorrowed and dateDue fields in the query [qryBooksAWeekOverdue] along with the visitor's information (person who borrowed the book) and the book title and BDS# (similiar to ISBN#).

    I would also like a dialog box to appear if there are no books due today, instead of having a blank report appear.

    Thanks,
    bajan_elf

    PS - I have also attached the database incase you would like to see it. I have not setup the main switchboard so it shows up at startup.
    Attached Files Attached Files
    "The extreme always make an impression." - Jeff Hardy

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Select *
    from table
    where DATEDIFF("d", date_OUT, date_RETURNED) > 7
    Last edited by r123456; 02-22-04 at 11:58.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Jul 2003
    Location
    Portugal
    Posts
    111
    I made a little change in your query.

    See if that is what you are looking for.

    Ruas
    Attached Files Attached Files

  4. #4
    Join Date
    Feb 2004
    Location
    Barbados
    Posts
    50
    It looks about right. Thanks for the change.

    But I can see the problem with this approach I didn't think about. Is there a way I can get the user to select the date? Like have a form with Month, Day, Year and the list boxes with the months, days and years in them.

    I think this approach may be easier for the user to select the dates they want instead of having it automatically done for them.


    bajan_elf
    "The extreme always make an impression." - Jeff Hardy

  5. #5
    Join Date
    Jan 2004
    Posts
    13

    Cool

    bajan_elf try the attached version.

    I created a wee form with a date picker control on it

    Reference:

    C:\Windows\system32\MSCOMCT2.OCX

    Windows Common Controls-2 6.0 (SP3)

    Tis a nice wee control as it shows a wee calender to choose your dates from. much nicer than having to type a full date in.
    Code:
    strWhere = "[DateDue]=#" & Format(Me.dtDueDate, "yyyy-mmm-dd") & "#"
    
    On Error Resume Next
    DoCmd.OpenReport "rptBooksDue", acViewPreview, , strWhere, acWindowNormal
    Passing the date picked as the Where clause when opening the report is a nice neat way to do it. You can also easily modify to alter date ranges.

    ;-)
    Attached Files Attached Files
    Last edited by fearnan; 02-22-04 at 18:48.
    no need to panic - its just 1's and 0's

  6. #6
    Join Date
    Feb 2004
    Location
    Barbados
    Posts
    50
    I'm getting an error when I click on the 'Books Due Today' and 'Overall Outstanding Books' button. It says "Undefined function 'Date' in expression". When I click on the 'Books Due' button the dialog from pops up but I can't type anything and when I click go it has a compile error.


    bajan_elf
    "The extreme always make an impression." - Jeff Hardy

  7. #7
    Join Date
    Jan 2004
    Posts
    13
    oops I forgot I made the changes in Access 2003.

    the openreport method has different number of arguments between 2003 and 2000.

    you need to edit the line:
    Code:
    DoCmd.OpenReport "rptBooksDue", acViewPreview, , strWhere , acWindowNormal
    so it reads like this:
    Code:
    DoCmd.OpenReport "rptBooksDue", acViewPreview, , strWhere
    In the form frmBooksdue, goto design view , press Alt f11. locate the sub cmdGo_Click() the line is in there.

    As for the Undefined Function 'Date' error, I have come across this problrm before but for the life of me i can't remember the solution!! (might be cos its monday morning and i ain't finished me coffeee yet either!!)

    i've attached another copy with the change above, compiled in access 2000 as well. (version 9.0.3821 SR-1)

    You may not have the activeX control required for the date time picker. do a search for the file MSCOMCT2.OCX , if its not on ur machine then ehhh u'll need to get it. tis part of VB6 if i remember right.
    Attached Files Attached Files
    no need to panic - its just 1's and 0's

  8. #8
    Join Date
    Feb 2004
    Location
    Barbados
    Posts
    50
    It's past 11 am here, so I hope you've had your coffee, lol. The form is working. It is really, truly working. Very nicely I might add. So thank you so much

    I may need some help (and I am hoping I won't have to) IF I have to do a report for 'books outstanding for more than a week'. I don't quite see the logic in it when I have the overall outstanding books done.

    I have another question to ask regarding the lil date calender you put in for me. I have this other form called frmUsers. It has a subform called subfrmUserLoans. The subform is in datasheet view in the main form and I want to know if I can put the lil calender in the DateBorrowed field instead of the users typing it in.

    The thing is, the DateBorrowed field is connected to the option group that says 'Internal, External'. If the person is internal, the date due is in three weeks and if external, two weeks. After the DateBorrowed is entered by the user, the DateDue is automatically entered [I noticed a slight calculation problem with that too, cause the dates are a bit off].

    So, that just what I wanted to know, and if it can be done any suggestions on how to do it?

    Bajan_Elf
    Attached Files Attached Files
    "The extreme always make an impression." - Jeff Hardy

  9. #9
    Join Date
    Jan 2004
    Posts
    13
    Well first off, you can't use any activeX controls in datasheet view. so u need continuous forms, but .....

    second prob is you cant bind an ActiveX control to a data source using continououssuss forms.

    so eh your only real option , without a lot of messin, is to use single form mode. which may be more of a hinderence in this circumstance.

    a way round it could be:
    Use the continuousus forms just for viewing the data (no date time picker needed). provide a wee button on the form called 'edit' which brings up another form for editing the data.
    this edit form can then use the data time picker.
    no need to panic - its just 1's and 0's

  10. #10
    Join Date
    Feb 2004
    Location
    Barbados
    Posts
    50

    Red face

    I'll leave that alone for awhile. I do though have one more question to ask. I have to generate reports called Daily, Weekly, Monthly and Yearly to produce the number of books borrowed in those time periods. I started out with a date range dialog box but that wasn't going so well. So since you found this date picker I thought I could use it some more.

    I already have the queries set up with the count. The daily one should have been easy to do if I followed the steps you used for the Books Due form. Unfortunately I'm having problems. I copied the code from the Go command button and replaced the date due with date borrowed, but it says something about compile error: method or data member not found.

    I want it similar to the books due form, only when the user selects the date from the date picker, and clicks go, the report with the total amount of books borrowed for that day shows up.

    The others I know I wil have a definite problem with. The way the weekly loans query is structured is that it starts counting from the beginning of the year starting with the first week of january.. 1,2,3,4 and so on. There are no dates, just those numbers, which make it hard to understand.

    The monthly loans query also has numbers, no actual names, but isn't hard to understand. How the user will select the month is another problem.

    This is the only "real" problem that is giving me grat hairs over and I want to try and get something done before I start on other things. I'm already jumping all over the place with this. Again, I really appreciate all the help here and THANK YOU!


    bajan_elf...
    "The extreme always make an impression." - Jeff Hardy

  11. #11
    Join Date
    Jan 2004
    Posts
    13
    put a copy up bajan and I'll have a looksee.
    no need to panic - its just 1's and 0's

  12. #12
    Join Date
    Feb 2004
    Location
    Barbados
    Posts
    50
    Hey, I got the Daily Books Loaned working. It's just the weekly, monthly and yearly ones in the Library Reports switchboard now.

    I was thinking, I had to create a new date picker form called 'frmBookLoansDate' in order to get the Daily report working... so would I have to make other forms like that with the date picker for the rest of the reports?

    I also tried having an option group instead of seperate command buttons for the Books Loaned but was having trouble with that.

    bajan_Elf
    Attached Files Attached Files
    "The extreme always make an impression." - Jeff Hardy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •