Help with the DCount function

[Jess]

I am attempting to create the following query, but I keep
getting a "Type Conversion Failure". I want to update a
Relationships Established field in a Relationship Managers
Table from the User ID field in the RMClients table using
the DCount function where the RMLastNames are equal. The
query I wrote was:

Field = RelationshipsEstablished
Table = TBL_RelationshipMangers
Update To =
DCount([UserID],"TBL_RMClients",[TBL_RMClients]![RMLastName]=[TBL_RelationshipManagers]![RMLastName])
Criteria =
[TBL_RelationshipManagers]![RMLastName]=[TBL_RMClients]![RMLastName]

I need to count the number of user IDs in a table under a
particluar Last Name and place that number in a field in
another table.

Can someone please tell me what I'm doing wrong?

 

[Ken Snell]

Your DCount syntax is incorrect, I believe. Try this:

DCount("UserID","TBL_RMClients","[RMLastName]='" &
[TBL_RelationshipManagers].[RMLastName] & "'")



--
Ken Snell
<MS ACCESS MVP>

I am attempting to create the following query, but I keep
getting a "Type Conversion Failure". I want to update a
Relationships Established field in a Relationship Managers
Table from the User ID field in the RMClients table using
the DCount function where the RMLastNames are equal. The
query I wrote was:

Field = RelationshipsEstablished
Table = TBL_RelationshipMangers
Update To =
DCount([UserID],"TBL_RMClients",[TBL_RMClients]![RMLastName]=[TBL_Relationsh
ipManagers]![RMLastName])
Criteria =
[TBL_RelationshipManagers]![RMLastName]=[TBL_RMClients]![RMLastName]

I need to count the number of user IDs in a table under a
particluar Last Name and place that number in a field in
another table.

Can someone please tell me what I'm doing wrong?

 

[Guest]

Your DCount function might be returning a Null for some entry that doesn't satisfy your criteria, and the field RelationshipsEstablished probably won't accept nulls (Required set to Yes). Either make sure there is at least one client entry for each Manager last name, or set the Required property to False. If you want to check your DCount function, just make it a field in a normal query, execute it in the QBE grid and see if there are any odd values.

 

[Guest]

Thank you so much...IT WORKED!!!

 

[Ken Snell]

You're welcome.

 

[Guest]

Hey, I have one more question, can I add another criteria
that says to only count the records where the value in the
Duplicate field of the ClientAccounts table is blank?

 

[Ken Snell]

DCount can work on only one table or one query at a time. ClientAccounts is
not the table that you're using in the DCount expression in your post, so
the answer is no.

However, you can create a query that will return all the records from these
two tables and save the query, and then use that query name in a DCount
function.

 

[Guest]

Okay thanks.

-----Original Message-----
DCount can work on only one table or one query at a time. ClientAccounts is
not the table that you're using in the DCount expression in your post, so
the answer is no.

However, you can create a query that will return all the records from these
two tables and save the query, and then use that query name in a DCount
function.

--
Ken Snell
<MS ACCESS MVP>





.