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

    Unanswered: Dialog box with 2 date pickers...

    Hey all,

    I have these reports that generate the number of books that were borrowed from a library on a daily, weekly, monthly, and yearly basis. I have switchboard that has these command buttons on it (daily, weekly, monthly, and yearly), when u click on daily, a dialog box appears asking the user to select the date picker, then click go to preview how many books were loaned that day.

    The others are a bit straining. For the weekly one I was considering creating a new dialog box with 2 date pickers for the start of the week and the end of the week. The thing is, the report is based on a query with the week format - Week: Format([DateBorrowed],"ww"). So the week appears like 01 or 02... no dates indicating the start or end of the week, which can be confusing.

    The monthly query is quite similar to this with the format showing up like 01, 02, 03. I have a dialog box with two date pickers already on it with the start date and end date, but it still shows all the months. So that's my problem.

    Any suggestions?

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

  2. #2
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343
    first questiion

    do you really have to have a date picker where you use week numbers as the criteria - can you not use the date 12/03/04 etc

    if you do HAVE to use week numbers i assume that they run sequentialy from 01 - 52 (or 53)

    rgds

    gareth

  3. #3
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032
    Hopefully this might give you some ideas as far as the query by form concept goes (and the 2 dates area actual dates as opposed to week numbers but hopefully you can still learn from it).

    The free downloadable sample database at www.bullschmidt.com/access uses the query by form concept so that on the invoices dialog one can optionally choose a rep, a customer, and perhaps a date range, click on a button that says "Input," and then have the invoice form open up showing all the invoices that match the criteria.

    And here is how the query by form concept can work.

    On the invoices dialog there are the following controls:
    InvDateMin with DefaultValue of =DateSerial(Year(Date())-1,1,1)
    InvDateMax with DefaultValue of =Date()
    InvRepNum with DefaultValue of *
    InvCustNum with DefaultValue of *

    Also on the invoices dialog there is a command button called cmdInput to open the invoices form with the following code behind the OnClick property:
    DoCmd.OpenForm "frmInv"

    And of course there could be a button to open a report the same way:
    DoCmd.OpenReport "rptInv", acViewPreview

    The invoices form (frmInv) has RecordSource property of qryInv.

    And the qryInv query's criteria for the InvDate field has:
    Between [Forms]![frmInvDialog]![InvDateMin] And [Forms]![frmInvDialog]![InvDateMax]

    And the qryInv query's criteria for the RepNum field has:
    Like [Forms]![frmInvDialog]![InvRepNum]

    And the qryInv query's criteria for the CustNum field has:
    Like [Forms]![frmInvDialog]![CustNum]

    One related point is that you probably wouldn't want to allow blanks (i.e. Nulls) in fields that are going to be used with Like in any criteria for that field. Otherwise the blanks wouldn't be shown. And to counter that you might consider creating the query's SQL statement dynamically so that the criteria on a particular field isn't used unless needed.
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

Posting Permissions

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