Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2002
    Posts
    22

    Unanswered: Acces Help Please.

    I have a report that uses IIF statment and returns a 1 or 0 based on the statement. I want to be able to count the number of 1's that where returned from the IFF statement using another text box, but I am unable to. what comes back is either a 0 or +_03

    Here is my IIF Statement : =IIf([Datecount]<16,"1","0")
    This statement has been tested and works fine. I simply can not get another text box to count the number of 1's that are returned. I tried the count function and it does not work. Please help, let me know what I am missing or doing wrong

    Thank you for taking the time to read all this and help if you can...

    Mojo-jo

  2. #2
    Join Date
    Sep 2002
    Location
    Brooklyn Heights, NY
    Posts
    2
    I think it would help greatly if you stated your problem a lil more clearly and descriptively.
    I'm not sure what you mean by "Count the number of 1's returned"?

  3. #3
    Join Date
    Sep 2002
    Posts
    22

    Access Help

    Originally posted by Jimboyoo
    I think it would help greatly if you stated your problem a lil more clearly and descriptively.
    I'm not sure what you mean by "Count the number of 1's returned"?
    ok thank you for taking the time to read this.... What I mean by counting the number of 1's that are returned is: my IIF statement is set up in such a way that is will return either a zero or a one (0 or 1).

    The problem I am having is I am unable to count the number of one's (1) that have been returned from the IIF Statement using a text box. All of this is developed on the report using expression builder.

    Please let me know if there is more information that I can give you that would be of help.

  4. #4
    Join Date
    Sep 2002
    Location
    Coventry, Warwickshire, UK
    Posts
    74
    Why not use something like

    Sum(IIf([Datecount]<16,"1","0"))

    That will effectively count the number of 1s.
    Roger Hampson
    XI - ecs (UK) Ltd

  5. #5
    Join Date
    Sep 2002
    Posts
    22

    Access Help

    Originally posted by xiecsuk
    Why not use something like

    Sum(IIf([Datecount]<16,"1","0"))

    That will effectively count the number of 1s.

    Thank you I will try that.... I knew it would be something simple.

  6. #6
    Join Date
    Sep 2002
    Posts
    22

    Re: Access Help

    Originally posted by mojo-jo
    Thank you I will try that.... I knew it would be something simple.
    OK I tried it and it did not work, the message I got was there are one to many ( in the expression

    but when I took out the parenthisis I recieved a syntax error. to give you more info on the database:

    I have two dates Date A and Date B

    Date B can not be no more than 15 days past Date A
    =IIf([Datecount]<16,"1","0")
    If Date B is under 15days then my texbox called compliance returns a 1
    If Date B is over 15days then it returns a 0.
    All I want to do is count the number of 1's that have been returned to the textbox called compcount..
    Keep in mind all of this is done at using expression builder. Date A and B are fields in the table but compcount is not nore is the text box I am using to count the number of returns

    Thanx again for all the help

  7. #7
    Join Date
    Jul 2002
    Location
    Romania
    Posts
    122
    Place a hidden text box named RunningChecked beside your check box.
    Set its ControlSource Property to:
    = [CheckBoxName]
    Set its RunningSum property to OverAll

    In the report footer, create a visible text box and set its control source to:

    =[RunningChecked]

    Open your report and...VOILA


  8. #8
    Join Date
    Sep 2002
    Location
    Grand Rapids, MI
    Posts
    2

    Re: Acces Help Please.

    This should be pretty simple.
    you said you're in a report, so make sure the source of that report is a Query and not a table.
    In the query that supplies the report with data, use your IIF statement in the query. It will then be supplied to the report as a field.

    Then in a report footer (or a group footer), create a unbound text field that will have the following: =Sum([NameOfField])
    where [NameOfField] is the field that is linked to the IIF statement in the query.

    hope that makes sense!



    Originally posted by mojo-jo
    I have a report that uses IIF statment and returns a 1 or 0 based on the statement. I want to be able to count the number of 1's that where returned from the IFF statement using another text box, but I am unable to. what comes back is either a 0 or +_03

    Here is my IIF Statement : =IIf([Datecount]<16,"1","0")
    This statement has been tested and works fine. I simply can not get another text box to count the number of 1's that are returned. I tried the count function and it does not work. Please help, let me know what I am missing or doing wrong

    Thank you for taking the time to read all this and help if you can...

    Mojo-jo

  9. #9
    Join Date
    Sep 2002
    Posts
    22

    Re: Acces Help Please.

    Originally posted by SirRandall
    This should be pretty simple.
    you said you're in a report, so make sure the source of that report is a Query and not a table.
    In the query that supplies the report with data, use your IIF statement in the query. It will then be supplied to the report as a field.

    Then in a report footer (or a group footer), create a unbound text field that will have the following: =Sum([NameOfField])
    where [NameOfField] is the field that is linked to the IIF statement in the query.

    hope that makes sense!
    I thank you all for the help... I have tried all of the solutions with no luck... I have some more info to pass along if you still wish to help..

    In my query I only have date A and Date B. In my report is where I am doing all the calculations, the IIF Statements and such

    SirRandall suggested stated that I should make sure that my report is binded to the query and it is.

    Then SirRandall stated that I should us the IIF statement in the query, this would require me to add a filed to my table, since you can not add a report field to a query.

    Since I did not want to do this I choose to do all of data manipulation at the report level, to aviod having to add any extra fields to my tables, I have over 30 tables just to let you know.

    On my report I am using these statements, DateDiff, IIF. The datediff is held in a text box called datecount2, My IIF Statement is held in a text box called compliance. My IIF statement returns a zero or a 1 back to complaince. Everything after that point is where I am having problems, I only wish to count the number of one's returned to the complaince text box.

    I have tried to add a text box called compcount at the detail section of the report, set the source =[compliance] property count over group.

    Then I added a text box called compcount2 to the footer and set the source =[compcount] when I run the report I get 0 all the way down the report and in the footer I get a 1

    I hope this explains things a little better thanks SirRandall again for your help as well as Danny Y, xiecsuk, and Jimboyoo for the insight, input, and support...

  10. #10
    Join Date
    Sep 2002
    Location
    Grand Rapids, MI
    Posts
    2

    Re: Acces Help Please.

    <<Then SirRandall stated that I should us the IIF statement in the query, this would require me to add a filed to my table, since you can not add a report field to a query. >>

    Actually, what I suggested is placing your IIF statements in the query. This does not require you to add more fields to the tables, you're just adding a calculated column to the query, and that column is the result of the IIF calculation.

    for example:
    Table1 only has FieldA and FieldB. Both are numeric
    Query1 is based solely on Table1, but has 3 columns:
    [FieldA]
    [FieldB]
    [Expr1]

    [Expr1] is a expression that does NOT actually exist in the table. When entering the [Expr1] field onto the query grid, it will look something like this:
    Expr1: =IIF([FieldA] > 16, 1,0)

    When this QUERY is ran, three fields will be output, [FieldA], [FieldB], and [Expr1]

    For each row, if FIELDA > 16, then EXPR1 will be 1, otherwise it will be 0.

    In place of the IIF statement, you can also use DATEDIFF, or any other functions built into Access. Likewise, you can use both simutaneously, in separate columns in the query of course.

    Doing this keeps the math in the query, and relives the REPORT of having to do the calculations.

    Once your query is set, the report will see EXPR1, just as it sees FieldA and FieldB. To the report, there is no difference between a field that comes directly from a table, and a field that comes from a calculation.

    You should be able to use the EXPR1 field, along with the IIF function, to have the query return a 1 or a 0, based on your needs. The query only returns the value to the report. The report never even sees the IIF calculation.

    <<I have tried to add a text box called compcount at the detail section of the report, set the source =[compliance] property count over group.>>

    Lastly, in the DETAIL section of your report, place EXPR1 on the report, and call it EXPR1. Then at the Report Footer, place your unbound text box, and rather than set it to pull data from a field, it will be a function based on the EXPR1 field that you already put on your report.
    For example: txtSumExpr1 (object name)
    Control Source is '=Sum([expr1])'

    Remember, EXPR1 is returning a 1 for each record where your calculation is true, so if you summarize that field at the end of the report, it's really just counting the number of times your calculation was true.

    A report can perform a calculation, but only if the factors for that calculation are returned directly from the query. You cannot create a calculation on a report, where one of the factors is the result of another calculation on that same report.

    Good luck. This is about all the help I can offer. Experiment, you'll get it.

  11. #11
    Join Date
    Sep 2002
    Posts
    22
    Thanks I will tinker with it till I figure it out

  12. #12
    Join Date
    Sep 2002
    Posts
    22

    I got it

    Just wanted to say I figured it out but it was my IIf statement that was stopping me.. OK my IIF statement was IIF([datecount]<16,"1","0")

    the error I made was placing quotes around the 1 and zero, when I attempted to sum datecount the sum command thought I the 1 and zero was text instead of a numerical number. When I removed the quotes from the 1 and zero, it counted the number of 1's I had with out a problem,,, thanks to all you especially SirRandell for all the help.. I learned something new, and that is the most important..


    Thanks every one. love this board, and will contribute as often as possible.

Posting Permissions

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