If all rows in a query = true

[lloydmav]

Hi,

Its been along while since I've worked with Access,

I've been using VB & SQL Server however I'm trying to repair an old database I built. So I would be very grateful for any help you could give me!

I have a query which brings back lots of results with different 'Action ID Values'.

Now I'm trying to put together a piece of code that checks to see if all the records in a query with a particular 'Action Plan ID' have a field 'Completed' = true.

The idea is if all 'Action Plan ID' (26) for example are completed i.e True then I can then give a msgbox stating this.

I started looking at some sort of DCOUNT but got a bit lost.

Any thoughts?

 

[Mile-O]

2 Dcounts would get you what you want.

One where you count all of the field meeting the requirement of the ActionPlanID and the Completed = True


The second DCount only has the criteria that ActionPLanID must be met.

You can then compare the two results. If they are equal then all are completed. If the former has less you can be sure that they are not all completed.


....And if it's greater , well, its beyond help!

 

[lloydmav]

Thanks Miles,

Thats a great suggestion, not sure how to go about writing that but I'll go away and do some research, and give it a go!

Thanks

 

[lloydmav]

I've given it ago Miles but I'm getting a Type Mismatch error

What do you think of the effort below?, does anything stand out that looks wrong?



-----------------------------------------------------------------------------

Dim var1 As String
Dim var2 As String
Dim actionplanid As String


actionplanid = 26

var1 = DCount("[Action Plan ID]", "[PersonalActionPlanQuery]", "[Action Plan ID]" = actionplanid And "[Completed]" = True)
var2 = DCount("[Action Plan ID]", "[PersonalActionPlanQuery]", "[Action Plan ID]" = actionplanid)

If var1 = var2 Then
MsgBox "All Tasks Matched"

Else
MsgBox "Not Matched"

End If

--------------------------------------------------------------------------



Appreciate the help Miles
Thanks

 

[crook]

lloydmav,

A suggestion for approaching your query...

Make a query with two fields.

One field is the primary key. Select "Count" in the total block. Primary keys should never be null, so this will tell you how many records you have in the table. Alternately, you can enter something like:
AllRecs: count([primary_key_field_name])
in the field block.

The second field holds your ActiveID field. Enter the following:
AllActiveIDs: sum(iif([action_ID_value_field] is null, 0, 1))
in the field block. This will effectively count how many times your ActiveID field has something entered in it.

Now just compare the numbers in both fields...

Here's something one step more complicated, but more useful. Make a query with one field - enter the following in the field block:

AllDone: iif(count([primary_key_field_name] > 0, iif(count([primary_key_field_name]) = sum(iif([action_ID_value_field] is null, 0, 1)), "All Done", "Not All Done"), "Query returned no records")

Hope this helps,
- crook

 

[lloydmav]

Ok I've managed to get it to work now. But the problem is I need to replace the value 26 in the dcount function with the variable actionplanid. Whenever I try to do this I get an error message Runtime error 2001 "You cancelled the previous operation." Help doesn't seem to be much Help! Anyone have any ideas with this. Is it possible to put a variable in the dcount criteria?

Cheers

----------------------------------------------------------------------------------

Dim var1 As String
Dim var2 As String
Dim actionplanid As String


actionplanid = 26
var1 = DCount("[Action Plan ID]", "PersonalActionPlanQuery", "[Action Plan ID] = 26 and [Completed] = True")
var2 = DCount("[Action Plan ID]", "PersonalActionPlanQuery", "[Action Plan ID] = 26")
If var1 = var2 Then
MsgBox "All Tasks Matched"

Else
MsgBox "Not Matched"

End If
----------------------------------------------------------------------------------

 

[MattS]

Based on the code in one of your previous posts I'm guessing the problem is caused by not using apostrophes/quotation marks to include the string variable.

Try:

Dim var1 As String 
Dim var2 As String 
Dim actionplanid As String 


actionplanid = 26 
var1 = DCount("[Action Plan ID]", "PersonalActionPlanQuery", "[Action Plan ID] = '" & actionplanid & "' And [Completed] = True") 
var2 = DCount("[Action Plan ID]", "PersonalActionPlanQuery", "[Action Plan ID] = '" & actionplanid & "'") 
If var1 = var2 Then 
MsgBox "All Tasks Matched" 

Else 
MsgBox "Not Matched" 

End If

HTH,

Matt.

 

[lloydmav]

Thanks for the help guys! Tried the last suggestion but had data type errors;

However I've managed to solve it easier by removing the need for the action plan id in the dcount. I've put that criteria directly into the query. And for the dcount code I've used:

Dim var1 As String
Dim var2 As String
Dim actionplanid As String


var1 = "0"
var1 = DCount("*", "AllTasksCompleted", "[Completed] = True")
var2 = DCount("[Action Plan ID]", "AllTasksCompleted")

If var1 = var2 Then
MsgBox "All Tasks Matched"

Else
MsgBox "Not Matched"

End If

Thanks for the help!

 

[MattS]

I included the apostrophes because you were using a string variable (actionplanid). Looking back at the code (and the reason for the data type errors) is that the field [Action Plan ID] appears to be numeric.

To get the previous code I posted to work, change the variable actionplanid to be an Integer, and remove the apostrophes.

For total correctness (!?!), make var1 and var2 Integers as well.

Glad you got it sorted anyhow.

Matt.

 

[Mile-O]

For total correctness (!?!), make var1 and var2 Integers as well.

And give them more meaningful names - preferable with the int prefix - you wouldn't want to come back to your code one day, see those variable names and wonder what's 1? what's 2? Ah, they are variants! But what do they represent?

 

[lloydmav]

MattS - cheers, I did realise that was the problem and so I changed them to integers etc however I forgot to remove the apostrophes.

Miles - yeah I know the naming convention is crap. I only temporarily named them like this for testing purposes

Thanks for the suggestions guys, I'm slowly getting back into this access stuff, SLOWLY!