VBA - label to display total of column in table based on two checks

[gdesruis]

I am looking to generate a total number of a given column based on two criteria.

I would like a label (or textbox if necessary) to display a given total of "Active" devices based on a given month.

The table name is "Blackberry" and the specific columns I would like to check would be "Activated?" and "Registration Date". I'm not sure if I should be using a dsum or dcount and the error i keep getting is a type mismatch.

An active device would have the text value of "Yes

This was the code I have tried, not sure if i'm on the right track at all, but any pointers in the right direction would be very appreciated.

Dim advalue As Integer
advalue = DCount("[Activated?]", "Blackberry", "[Activated?]=yes" And "[Registration Date] > #01/01/2000#")
lblad.Caption = advalue

Garrett

 

[JHB]

Try the below:

advalue = DCount("[Activated?]", "Blackberry", "[Activated?]=yes And [Registration Date] > #01/01/2000#")
or
advalue = DCount("[Activated?]", "Blackberry", "[Activated?]=-1 And [Registration Date] > #01/01/2000#")

 

[gdesruis]

thanks, that seemed to provide a count for me instead of a type mismtach, although the criteria filters do not seem to be working, it is just providing a total.

 

[JHB]

it is just providing a total.

Could it be because it is selecting dates from 13 years?
Try changing the year to see if you gets an other result.
Else create a query in the QBE window with the criteria, to get the right syntax.

 

[gdesruis]

By changing the year column it does provide an accurate check based on the year, but it is not considering the [Activated?] column if the value is "no".

Should I try your query method and see? or is there a simple piece i'm missing

thanks for following up.

 

[gdesruis]

I had the [Activated?] column set as text. Upon switching to Yes/No the code worked flawlessly.

Thanks!