Yes/No Queries

[Jess]

I have set up a query that states:

Field = TrustAccountsIncluded
Table = TBL_RelationshipManagers
Update To = DCount("UserID","TBL_RMClients","[RMLastName]
='" &[TBL_RelationshipManagers].[RMLastName] & "'")

But now, I want to add one more stipulation that says only
count the records where the field Duplicate in the table
CustomerAccounts is equal to No (I have it in the table as
a Yes/No field type), can someone help me with this?

 

[Bas Cost Budde]

I have set up a query that states:

Field = TrustAccountsIncluded
Table = TBL_RelationshipManagers
Update To = DCount("UserID","TBL_RMClients","[RMLastName]
='" &[TBL_RelationshipManagers].[RMLastName] & "'")

But now, I want to add one more stipulation that says only
count the records where the field Duplicate in the table
CustomerAccounts is equal to No (I have it in the table as
a Yes/No field type), can someone help me with this?

Update To = DCount("UserID","TBL_RMClients","[RMLastName]='"
&[TBL_RelationshipManagers].[RMLastName] & "' AND Duplicate=False")

 

[John Vinson]

I have set up a query that states:

Field = TrustAccountsIncluded
Table = TBL_RelationshipManagers
Update To = DCount("UserID","TBL_RMClients","[RMLastName]
='" &[TBL_RelationshipManagers].[RMLastName] & "'")

But now, I want to add one more stipulation that says only
count the records where the field Duplicate in the table
CustomerAccounts is equal to No (I have it in the table as
a Yes/No field type), can someone help me with this?

The DCount() function's third argument is a text string which would be
a valid WHERE clause in a query (without the word WHERE) - so you can
just build it up piecewise:

Update To = DCount("UserID","TBL_RMClients","[RMLastName]
='" &[TBL_RelationshipManagers].[RMLastName] & "' AND [Duplicate] =
False")

HOWEVER!!!!

Storing this kind of count in a table field is probably a VERY BAD
IDEA. If a new record gets added to TBL_RMClients, or a client is
deleted, then the value stored in this field WILL BE WRONG, with no
obvious way to detect that it's wrong. I'd suggest just using the
DCount function (as modified) as a calculated field in a Query rather
than storing it at all.