How would I write this SQL in a Function

[dschomburg]

Here is my SQL
SELECT tmp_tbl_dealing.crd_Player_Number, Count(tmp_tbl_dealing.crd_Player_Number) AS CountOfcrd_Player_Number
FROM tmp_tbl_dealing
GROUP BY tmp_tbl_dealing.crd_Player_Number;

Which will always return 1 record. I want to use the field "CountOfcrd_Player_Number" to populate a textbox when a button is pushed.
I'm having trouble with using the value of the textbox in an IF THEN statement. So,
I think the only thing I can do is call a function but I don't know how to do the SQL in the function.

Any thoughts?

Thanks,
Dave

 

[scking]

If it only returns a single record then what is the purpose of the GROUP BY clause? If true then you should be able to just assign the value after using a DCount function.

txtBox = DCount("[Player_Number]", "tmp_tbl_dealing", "[Player_Number]=" & lngPlayer_Number

-------------------------------------
scking@arinc.com
Try to resolve problems independently
Then seek help among peers or experts
But TEST recommended solutions
-------------------------------------

 

[dschomburg]

oops! You're absolutly correct. There will be more then 1 groups. It's based on players, so you could have up to 4 groups.
I had the text box as a list box and the record source was based on the above SQL but used a separate function based on a global variable to limit the record to 1. Which was based on the player number (say player # 1) it would just return the group for player1 - that's what I meant by return on 1 record.

So could I use your suggestion but include a WHERE clause with it? Like WHERE tmp_tbl_dealing.crd_Player_Number = WHATEVERTHEVARIABLEIS

 

[scking]

I've set this up to user a global variable (not recommended) called crd_Player_nbr to return the count. I did not add code to verify the global variable is good, error trapping, or setting the recordset to nothing. This would work for Access 97 but Access 2000 and greater would need to have a reference set to Dao360.dll (Data Access Objects Library).

Public Function GetCount() As Integer

Dim strSQL As String
Dim rstPlayer As DAO.Recordset

strSQL = "SELECT tmp_tbl_dealing.crd_Player_Number, Count(" & crd_Player_nbr & &quot AS CountOfcrd_Player_Number
FROM tmp_tbl_dealing
GROUP BY tmp_tbl_dealing.crd_Player_Number;"
Set rstPlayer = CurrentDb.OpenRecordset(strSQL)
If rstPlayer.RecordCount <> 0 Then
rstPlayer.MoveFirst
GetCount = rstPlayer!CountOfcrd_Player_Number
Else
GetCount = 0
End If
End Function
-------------------------------------
scking@arinc.com
Try to resolve problems independently
Then seek help among peers or experts
But TEST recommended solutions
-------------------------------------

 

[dschomburg]

hmmm...I get the error
&quot;Run-time 3075 wrong number of arguments used with function in query expression 'count()'&quot;

 

[simmsey]

As far as I can see what you are trying to do:

return the count for a particular player number is done with scking's original post:

txtBox = DCount(&quot;[Player_Number]&quot;, &quot;tmp_tbl_dealing&quot;, &quot;[Player_Number]=&quot; & lngPlayer_Number

by setting the variable lngPlayer_Number to the number you want.

The following post using function getcount has an error in the SQL and I don't follow the bit about the return being in the variable crd_Player_nbr.
I think the function should be something like this:

Public Function GetCount(lngPlayer_Number as long) As Integer

Dim strSQL As String
Dim rstPlayer As DAO.Recordset

strSQL = &quot;SELECT Count(tmp_tbl_dealing.crd_Player_Number) AS CountOfcrd_Player_Number
FROM tmp_tbl_dealing
GROUP BY tmp_tbl_dealing.crd_Player_Number
HAVING tmp_tbl_dealing.crd_Player_Number=&quot;& lngPlayer_Number & &quot;;&quot;

Set rstPlayer = CurrentDb.OpenRecordset(strSQL)
If rstPlayer.RecordCount <> 0 Then
rstPlayer.MoveFirst
GetCount = rstPlayer!CountOfcrd_Player_Number
Else
GetCount = 0
End If
End Function


BUT doesnt that just do the same as the one line DCount?

Simmsey