Dlookup or Dcount

[lmv]

I'm trying to display the ttl # of records in each TID on a form in a txt box
only to view #.

If I add the "query q_TID_TTL" to my forms record source then it locks the
form and my ck boxes won't work but it displays the info.

I don't know if it should be Dcount or Dlookup.

I can't remember how to tie it to the Forms list of TIDs which are
displaying in a continous form.

It is repeating just the 1st # in the query list and not the Forms list

=DLookUp("TTL","q_TID_TTL","[TID]")

'========
SELECT tblArea.Area123, TerritoryNumbers.TID, tblArea.AreaName,
Count(TerritoryNumbers.TID) AS Ttl
FROM (TerritoryNumbers INNER JOIN tblArea ON TerritoryNumbers.Area =
tblArea.AreaID) INNER JOIN [Name List] ON TerritoryNumbers.TID = [Name
List].TID
GROUP BY tblArea.Area123, TerritoryNumbers.TID, tblArea.AreaName
HAVING (((TerritoryNumbers.TID) Not Like "Inactive" And
(TerritoryNumbers.TID)<>"Moved" And (TerritoryNumbers.TID)<>"Pending" And
(TerritoryNumbers.TID)<>"x-Eng" And (TerritoryNumbers.TID) Is Not Null))
ORDER BY tblArea.Area123, tblArea.AreaName;
'========

Please help...Thanks!

 

[Ken Snell]

Use DCount function in an expression in a textbox's ControlSource.

=DCount("*", "q_TID_TTL", "[TID]=" & [TID])

 

[lmv]

Thanks Ken I tried before and it errors...
I tried again just to be sure and it still says error...

Use DCount function in an expression in a textbox's ControlSource.

=DCount("*", "q_TID_TTL", "[TID]=" & [TID])

--

Ken Snell
http://www.accessmvp.com/KDSnell/

I'm trying to display the ttl # of records in each TID on a form in a txt
box
only to view #.

If I add the "query q_TID_TTL" to my forms record source then it locks the
form and my ck boxes won't work but it displays the info.

I don't know if it should be Dcount or Dlookup.

I can't remember how to tie it to the Forms list of TIDs which are
displaying in a continous form.

It is repeating just the 1st # in the query list and not the Forms list

=DLookUp("TTL","q_TID_TTL","[TID]")

'========
SELECT tblArea.Area123, TerritoryNumbers.TID, tblArea.AreaName,
Count(TerritoryNumbers.TID) AS Ttl
FROM (TerritoryNumbers INNER JOIN tblArea ON TerritoryNumbers.Area =
tblArea.AreaID) INNER JOIN [Name List] ON TerritoryNumbers.TID = [Name
List].TID
GROUP BY tblArea.Area123, TerritoryNumbers.TID, tblArea.AreaName
HAVING (((TerritoryNumbers.TID) Not Like "Inactive" And
(TerritoryNumbers.TID)<>"Moved" And (TerritoryNumbers.TID)<>"Pending" And
(TerritoryNumbers.TID)<>"x-Eng" And (TerritoryNumbers.TID) Is Not Null))
ORDER BY tblArea.Area123, tblArea.AreaName;
'========

Please help...Thanks!

.

 

[lmv]

BRILLIANT! Thanks that's it!

As your query is apparently already counting the rows per TID correctly you
can use the DLookup function, but you need to look up the relevant value in
the query for the TID in the current row in the form, so the ControlSource of
the text box would be:

=DLookUp("TTL","q_TID_TTL","[TID] =""" & [TID] & """")

This assumes the query returns one row per TID. If it returns multiple rows
per TID and you want the sum of all the counts per TID then use the DSum
function:

=DSum("TTL","q_TID_TTL","[TID] =""" & [TID] & """")

In the above expressions the TID value is wrapped in quotes characters as,
from your query, it appears to be a text data type.

Ken Sheridan
Stafford, England

I'm trying to display the ttl # of records in each TID on a form in a txt box
only to view #.

If I add the "query q_TID_TTL" to my forms record source then it locks the
form and my ck boxes won't work but it displays the info.

I don't know if it should be Dcount or Dlookup.

I can't remember how to tie it to the Forms list of TIDs which are
displaying in a continous form.

It is repeating just the 1st # in the query list and not the Forms list

=DLookUp("TTL","q_TID_TTL","[TID]")

'========
SELECT tblArea.Area123, TerritoryNumbers.TID, tblArea.AreaName,
Count(TerritoryNumbers.TID) AS Ttl
FROM (TerritoryNumbers INNER JOIN tblArea ON TerritoryNumbers.Area =
tblArea.AreaID) INNER JOIN [Name List] ON TerritoryNumbers.TID = [Name
List].TID
GROUP BY tblArea.Area123, TerritoryNumbers.TID, tblArea.AreaName
HAVING (((TerritoryNumbers.TID) Not Like "Inactive" And
(TerritoryNumbers.TID)<>"Moved" And (TerritoryNumbers.TID)<>"Pending" And
(TerritoryNumbers.TID)<>"x-Eng" And (TerritoryNumbers.TID) Is Not Null))
ORDER BY tblArea.Area123, tblArea.AreaName;
'========

Please help...Thanks!

--
Message posted via AccessMonster.com


.

 

[Ken Snell]

Hmm... you're right, Ken. I was looking at his expressions and didn't scroll
down all the way to the query statement.