User Profile

Collapse

Profile Sidebar

Collapse
penjddj1
penjddj1
Last Activity: Jan 31 '07, 02:32 PM
Joined: Jan 25 '07
Location:
  •  
  • Time
  • Show
  • Source
Clear All
new posts
  • penjddj1
    replied to Grouping with Subreports
    in Access
    Here is the SQL code as requested for a False result:

    Code:
    TRANSFORM Count(KPI.[KPI Record #]) AS [The Value]
SELECT Format([Return from Trip],"yyyy") AS Expr2, KPI.KPI2, 
Count(KPI.[KPI Record #]) AS [Total Of KPI Record #]
FROM KPI
WHERE (((KPI.KPI2)=False) AND ((KPI.[Return from Trip]) Is Not Null))
GROUP BY Format([Return from Trip],"yyyy"), KPI.KPI2, KPI.[Return from Trip]
    ...
    See more | Go to post
    Last edited by penjddj1; Jan 31 '07, 09:19 AM. Reason: Left out a portion of reply

      Leave a comment:

    • penjddj1
      replied to Grouping with Subreports
      in Access
      It's probably my bad explanation, so I'll try again.

      I have two tables:

      Table:Work Order
      Fields:
      Job# (Primary Key)
      Date Task Requested (Date)
      KPI Required (Yes/No checkbox)

      Table:KPI (Key Performance Indicators)
      Fields:
      KPI Record # (Primary Key)
      Job# (From Work Order Table)
      Respond to Customer (Date)
      KPI1(Yes/No checkbox)
      Return from...
      See more | Go to post

        Leave a comment:

      • penjddj1
        replied to Calculated Control (Text Box) in a Report
        in Access
        When I use Me!, I receive a request for a parameter value for Me when I run the report. Perhaps Access doesn't like the mix of brackets/non-brackets - but that is just my guess. (I could also be typing something incorrectly, but can't see the tree for the forest as it were.)...
        See more | Go to post

          Leave a comment:

        • penjddj1
          replied to Calculated Control (Text Box) in a Report
          in Access
          I believe Me! did not work because I have spaces in my subreport titles (although maybe I could have just used _)....
          See more | Go to post

            Leave a comment:

          • penjddj1
            started a topic Grouping with Subreports
            in Access

            Grouping with Subreports

            I have created a report that displays a count for five controls for each month. The controls are yes/no boxes, so each control can have a value of true, false or null. I created two subreports based on crosstab queries for each control - one for true and one for false/null. I would like to create a result like the following in my report:

            Year1
            Control1T CountJan, CountFeb,...
            Control1F CountJan, CountFeb,...
            ...
            See more | Go to post
            • penjddj1
              replied to Calculated Control (Text Box) in a Report
              in Access
              Ok, I tried both of the examples above, and found that the following code makes the equation work:

              Code:
              =Nz([Reports]![Main Report]![Subreport1].[Report]![Jan],0)
/IIf(Nz([Reports]![Main Report]![Subreport2].[Report]![Jan],0)
+Nz([Reports]![Main Report]![Subreport1].[Report]![Jan],0)=0,
Nz([Reports]![Main Report]![Subreport1].[Report]![Jan],1),
Nz([Reports]![Main Report]![Subreport1].[Report]![Jan],0)
              ...
              See more | Go to post

                Leave a comment:

              • penjddj1
                replied to Calculated Control (Text Box) in a Report
                in Access
                Ok, I will try that on Monday (they've sent us home for the weekend) and let you know how it works. I do really appreciate your help.
                See more | Go to post

                  Leave a comment:

                • penjddj1
                  replied to Calculated Control (Text Box) in a Report
                  in Access
                  Ok, I've tried this and it does give me a 0 result if both controls are null, but gives me incorrect percentages if one or both are not null (basically acts if the denominator does not exist.)

                  Because you asked why I added the Jan field from subreport 1, I thought it might be useful to break the problem down into the simplest form possible. Therefore, this is what I'm trying to do:

                  I have two reports, say A and B,...
                  See more | Go to post

                    Leave a comment:

                  • penjddj1
                    replied to Calculated Control (Text Box) in a Report
                    in Access
                    First, thank you for your help with this. I tried your suggestion, but I now receive an "Enter Parameter Value" request for the IIF statement. I've tried changing the arguments around, but to no avail.

                    Also, I'm curious as to why the Nz Statements were taken out of the denominator given that SubReport1 could also be null, causing an error. I'm having a little trouble following the logic, but freely admit that I'm no...
                    See more | Go to post

                      Leave a comment:

                    • penjddj1
                      started a topic Calculated Control (Text Box) in a Report
                      in Access

                      Calculated Control (Text Box) in a Report

                      (Please forgive any misuse of terms in my description as this is my first post.)
                      I have created a text box (in a report) that is used to find a percentage based on two other controls. The calculation works fine unless there is a null value in both of the other controls. I would like to find a way to return either a 0 or bit of text (such as "N/A") whenever this situation arises. Currently, I receive the #Num! error.
                      ...
                      See more | Go to post
                      No activity results to display
                      Show More
                      Working...