Add Soldiers who are promotable only =Count (IIf[x]="y" and [z]="a")

[Lucas Schwartz]

I need to do something like

=Count(IIf([Rank]="SPC" and [Promotable]=-1 )

I also need to do

=Count(IIf([Rank]="SPC" and [WLC]=-1 )

Promotable is a Check box
Rank is an exact selection from a drop down box.
I'm assuming that -1 = the box is checked. Im new to reports in access Thanks

[Oralloy]

Lucas,

How messy do you want your nested queries to be? I'd suggest using two queries, just to make life simple. The first would be:

SELECT COUNT(*)
  FROM soldier AS ss
  WHERE (ss.Rank = "SPC")
        AND ([Promotable]=-1);

And the second:

SELECT COUNT(*)
  FROM soldier AS ss
  WHERE (ss.Rank = "SPC")
        AND (ss.WLC = -1);

If they absolutely have to be in one query, we can go into that, too.

Good Luck!
Oralloy

[Lucas Schwartz]

I need it to display in a report i was using unbound text boxes to do this. Can i still do this with the method above and how would i insert it? I have made a report that hides all the soldiers information and is one page that displays all the stats i want like the count of each rank of soldiers using :=Count(IIf([Rank]="PV1",0)) ext

[Lucas Schwartz]

I should have specified it was for an report

[Rabbit]

Use the DCount function.

[Lucas Schwartz]

Ill research it never heard of it. Hope its easy! lol

[Rabbit]

Pretty easy, DCount("fieldna me", "table name", "where condition").

Fieldname can be "*" and the where condition is optional.

[Oralloy]

Rabbit, thanks. I keep forgetting to use the DCount() function.

Lucas, Luck!

[Lucas Schwartz]

I'm confused on how to evaluate two items from one record using this:
=DCount("WLC",P ersonnel Data", ?

I need it to Display all that are checked WLC and = "SPC" in rank field

[Rabbit]

Well, I think it was just a miscommunicatio n from the first post. If it was in SQL, I wouldn't use DCount either, but if he's trying to use an unbound textbox in a form, then that's one of the few situations when I would settle for DCount.

[Lucas Schwartz]

=DCount([WLC],"Personnel Data","WLC=-1") returned 42 entires so i got that part but now i need to make it just show records that WLC is checked and the rank = "SPC"

[Oralloy]

Try: =DCount([WLC],"Personnel Data","(WLC=-1) AND Rank='SPC'")

[Lucas Schwartz]

How would i modify this to add say
=SPC & WLC checked & promotable is checked?

[Oralloy]

Pretty much exactly the way you wrote it.

Try: =DCount([WLC],"Personnel Data","(Rank = 'SPC') AND (WLC = -1) AND (promotable = -1)")

[Lucas Schwartz]

ah ok got it! What does the first part [WLC] represent?