Results 1 to 14 of 14
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Apples & Oranges (A2K)

    As always, seemingly simple.

    =Sum(IIf([MN]=1 And [Paid]="1",1,0))

    MN is a numeric field and paid is an alpha field and if both are equal to 1 in their own worlds, I want to add 1 to a counter.

    Needless to say I'm getting an ERROR or I wouldn't be bothering you.

    Appreciate any help,
    Cheers,
    Andy

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Apples & Oranges (A2K)

    Andy,

    How is your equation being used. In a query, report, code, or other means and what event is happening to trigger it to occur? Just need to know so an answer can be provided.

    Are you trying to store the value of a counter in code or using a query to just count all records that meet this condition?
    Regards,

    Gary
    (It's been a while!)

  3. #3
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Apples & Oranges (A2K)

    Andy,

    I am going to assume that you did this in a query based on a table with the fields MN and Paid.

    Create a query on the table.
    Input the following in the Field Cell for the query
    Expr1: IIf([MN]=1 And [Paid]="1",1,0)
    Turn on totals and then change group to sum

    The query will return the sum as needed. Note - you will run into trouble if you have null values.

    HTH
    Regards,

    Gary
    (It's been a while!)

  4. #4
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Apples & Oranges (A2K)

    Gary,

    Thanks for responding so quickly and my apologies for not responding as quickly.

    The code: =Sum(IIf([MN]=1 And [Paid]="1",1,0))

    with [MN] being a numeric field in the table and [Paid] being a text field both happily reside within a text box on a form. Never one to leave well enough alone, [MN] used to also be a text field and the code:

    =Sum(IIf([MN]="1" And [Paid]="1",1,0)) worked perfectly.

    It was after I "improved" things that it decided to crash. Where have I gone wrong?
    Cheers,
    Andy

  5. #5
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Apples & Oranges (A2K)

    Andy,

    Now you have lost me.

    - Field MN, numberic field, resides in a table. Lets call it Table 1.
    - Field Paid, text field, resides in a table. Is this true and what is the table name? Would it also be Table 1?
    - On the form, is their one text box for each field?
    - On the form, is their another textbox holding the value for the sum statement or what is being set equal to the sum statement?

    You say the code =Sum(IIf([MN]="1" And [Paid]="1",1,0)) worked perfectly. Where does this code reside? In another textbox upon the execution of an event from what control source?

    Are you saying that if MN (whatever that is) is a 1 and you change the textbox holding Paid to a 1 then the equation is triggered and fills another textbox?

    Sorry, but I really can't figure out what you are trying to do.
    Regards,

    Gary
    (It's been a while!)

  6. #6
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Apples & Oranges (A2K)

    Hi Poor Gary,

    As you've noticed, I have an ability of turning a simple question into a totally confusing issue. Let's take it from the top:

    Table 1: contains the fields

    [MN] Numeric
    [Paid] Text

    Form 1: contains 1 text box in which resides the following code as its data source

    =Sum(IIf([MN]=1 And [Paid]="1",1,0))


    The theory is for every record in the table in which [MN] is equal to 1 and [Paid] is equal to '1" I want to add 1 to the text box so that the total is displayed.

    So, if there are 9 records in the table in which [MN] = 1 and in the same 9 records [Paid] = "1", the text box will display, are you ready for this, 9

    I cannot think of any other way of explaining this. I do appreciate your patience. The only point I was trying to make earlier was that when, prior to my 'improving' the system, [MN] was a text box and the formula I was using at that time worked. There appears to be some problem in combining a numeric field and a text field within the same calculation.. So, again, the real field we are trying to make work is:

    =Sum(IIf([MN]=1 And [Paid]="1",1,0))
    Cheers,
    Andy

  7. #7
    Star Lounger
    Join Date
    Jun 2001
    Location
    Christchurch, New Zealand
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Apples & Oranges (A2K)

    Hi Andy,

    I just set up a wee test in a db. Using just a table and query, the query returns the results correctly: (test1 as nbr, test2 as txt, x being your formula) I am correct this is what you are expecting also?

    Test1 Test2 x
    0 0 0
    0 1 0
    1 0 0
    1 1 1

    Have you tried this test in just a query in your db to see if it works?? Is the form is this being used a single page or continous?...

    Cheers
    Tony

  8. #8
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Apples & Oranges (A2K)

    Andy,

    Your last explanation was quite clear.

    Going back to a prior post, create a query as follows:

    Create a query on the table.
    Input the following in the Field Cell for the query
    Expr1: IIf([MN]=1 And [Paid]="1",1,0)
    Turn on totals and then change group to sum

    The query will then return the correct value you want.

    Create a new form and base the form on the query you created.
    Select, in this case, Expr1 and drag it onto the form.
    When you open the form, the textbox will have the result of the query.

    OR if using the textbox and dont want to create a query you will need to use a function such as DSum.

    HTH
    Regards,

    Gary
    (It's been a while!)

  9. #9
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Apples & Oranges (A2K)

    Andy,

    Have you been able to solve your problem? If not, post back.
    Regards,

    Gary
    (It's been a while!)

  10. #10
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Apples & Oranges (A2K)

    Gary,

    Sorry for the delay in getting back to you. The problem, albeit simple, is not yet solved and I'll take the responsibility for that. I want to pursue a couple of other options, before I belabour you with any more details. I have to, for my own peace of mind, have this work within a text box like it always did prior to my 'improving' it.
    Who would have thunk that changing:

    =Sum(IIf([MN]="1" And [Paid]="1",1,0))

    to

    =Sum(IIf([MN]=1 And [Paid]="1",1,0))

    could cause so much bloody trouble

    Andy
    Cheers,
    Andy

  11. #11
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Apples & Oranges (A2K)

    Andy,

    Try the following in your textbox:

    <pre>formatted text=DSum(1,"table1","<table1>.[mn]=1 and <table1>.[paid] ='1'")</pre>



    The assumes fields MN and Paid are in Table1. MN is numeric and Paid is text
    This basically sums the number 1 whenever MN = numeric one and Paid is text 1.

    HTH

    Post edited to add pre and post tags to align data
    Regards,

    Gary
    (It's been a while!)

  12. #12
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Apples & Oranges (A2K)

    OK lets try this again. See the attached file for the correct format as I cant get this to work.
    Attached Files Attached Files
    Regards,

    Gary
    (It's been a while!)

  13. #13
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Apples & Oranges (A2K)

    Gary,

    I am about to let you return to your normal life. There is good news and there is weird news. The good news is your formula, over which you obviously spent a lot of time, works perfectly. The really, really, weird news is that my original formula =Sum(IIf([MN]=1 And [Paid]="1",1,0)) also works perfectly in AN EARLIER VERSION of my database. Somewhere in there, there is some sort of corruption that killed my results. And that is why my hair grows ever thinner and ever whiter. It's now a case of backtracking to my original database so that I can make my version work just out of pure, bloody spite. Your version, however, shall go into my coffer of coding gold to be kept safely for ever more. Thanks again, so very much for taking the time that you did,

    Andy
    Cheers,
    Andy

  14. #14
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Apples & Oranges (A2K)

    Andy,

    Glad I could help and I hope you figure out what the issue is so you don't get <img src=/S/cranky.gif border=0 alt=cranky width=18 height=25>
    Regards,

    Gary
    (It's been a while!)

Posting Permissions

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