# Thread: Apples & Oranges (A2K)

1. ## 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,

2. ## 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?

3. ## 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

4. ## 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?

5. ## 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.

6. ## 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))

7. ## 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. ## 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

9. ## Re: Apples & Oranges (A2K)

Andy,

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

10. ## 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

11. ## 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

12. ## Re: Apples & Oranges (A2K)

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

13. ## 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

14. ## 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>

#### Posting Permissions

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