Using SQL with RecordSet (1 Viewer)

Coldsteel · Mar 31, 2010

[FONT=&quot]I keep getting the run-time error 3709 when I run this code. Can someone help me figure out what I am doing wrong.[/FONT]
[FONT=&quot] [/FONT]
Private Sub Text114_Click()

Dim ar As adodb.Recordset

Set ar = New adodb.Recordset
ar.Open "SELECT Count(1) AS Total, tbl_referral.[Prod Specialist #]" & _
"FROM tbl_referral" & _
"WHERE (((tbl_referral.[DATE REFERRED])" & _
"Between GetDateLower() And GetDateUpper()) AND ((tbl_referral.[APPROVED/DENIED])='Approved') AND ((tbl_referral.[Prod Specialist #])=[Forms]![PSstats]![Text114]))" & _
"GROUP BY tbl_referral.[Prod Specialist #];"""
intID = ar!Total
Text81 = intID
End Sub

WayneRyan · Mar 31, 2010

Cold,

Are you really counting or summing?

If you're counting, then change the "Count(1)" to "Count(*)".

If it's a summation, then change to something like --> Sum(TotalHours).

Also, the initial problem is how your sql string is formatted.
See the comment about the trailing spaces.

Code:

Private Sub Text114_Click()

Dim ar As adodb.Recordset

Set ar = New adodb.Recordset
ar.Open "SELECT [Prod Specialist #], Count(*) AS Total " & _  ' <-- Note the trailing space, each line
        "FROM   tbl_referral " & _                            ' <-- has a space before the final "
        "WHERE  [DATE REFERRED] Between GetDateLower() And GetDateUpper() AND " & _
        "       [APPROVED/DENIED] = 'Approved' AND " & _
        "       [Prod Specialist #] = " & [Forms]![PSstats]![Text114] & " " & _
        "GROUP BY [Prod Specialist #];"
Me.Text81 = ar!Total
End Sub

You can also build a string:

strSQL = "Select ..."

Then, you can just insert a MessageBox which will print strSQL and you can see what
the JET engine will see.

hth,
Wayne

Coldsteel · Mar 31, 2010

Thanks for the help Wayne. I have been working on this Sql Statement to see if what i am processing is correct. I keep getting errors. Here is the code i have been playing with to see my SQL results.
Private Sub Text114_Click()

Dim strSQL As String

strSQL = "SELECT [Prod Specialist #], Count(*) AS Total " & _
"FROM tbl_referral " & _
"WHERE [DATE REFERRED] Between GetDateLower() And GetDateUpper() AND " & _
" [APPROVED/DENIED] = 'Approved' AND " & _
" [Prod Specialist #] = " & [Forms]![PSstats]![Text114] & " " & _
"GROUP BY [Prod Specialist #];"

MsgBox strSQL

End Sub

namliam · Mar 31, 2010

When building sql use something like:

Code:

strSQL = ""
strSQL = strSQL & " SELECT [Prod Specialist #]"
strSQL = strSQL & " ,      Count(*) AS Total " 
strSQL = strSQL & " FROM  tbl_referral " 
strSQL = strSQL & " WHERE [DATE REFERRED] Between GetDateLower() And GetDateUpper() " 
strSQL = strSQL & "   AND [APPROVED/DENIED] = 'Approved' " 
strSQL = strSQL & "   AND [Prod Specialist #] = " & [Forms]![PSstats]![Text114] & " " 
strSQL = strSQL & " GROUP BY [Prod Specialist #];"

Also use the code wraps (the # button in the post formatting box) to wrap the code in... like above.

Note the "extra" spaces I added in front of the keywords SELECT FROM WHERE etc...
This ensures youd dont "forget" a space. As the starting space is easy to catch as all code is always on the same 'allignement'

Using SQL with RecordSet (1 Viewer)

Coldsteel

Registered User.

WayneRyan

AWF VIP

Coldsteel

Registered User.

namliam

The Mailman - AWF VIP

Similar threads

Users who are viewing this thread