SQL Statement with a Grouping Function in Control Source for Form

[Guest]

I would like to put a text box on a form that displays the count of the
number of records in the underlying table that have the same values as the
values for two fields in the record being displayed in the form. For
example, suppose the Field1 field in the record in the form has the value 6
and the Field2 field in the form has the value 4. I would like to display in
the new field the total number of records in the table that have these two
values for Field1 and Field2. Presumably I can run a SQL statement using the
Count function something like this:

Select count(*) from MyTable where Field1=6 & Field2=4

However, the 6 and 4 above would be replaced by variables that reflect the
appropriate values, as taken from the form. Presumably I could attach the
query to the ControlSource field in the properties of the control (text box)
I put in the form. But I can’t figure out the proper syntax for the Control
Source.

Am I approaching this properly?

Can you suggest the proper syntax for the control source?

Thank you for you help.

 

[Rob Parker]

Try the following (with the appropriate table, field and variable names) in
your unbound textbox:

= dcount("*","MyTable","Field1 = " & var1 & " And Field2 = " & var2)

If var1 and var2 are not numbers, you'll need to include single-quote
delimiters:

= dcount("*","MyTable","Field1 = '" & var1 & "' And Field2 = '" & var2 &
"'")

HTH,

Rob

 

[Guest]

I would like to put a text box on a form that displays the count of the
number of records in the underlying table that have the same values as the
values for two fields in the record being displayed in the form. For
example, suppose the Field1 field in the record in the form has the value 6
and the Field2 field in the form has the value 4. I would like to display in
the new field the total number of records in the table that have these two
values for Field1 and Field2. Presumably I can run a SQL statement using the
Count function something like this:

Select count(*) from MyTable where Field1=6 & Field2=4

However, the 6 and 4 above would be replaced by variables that reflect the
appropriate values, as taken from the form. Presumably I could attach the
query to the ControlSource field in the properties of the control (text box)
I put in the form. But I can’t figure out the proper syntax for the Control
Source.

Am I approaching this properly?

Can you suggest the proper syntax for the control source?

Thank you for you help.

A little extra info...

If you use the DCount() function like Rob suggested, you will need to use
Forms!FormName.ControlName (replaced with your form and control names) to
get the values from the form.

An alternative, if you wanted to use SQL, is to write a custom function (UDF
- user defined function). It would be sometning like this:

'******* AIR Code ******
Function Get_Rec_Count()

Dim rs as DAO.Recordset
Dim strSQL as String

Get_Rec_Count = 0

' numeric parameters
strSQL = "Select count(*) from MyTable where [Field1] = "
strSQL = strSQL & Forms!frmName.ControlName1
strSQL = strSQL & " And [Field2] = " & Forms!frmName.ControlName2

Set rs = CurrentDB.OpenRecordset(strSQL)

If rs.RecordCount >0 then
Get_Rec_Count= rs.RecordCount
End If

rs.Close
Set rs = Nothing

End Function

'********END Code *******

In the Control Source for the text box where you want the show the count of
records found, you would enter:

= Get_Rec_Count()


HTH

 

[Guest]

The Dcount() works, but I need the versatility of the VBA function to allow a
little editing of the count, so I tried to convert from the Dcount() to the
function recommended by Steve. I installed the function as a public function
in the module associated with the form. But when I insert the function name
preceded by an equals sign in the Control Source field for the Control on the
form I get the message: You tried to run a Visual Basic procedure to set a
property or method for an object. However, the component doesn't make the
property or method available for Automation operations. Check the
component's documentation for information on the properties and methods it
makes avaialble for Automation operations.

Thanks again for the help,

Don Macnaughton

I would like to put a text box on a form that displays the count of the
number of records in the underlying table that have the same values as the
values for two fields in the record being displayed in the form. For
example, suppose the Field1 field in the record in the form has the value 6
and the Field2 field in the form has the value 4. I would like to display in
the new field the total number of records in the table that have these two
values for Field1 and Field2. Presumably I can run a SQL statement using the
Count function something like this:

Select count(*) from MyTable where Field1=6 & Field2=4

However, the 6 and 4 above would be replaced by variables that reflect the
appropriate values, as taken from the form. Presumably I could attach the
query to the ControlSource field in the properties of the control (text box)
I put in the form. But I can’t figure out the proper syntax for the Control
Source.

Am I approaching this properly?

Can you suggest the proper syntax for the control source?

Thank you for you help.

A little extra info...

If you use the DCount() function like Rob suggested, you will need to use
Forms!FormName.ControlName (replaced with your form and control names) to
get the values from the form.

An alternative, if you wanted to use SQL, is to write a custom function (UDF
- user defined function). It would be sometning like this:

'******* AIR Code ******
Function Get_Rec_Count()

Dim rs as DAO.Recordset
Dim strSQL as String

Get_Rec_Count = 0

' numeric parameters
strSQL = "Select count(*) from MyTable where [Field1] = "
strSQL = strSQL & Forms!frmName.ControlName1
strSQL = strSQL & " And [Field2] = " & Forms!frmName.ControlName2

Set rs = CurrentDB.OpenRecordset(strSQL)

If rs.RecordCount >0 then
Get_Rec_Count= rs.RecordCount
End If

rs.Close
Set rs = Nothing

End Function

'********END Code *******

In the Control Source for the text box where you want the show the count of
records found, you would enter:

= Get_Rec_Count()


HTH