Using SQL in VBA 3

[JR2913]

I am trying to use the following query to generate a variable in VB:


strSQL = "SELECT Count(tblChildAttend.ChildID) AS CountOfChildID "
strSQL = strSQL & "FROM tblChildAttend "
strSQL = strSQL & "GROUP BY tblChildAttend.[Service ID], tblChildAttend.Date, tblChildAttend.Present "
strSQL = strSQL & "HAVING (((tblChildAttend.[Service ID])=[Forms]![frmAttendance]![childService ID]) AND ((tblChildAttend.Date)=[Forms]![frmAttendance]![chattenddate]));"

Could some one please help by suggesting the code I would need in VBA to turn the CountOfChildID into a TEMP variable that I can use elsewhere in the procedure I am trying to put together? Does this require the use of recordsets?

Many thanks

 

[aregan1]

Look up DCOUNT as a quick solution...

other wise try a recordset for a better (but harder to code) solution...

--James

 

[scriverb]

I am going to assume that you are returning one record only here. And, yes the use of a recordset is the way to go:
Dim db as DAO.database
Dim rs as DAO.recordset
Dim lngCntOfChildID as Long
Dim strSQL as string
Set db = CurrentDB
strSQL = "SELECT Count(tblChildAttend.ChildID) AS CountOfChildID "
strSQL = strSQL & "FROM tblChildAttend "
strSQL = strSQL & "GROUP BY tblChildAttend.[Service ID], tblChildAttend.Date, tblChildAttend.Present "
strSQL = strSQL & "HAVING (((tblChildAttend.[Service ID])=[Forms]![frmAttendance]![childService ID]) AND ((tblChildAttend.Date)=[Forms]![frmAttendance]![chattenddate]));"
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
rs.movefirst
lngCntOfChildID = rs("CountOfChildID&quot
rs.close
db.close

Give this a try and let me know how it works for you.
Bob Scriver

 

[Junior1544]

You go Bob! I don't have access to access (nice pun eh?) right now, other wise I would have done some thing...

--James
junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[JR2913]

Many thanks sciverb - I have been playing with some code that is very similar, but each time I run it (and yours!) I get the error message "Too few parameters. Expected 2".

I think this occurs when it gets to the line beginning set rs = db.OpenRecordset

Have you any idea why?

Although I am using Access XP it is an A2K database I am developing.

 

[JR2913]

Have given up on the SQL route - don't have the time to find the sophisticated solution, so have gone with aregan1's DCOUNT suggestion, which seems to work just as well.

Very frustrating that the SQL copied from a query (which works and generates the required output) doesn't work in the same way when you get to VB.


JR

 

[scriverb]

JR2913: Put a STOP command in front of the Set rs command and open the Debug window. In the bottom pane enter ?strSQL and press Enter. Copy the full SQL string that has been created there and then close everthing else down and go back to design view. Create a new query and paste this SQL into the SQL screen. Now run the SQL and see what you get. Remember you have to have the form open and filled in to do this as there are parameter values on the form that the SQL query needs.

This is how I would try to debut your query here. Let me know what you find out. Bob Scriver

 

[JR2913]

Thanks Bob. I tried what you suggested and interestingly 2 figures were presented as output for CountofChildID rather than the one I had expected. One was the actual total of records found (which was correct) and the second was the number 2.

I think the discrepancy is to do with the True/False field (Present) - the two results are firstly, the number of children present for the specified date and, secondly, those who were absent.

However, I have amended the script, pasted into a new query and it works fine. When I try to run it in VB, though, I still get the parameters error.

Current SQL is:

SELECT Count(tblChildAttend.ChildID) AS CountOfChildID FROM tblChildAttend GROUP BY tblChildAttend.[Service ID], tblChildAttend.Date HAVING (((tblChildAttend.[Service ID])=[Forms]![frmAttendance]![childService ID]) AND ((tblChildAttend.Date)=[Forms]![frmAttendance]![chattenddate]));


Still baffled..........


John R

 

[JeremyNYC]

Dates have to be offset with #, just like text has to be offset with '. All references to forms should happen outside the quotes, so that Access can convert those things to the appropriate values before passing the sql string to Jet. That would make your sql construction look like this:
strSQL = "SELECT Count(tblChildAttend.ChildID) AS CountOfChildID "
strSQL = strSQL & "FROM tblChildAttend "
strSQL = strSQL & "GROUP BY tblChildAttend.[Service ID], tblChildAttend.Date, tblChildAttend.Present "
strSQL = strSQL & "HAVING (((tblChildAttend.[Service ID])= " & [Forms]![frmAttendance]![childService ID]) & " AND ((tblChildAttend.Date)= #" & [Forms]![frmAttendance]![chattenddate] & "#));"

Jeremy ==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[JR2913]

Thanks Jeremy - that has cracked it! So frustrating, though...... the hours I have wasted on this.

DCOUNT took me less than 2 minutes!

 

[JeremyNYC]

JR,

Glad to help. Yes, DCount is easier, but as Bob points out, it's better to use a recordset. Using recordsets is a bit more complex to learn, but once you do, it doesn't take much more time to write the code, and it runs MUCH more quickly.

Jeremy ==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.