Solved DCOUNT() ISSUE

[Ashfaque]

Hi,

I have mainform called F_Project with subform F_ProjAssigneeDetails - one-to-many relation

The key field in main form is ProjNum which is relevant with subform as well. When we enter new project in main form, we adds number employee to that particular projects.

I just want to count number of staff assigned on a particular projects. I tried it with Dcount but the X is producing 0 only where as there are 3 active employees assigned on that project. my AssigneeActive is check mark field on subform.

Following code is triggering thru a combo placed on main form.

Dim X As Integer
X = DCount("*", "T_ProjAssigneeDetails", "ProjNum = 'ProjNum' and 'Me!F_ProjAssigneeDetails!AssigneeActive.value= true'")
Me!F_ProjAssigneeDetails!TotAssignedStaff = " " & X

No error but not counting numbers.

Any help shall be appreciated..

 

[cheekybuddha]

Without more details, I suspect you should be concatenating your values in to your criteria expression:

Dim X As Integer
X = DCount("*", "T_ProjAssigneeDetails", "ProjNum = '" & Me.ProjNum & "' and AssigneeActive = " & Me.F_ProjAssigneeDetails.Form.AssigneeActive)
' ...

 

[arnelgp]

or

Dim X As Integer
X = DCount("*", "T_ProjAssigneeDetails", "ProjNum = '" & Me.ProjNum & "' and AssigneeActive = -1")

 

[cheekybuddha]

Good point Arnel, you only need to count the active assignees.

 

[Ashfaque]

or

Dim X As Integer
X = DCount("*", "T_ProjAssigneeDetails", "ProjNum = '" & Me.ProjNum & "' and AssigneeActive = -1")

Thanks Arnel,

I tried the both way you explained but still X is producing 0 value.
Or do you suggest I should place my text box"TotAssignedStaff" on main form instead of subform to get X value.

 

[arnelgp]

Or do you suggest I should place my text box"TotAssignedStaff" on main form

Could be.

 

[The_Doc_Man]

What is the data type of ProjNum? If it is numeric, that syntax isn't going to work because in that context, you are comparing a quoted text string to a numeric value. That won't fly.

X = DCount("*", "T_ProjAssigneeDetails", "( ProjNum = " & ProjNum & ") and " & Me!F_ProjAssigneeDetails!AssigneeActive )

1. Syntax for numeric "ProjNum" case
2. Don't have to compare a "TRUE" value to anything when in a conditional expression. Remember that your criteria will be evaluated as separate T/F values. So the left half is T/F based on ProjNum having the specific value and the right half is T/F based on ... itself, because it is a Boolean.

 

[Ashfaque]

What is the data type of ProjNum? If it is numeric, that syntax isn't going to work because in that context, you are comparing a quoted text string to a numeric value. That won't fly.

X = DCount("*", "T_ProjAssigneeDetails", "( ProjNum = " & ProjNum & ") and " & Me!F_ProjAssigneeDetails!AssigneeActive )

1. Syntax for numeric "ProjNum" case
2. Don't have to compare a "TRUE" value to anything when in a conditional expression. Remember that your criteria will be evaluated as separate T/F values. So the left half is T/F based on ProjNum having the specific value and the right half is T/F based on ... itself, because it is a Boolean.

ProjNum is Text data type.

Your above line produces Run-time error 2471 - The expression your entered as query parameter has produced this error: 'PRJ'

The string value is actually PRJ/CNRT/21/00001, PRJ/CNRT/21/00002 etc....

 

[arnelgp]

post the structure of T_projAssigneeDetails table.

 

[Ashfaque]

Thanks Arnel,

My tables are on SQL server and linked in Access FE.

 

[Ashfaque]

Attached shows the data in both table

 

[arnelgp]

we go back to post#1 formula.
projNum is in the Combo?
what is the rowSource of the combo and it's Bound Column?

 

[Ashfaque]

we go back to post#1 formula.
projNum is in the Combo?
what is the rowSource of the combo and it's Bound Column?

This is rowsource of combo on main form:

SELECT [T_ProjectBaseContracts].[ProjNum] FROM T_ProjectBaseContracts ORDER BY [ProjNum];

 

[arnelgp]

so you need to use the Combobox Name:

Dim X As Integer
X = DCount("*", "T_ProjAssigneeDetails", "ProjNum = '" & Me!theComboboxNameHere & "' and AssigneeActive = -1")

 

[Ashfaque]

so you need to use the Combobox Name:

Dim X As Integer
X = DCount("*", "T_ProjAssigneeDetails", "ProjNum = '" & Me!theComboboxNameHere & "' and AssigneeActive = -1")

Tried that way in the beginning. Please see the pic. I placed cursor to know the x value...it is still zero.

 

[Ashfaque]

I just played with your code particulary with quotes like below:
X = DCount("*", "T_ProjAssigneeDetails", "ProjNum = '" & Me!CboSearchProj.Column(0) & "'")
and half part of worked. But addtional criteria is still pending.

 

[arnelgp]

is any record for that projnum has AssigneeActive Checked?
can you try the other fields:

X = DCount("*", "T_ProjAssigneeDetails", "ProjNum = '" & Me!CboSearchProj & "' And ((ProjAssigneeName Is Null)=False)")

 

[Ashfaque]

is any record for that projnum has AssigneeActive Checked?
can you try the other fields:

X = DCount("*", "T_ProjAssigneeDetails", "ProjNum = '" & Me!CboSearchProj & "' And ((ProjAssigneeName Is Null)=False)")

2 records are checked and one record unchecked

 

[arnelgp]

does ProjNum field has a Lookup?
can you post just the T_ProjAssigneeDetails.

 

[Ashfaque]

does ProjNum field has a Lookup?
can you post just the T_ProjAssigneeDetails.