I have a query which interrogates activities in an append query and counts stats up by week,
replacing if the week is in use, and adding if it is the first occasion.


I have various columns in the append query for counting the various category an activity can be in,
one that works perfectly well is:



In Testing:

This works fine.

I have another field in the query which records the percentage of completion of the activity. If it
is 100% complete i.e. the PerCentComp field in the underlying query is 100, I would like to count
that as well

I have tried various combinations like:

Sum(IIf([TFStatus]="In Testing" AND [PerCentComp] =100,1,0))


Sum(IIf([TFStatus]="In Testing" AND W[PerCentComp] =100W,1,0))


Sum(IIf([TFStatus]="In Testing" AND "[PerCentComp] ='"100"'",1,0))

But nothing appears to work.

Is this in fact imposible to do or have I just got the syntax wrong?

Thanks
J


--
--------------------------------- --- -- -
Posted with NewsLeecher v4.0 Final
Web @ http://www.newsleecher.com/?usenet
------------------- ----- ---- -- -

If [PerCentComp] is a text field maybe:

Sum(IIf([TFStatus]="In Testing" AND [PerCentComp] ="100",1,0))

If [PerCentComp] is sometimes Empty/Null, this would cause problems.
Maybe then you'd use the Nz function or IsNull test.


Example where boolInput could be True, False, or Null:

Public Function ReturnStatusIsCurrent(boolInput As Variant) As Variant

If Not IsNull(boolInput) Then
If boolInput Then
ReturnStatusIsCurrent = "Current"
Else
ReturnStatusIsCurrent = "Historical"
End If
Else

End If

End Function

On Jul 22, 12:22*pm, "christianlo... (AT) yahoo (DOT) com"
<christianlo... (AT) yahoo (DOT) com> wrote:
There's another possibility. I created a table. I stuck in "In
Testing", "In Testing[spc][spc][spc]", and "[spc]In Testing" and set
all 3 to 100, as well as some with and without null/blank values.
Those with spaces got trimmed when saved. So in these 3 recs I got
returned back 2. The one with the leading space was ignored. It
didn't matter whether or not the value was null or 100.

His first Sum() looks OK. I'd probably modify it to remove the
totals, create a column like IIf([PercentComplete]=100,1,0)) As
OneOrZero and see if I get a value in the column. When I do, change
it back to a GroupBy and add the SUM() around it.

schwallyhoo <sumone (AT) hotmail (DOT) com> wrote in
news:d6Sdnf3Zi6FFMLTTnZ2dnUVZ8oCdnZ2d (AT) giganews (DOT) com:

Access often stores percentage values as 0.00 to 1.00
and displays them as 0% to 100% That may be part of your problem.

--
Bob Q.
PA is y I've altered my address.

schwallyhoo wrote:
The biggest problem is you have not defined what "doesn't work" means. Do
you get error messages? If so, what are they? Do you get incorrect results?
If so, show us some sample data, followed by expected results, followed by
the results you obtained.

The errors are around syntax, apart from when I us the first option, which just returns 0.

The PerCentComp column in the query contains from 0 - 100 as an integer, another way of doing the
math would be to use another column when I use the append query using IIF then I would just use the
SUM(IIF as in all the other fields (there are more categories that the query sums)

J


--
--------------------------------- --- -- -
Posted with NewsLeecher v4.0 Final
Web @ http://www.newsleecher.com/?usenet
------------------- ----- ---- -- -

schwallyhoo wrote:
Sorry, I don't understand what you're talking about. Show us some sample
rows in tabular form, followed by desired results in tabular form, followed
by the incorrect result from your query if you can get it to run without
error, that is. We need the exact wording of any errors you get, not just a
vague description.

"schwallyhoo" <sumone (AT) hotmail (DOT) com> skrev i en meddelelse
news:d6Sdnf3Zi6FFMLTTnZ2dnUVZ8oCdnZ2d (AT) giganews (DOT) com...
Not sure if I understand you correctly, but if you want to count the rows
that are either "In Testing" or 100% finished then you need to change your
IIf condition from AND to OR:

Sum(IIf([TFStatus]="In Testing" OR [PerCentComp] =100,1,0))