DCount question

[zookeeperbobbie]

Hi- first I want to thank everyone who has helped answering my questions in the past. in comparison to you all- i'm still for the most part a newbie, For those of you who have sent me the links to use access I appreciate it and yes i have been reading...

However I am stuck on a problem and i'm sure that someone will look at it in 2 seconds and say. Oh yeah thats an easy fix...I have been trying to work on this since 6:00 this morning and I still cant fiqure this out.

I;m setting up a query that will count the number of times shortage appears in a table, and across three columns. This is what i;m currently using-but getting errors:

field:Expr1: DCount([Problem 1] & [Problem 2] & [Problem 3] & [Problem 4],"Problems","shortage")
Table: Problems
Total: Group by
Show yes
Criteria:

Thanks for your help in advance....

 

[zookeeperbobbie]

Hi I was starting to do this last night but my isp went down.
I was testing some things out and I can across somthing that may work-
I my sub form that lists the problems by vendors I created a new unbound box, i then entered this formula:

=Count(IIf([Problem 1]+[Problem 2]+[Problem 3]+[Problem 4 ="Shortage",1,0))

well its working perfect except it still is counting all records per vendor. What am i doing wrong? I need it to only count records where shortage appears... I dont understand why this is such a problem.

I;m sure i;m not the only person who has ever has this sort of issue...
Thanks!!! I think all i need is a formula that will count the # of times the criteria appears across columns.

And i am going to be working on that database upload tonight..
(I hope that I am not going to be blacklisted for agravating everyone so much- believe it or not I think I am learning quite a bit through my trial and errors and everyone's advice)

Thanks
Bobbi

 

[VicRauch]

Bobbi,
Try this count code. I don't see any way for the one you posted to work. What the one you posted is doing, is giving you a 0 to count, and that is why you are getting a count of all your records. This one will put all 4 problems into one string, look in that string for the word "Shortage", and if it finds it give you a 1, otherwise give you a zero (0). By doing a sum, rather than cound, you should get the number of records that have "Shortage" in any one of the Problem columns.
Here is the code:
=Sum(IIf(Instr([Problem1]+[Problem2]+[Problem3]+[Problem4], "Shortage")>0,1,0))

 

[zookeeperbobbie]

Hi, I posted this code exactly as you have it- But i dont think its working correctly.

for example one record has shortage on each of the problem columns (4)

Its only counting 1-

Bobbi

 

[VicRauch]

Bobbi,
I'm sorry, I did not understand that you could have Shortage in EACH column for one record, and if so, to count how many times it was there. So, here is the amended code:

=Sum(IIf(Instr([Problem1], "shortage")>0,1,0) + IIf(Instr([Problem2], "shortage")>0,1,0) +  IIf(Instr([Problem3], "shortage")>0,1,0) +  IIf(Instr([Problem4], "shortage")>0,1,0))