Ask Experts Questions for FREE Help !
Ask
    IowaGuy's Avatar
    IowaGuy Posts: 23, Reputation: 1
    New Member
     
    #1

    Nov 29, 2007, 07:28 AM
    Date input parameters on a report
    I have used Crystal Reports 8.5 in the past. One of features I liked is when a report was ran, it prompted the used to specify a date range.
    Can Access 2003 do that? If so, can you point me into the right direction on how to make it happen.
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #2

    Nov 29, 2007, 07:38 AM
    Yes Access can easily do that. There are different ways to do it, but the method I use is to create a form to kick off the report. On the (unbound) form, I place two text controls named txtStart and txtEnd. Also on the form is a button to run the report. The report itself is bound to a query. In the query the criteria for the date column is set to:

    BETWEEN Forms!formname!txtStart AND Forms!formname!txtEnd

    (use the correct name of the form). When you press the button to run the report, the query will be filtered by the range of dates.
    IowaGuy's Avatar
    IowaGuy Posts: 23, Reputation: 1
    New Member
     
    #3

    Nov 29, 2007, 08:02 AM
    Scott, you are everywhere! Mercy.

    Can you provide a link for this?
    If I am unable to do it from the link, I see that you take calls. I'd be willing to call.
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #4

    Nov 29, 2007, 08:21 AM
    I'm not sure what you need a link for. I think the instructions I gave were pretty clear. I happened to have a sample of this so I'm attaching it. If you still need more help, let me know.
    Attached Files
  1. File Type: zip parameters.zip (20.3 KB, 27 views)
  2. IowaGuy's Avatar
    IowaGuy Posts: 23, Reputation: 1
    New Member
     
    #5

    Nov 29, 2007, 01:55 PM
    Wow Scott.
    It totally clicked. I got several reports functional with this method. Thanks!

    Another question. Now, I am more of a graphics guy than a programmer. I took the basics in college, but nothing advanced. I have red some SQL books and I feel I understand the concepts until the advanced stuff. Ok..

    In my report, I have a sum of 3 columns grouped by date. This is what it shows.
    The total for each date. Perfect so far.

    PrintDate Apple EverythingElse Total
    ---------- ------ ----------------- ------
    11/4/2007... 100
    11/5/2007... 552

    I want it to do more. I want to know how many of the total were for Apple and how many were EverythingElse.

    Apple has a CustomerID of 1
    Grapes = 2
    and so on.

    In the report, I assume it's the report, maybe it's the VB code view.
    I created a text box for Apple (txt_Apple)and EverythingElse (txt_EverythingElse) and Total (txt_Total)

    I am trying to write something that stuffs the sum of txt_Total if customerid = 1
    for txt_Apple.
    Also to stuff the sum of txt_Total if customerid <>1 for txtEverythingelse.

    Am I going in the right direction and if so, what would be the correct syntax?
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #6

    Nov 29, 2007, 04:11 PM
    I'm not quite clear on your table structure here. Does it look like this:

    PrintDate
    CustomerID
    Amount

    If so, there are a few ways to do this. Probably a Union query would be easiest. First you create two group by queries. Both Grouping by Date and summing the amount. In one query you add a WHERE column to filter for CustomerDI =1 In the other CustomerID <> 1. Then Union those tow like this:

    SELECT PrintDate, SumOfAmount AS AppleTotal, 0 AS NonAppleTotal
    FROM qryApples

    UNION SELECT PrintDate, o AS AppleTotal, SumOfAmount AS NonAppleTotal
    FROM qryOthers;

    Finally, use the Union quey as the source of another query where you add the two total columns to get the overall total.
    IowaGuy's Avatar
    IowaGuy Posts: 23, Reputation: 1
    New Member
     
    #7

    Nov 30, 2007, 07:40 AM
    Hi Scott,

    My job table looks like this:

    JobID (pk)
    CustomerID
    PrintDate
    Documents
    Addl_Pages
    Wasted_Paper

    There are more columns, but I didn't want make this more confusing.

    In my report. I want the sum of (Documents,Addl_Pages,Wasted_Paper)
    This will show the total amount of paper was used to print.

    I also want to show in the report how much of the paper was used for Apex(Apple) and how much was for everyone else.

    I wrote a SQL statement, in what I tried to base on your outline.

    SELECT Job.PrintDate, sum(documents+addl_pages+wasted_paper) AS Total_Apex
    FROM Job
    WHERE CustomerID="1"
    GROUP BY Job.PrintDate, documents+addl_pages+wasted_paper
    ORDER BY job.printdate

    UNION

    SELECT Job.PrintDate, sum(documents+addl_pages+wasted_paper) AS Other
    FROM Job
    WHERE CustomerID <>"1"
    GROUP BY Job.PrintDate, documents+addl_pages+wasted_paper
    ORDER BY job.printdate


    It pulls back data, but I think there needs to be a way to differentiate between the 3 results I need.
    IowaGuy's Avatar
    IowaGuy Posts: 23, Reputation: 1
    New Member
     
    #8

    Nov 30, 2007, 07:42 AM
    Another thought, and I am not sure if its possible or not, but in the report properties, where the Record Source is at, is it possible to look pull more than 1 query?
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #9

    Nov 30, 2007, 08:31 AM
    Ok, this is why table structure was a key. You really have two thing going on here and you are trying to do the two things at the same time which isn't going to work well. My question to you is do you have show the breakdown on a daily basis. If so, I think there is a different way to go.

    qryApex
    SELECT Job.PrintDate, sum(documents+addl_pages+wasted_paper) AS Total_Apex
    FROM Job
    WHERE CustomerID="1"
    GROUP BY Job.PrintDate
    ORDER BY job.printdate

    qryOthers
    SELECT Job.PrintDate, sum(documents+addl_pages+wasted_paper) AS Total_Other
    FROM Job
    WHERE CustomerID <>"1"
    GROUP BY Job.PrintDate
    ORDER BY job.printdate

    qryDailySummary
    SELECT qryApex.PrintDate, Total_Apex, Total_Others, Total_Apex+Total_others As Daily_Total
    INNER JOIN ON qryApex.PrintDate = qryOthers.PrintDate
    FROM qryApex, qryOthers
    ORDER BY PrintDate

    Then use qryDailySummary as the Recordsource of your report.

    Disclaimer: The SQL is off the top of my head and untested, but it should give you an idea, I suggest using the query builder to build each query.

    And no, you can't have more than one object as a Recordsource.
    IowaGuy's Avatar
    IowaGuy Posts: 23, Reputation: 1
    New Member
     
    #10

    Nov 30, 2007, 09:55 AM
    Nice!

    Thanks!

Not your question? Ask your question View similar questions

 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

Where's the Date of last activity on my Equifax report? [ 1 Answers ]

Just got my Equifax report & It's broken up into a bunch of sections. The Credit Information section contains a date of last activity field, along with the following explanation in the header: The Collection Accounts section does not have a date of last activity field, but has the...

To-date total based on current date [ 1 Answers ]

:confused: I am trying to create a forumula that will give a cumulative or "to-date" total that will exclude future months from the total. i.e. ithe point in time is August and I need to show a to-date number Through August only even though September and October are included in The...

F-1 to H-1, what's the start date of H-1? Should be approval date or visa issue day? [ 3 Answers ]

As I mention before, I changed from F-1 to H-1 last year. I got my H-1 status in Feb 2004, but I went to my home country to get the H-1 visa stamp in August 2004. So I would like to know for counting my H-1 status, is it the date that USCIS (the Immigration) approved (which is Feb 1st), or the...


View more questions Search