Yes/No Queries

  • Thread starter Thread starter Jess
  • Start date Start date
J

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?
 
Jess said:
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?
Click to expand...

Update To = DCount("UserID","TBL_RMClients","[RMLastName]='"
&[TBL_RelationshipManagers].[RMLastName] & "' AND Duplicate=False")
 
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?
Click to expand...

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.
 
Back
Top