text box value on form based on 2 criteria and 6 fields

[chromachem]

Please take a quick look at the attached PDF. A picture is worth.....

We want to populate a field with the sum of the values in 6 other fields. The issue:

1) Some of the fields may or may not contain text.
2) We only want to add the values from the other fields if they exceed a certain number.

Please take a peek at the PDF - it is way more explanatory than I can be.

We have no problem writing code to do what we want in VBA. Many years of experience with Excel VBA. Access is a whole new paradigm for me. I am learning ACCES programming as I go. Googling and using reference books.



I tried to figure a way to enter the info needed into the control source. So many ifs as to be ungainly.

Any help with a straight forward way would be great. I could also figure out how to get the needed value from an SQL statement. Just no clue how to get the value from an SQL statement into the field.

I'm sure there is more than one way to figure out how to accomplish what we need. Just trying to figure a reasonable way to approach the problem.

As always, thanks for taking a look.

[June7]

Try:

Sum(IIf(IsNumeric([Result]),[Result],Null))

Works in query or textbox in report or group header/footer.

[pbaldy]

I agree with June7 though I would use 0 instead of Null. If I understand your requirement you'd want a second IIf() in place of [Result] that tested that value against the MRL value. Along the lines of

Sum(IIf(IsNumeric([Result]),IIf([Result] > [MRL], [Result], 0),0))

[chromachem]

I'm confused with the responses from June 7 and pbaldy. I'm just geussing here - [Result] would be the result from adding the 6 fields?

In case i'm not being clear (not the first time), please see the pdf just attached (2nd attachement). I think it may be a lot clearer.

Some fields will contain ND. Some fields will be less than MRL.

We need to sum fields that are not ND and are greater than MRL.

[pbaldy]

Result came from your PDF. If that's not the field name, change it to whatever the field name is.

[June7]

Result is whatever field you want to sum. Since only had the PDF as guide, that's what I thought the field name was. However, now I see there are 2 Result columns.

Use whatever is your actual field name.

On second thought, you keep saying "fields", yet the report shows the data to sum as records (rows). Aggregate functions aggregate records, not fields.

Suggest you provide db for analysis. Follow instructions at bottom of my post.

[chromachem]

I tried to export the report, but was unable to.
I can't send the DB. It is populated and has data on the web. Many tables are used to produce the report.

I'll try once more to explain.
This is a laboratory report for 6 compounds. The concentration detected for each compound is in the column directly to the right of it's synonym.


The results for the first compound are stored in a textbox called txtPFOS
The results for the second compound are stored in a textbox called txtPFOA
The results for the third compound are stored in a textbox called txtPFHxS
The results for the fourth compound are stored in a textbox called txtPFNA
The results for the fifth compound are stored in a textbox called txtPFHpA
The results for the sixth compound are stored in a textbox called txtPFDA

There is another text box call txtSumof6Compounds. This is the text box where we want to store the sum of the results that are in the six text boxes listed above.
We need to ignore a text box if it contains ND (not detected). We need to ignore a text box if it contains a number < the corresponding MRL. For the sake of this discussion - call the MRL 2. So:

We need to sum the column that contains the SIX results where the result for that compound <> "ND" and if the result is greater than 2.
Bearing this in mind, please take a look at the second attachment.

Please look at the second example PDF.

I manually typed in the correct sum for the txtSumof6Compounds text box. The correct number (25) for the text box is the sum of the results for PFOA (15) + the result for PFNA (10). The compounds that are not detected (ND) are ignored. Results for PFOS(1.55) and PFHxS(0.885) are also ignored because the values are < 2.

I know the following code does not use the correct syntax. We're looking for a control source for txtSumof6Compounds something like the following. The code is only to demonstrate what we are attempting to do.

=Sum(IIF(txtPFOS <> "ND" and txtPFOS > 2) + (IIF txtPFOA <> "ND" and txtPFOA >2) + (IIf( txtPFHxS <> "ND and txtPFHxS >2) + (IIf(txtPFNA <> "ND" and txtPFNA >2) + (IIf txtPFHpA <> "ND" and txtPFHpA >2) + IIf(txtPFDA <> "ND" and txtPFDA >2))

Thanks for taking a look. Hopefully I have explained the problem in a way that makes it somewhat clear.

[June7]

I did look at both pdfs. As I said, Sum is an aggregate function and aggregate functions aggregate records not fields.

Remove Sum from the expression. Just add the fields with normal arithmetic using correct IIf() syntax.

=IIf(txtPFOS <> "ND" AND txtPFOS > 2, txtPFOS, 0) + IIf(txtPFOA <> "ND" AND txtPFOA > 2, txtPFOA, 0) + IIf(txtPFHxS <> "ND" AND txtPFHxS > 2, txPFHxS, 0) + IIf(txtPFNA <> "ND" AND txtPFNA > 2, txtPFNA, 0) + IIf(txtPFHpA <> "ND" AND txtPFHpA > 2, txtPFHpA, 0) + IIf(txtPFDA <> "ND" AND txtPFDA > 2, txtPFDA, 0)

[chromachem]

Thanks to June 7th, we see where we were off. Classic case of overthinking the problem.

This is the working code to get the sum.

=IIf([txtPFOS]<>"ND" And [txtPFOS]>2,[txtPFOS],0)+IIf([txtPFOA]<>"ND" And [txtPFOA]>2,[txtPFOA],0)+IIf([txtPFHxS]<>"ND" And [txtPFHxS]>2,[txtPFHxS],0)+IIf([txtPFNA]<>"ND" And [txtPFNA]>2,[txtPFNA],0)+IIf([txtPFHpA]<>"ND" And [txtPFHpA]>2,[txtPFHpA],0)+IIf([txtPFDA]<>"ND" And [txtPFDA]>2,[txtPFDA],0)

To finish off, we need to either put "ND" in the txtbox for txtSumof6Compounds or round the sum (if there is a sum) to 3 significant digits.
Note - we use a function I think I found on this site to put numbers into sig figs. Called sigrnd.

I got as far as figuring out how to put ND or the word Round in the txtSumof6Compounds text box:

=IIf((IIf([txtPFOS]<>"ND" And [txtPFOS]>2,[txtPFOS],0)+IIf([txtPFOA]<>"ND" And [txtPFOA]>2,[txtPFOA],0)+IIf([txtPFHxS]<>"ND" And [txtPFHxS]>2,[txtPFHxS],0)+IIf([txtPFNA]<>"ND" And [txtPFNA]>2,[txtPFNA],0)+IIf([txtPFHpA]<>"ND" And [txtPFHpA]>2,[txtPFHpA],0)+IIf([txtPFDA]<>"ND" And [txtPFDA]>2,[txtPFDA],0))=0,"ND","Round")

I tried replacing "Round" with code for summing, and keep getting errors.
I imagine I want to use the code for summing in the first code snippet above to replace "Round".

What I would really like to do is something like

sigrnd(txtSumof6Compounds,3)  'of course a circular reference.

So...How do I replace "Round" with the sum rounded to 3 sig figs (using the sigrnd function)

[June7]

Do the first calc in a textbox then in another textbox reference the first. You can set it as not visible.

=IIf(txtSumof6Compounds = 0, "ND", sigrnd(txtSumof6Compounds, 3))

[chromachem]

Perfect. I was being stubborn and trying to do to much with one line of code. Splitting the sum into one text box (not visible) , and putting the result in a visible text box is much simpler.

Thanks for all your help!!!!!!!!!!!