Runtime error 13 Type mismatch on SQL string

[vba317]

I really thought that I had a better handle on this but I am still having a problem with writing sql strings in access. I put Chr 39 around the string character and I put brackets around the number I thought that was all I had to do, I am wrong. Does it matter what the original data is? In this case MonDt is a date. I tried to change it to a date I than got an error on the dim statement, so I changed it back to a string. I have highlighted in blue where the code is stopping.

Any help is appreciated

Tom


The original query works in access and the sql view is as follows:
'SELECT RPT_Data.uci, RPT_Data.ProvName, RPT_Data.MonDt, Sum(RPT_Data.Pmts) AS SumOfPmts, Sum(RPT_Data.Ref) AS SumOfRef
'FROM RPT_Data
'WHERE (((RPT_Data.rptpd) > 348))
'GROUP BY RPT_Data.uci, RPT_Data.ProvName, RPT_Data.MonDt
'HAVING (((RPT_Data.ProvName) = "LEVIN MD, ELSIE"))
'ORDER BY RPT_Data.ProvName, RPT_Data.MonDt;

Dim strSel1 As String
Dim strSel2 As String
Dim strSel3 As String
Dim strSel4 As String
Dim strSel5 As String
Dim strFld1 As String
Dim strFld2 As String
Dim strWhr1 As String
Dim intWhr1 As Integer
Dim strTbl1 As String
Dim strSum1 As String
Dim strSum2 As String
Dim strProvName As String
'RecordSet
Dim strSql As String
Dim rst As Recordset
'Row Counter
Dim intRow As Integer
Dim intX As Integer

strSel1 = "uci"
strSel2 = "ProvName"
strSel3 = "MonDt"
strSel4 = "Pmts"
strSel5 = "Ref"
strWhr1 = "rptpd"
intWhr1 = 348
strTbl1 = "RPT_Data"
strFld1 = "Pmts"
strFld2 = "Ref"
strSum1 = "Sum(" & strFld1 & ")" & "AS SumOf" & strFld1
strSum2 = "Sum(" & strFld2 & ")" & "AS SumOf" & strFld2
strProvName = "LEVIN MD, ELSIE"
intRow = 4

strSql = "SELECT " & strTbl1 & "." & strSel1 & ", " & strTbl1 & "." & strSel2 & ", " & strTbl1 & "." & strSel3 & ", " & strSum1 & ", " & strSum2 & _
         " FROM " & strTbl1 & _
         " WHERE " & strTbl1 & "." & strWhr1 & " > " & [intWhr1] & _
         " GROUP BY " & strTbl1 & "." & strSel1 & ", " & strTbl1 & "." & strSel2 & ", " & strTbl1 & "." & strSel3 & _
         " HAVING " & strTbl1 & "." & strSel2 & " = " & Chr$(34) & strProvName & Chr$(34) & _
         " ORDER BY " & strTbl1 & "." & strSel2 & ", " & strTbl1 & "." & strSel3 & ";" & ""
    Debug.Print strSql
[BLue] Set rst = CurrentDb.OpenRecordset(strSql, dbOpenSnapshot)[/Blue]
If Not (rst.BOF Or rst.EOF) Then
       goXL.ActiveSheet.Range("A" & intRow).CopyFromRecordset rst
End If

This is the debug.print output

SELECT RPT_Data.uci, RPT_Data.ProvName, RPT_Data.MonDt, Sum(Pmts)AS SumOfPmts, Sum(Ref)AS SumOfRef FROM RPT_Data WHERE RPT_Data.rptpd > 348 GROUP BY RPT_Data.uci, RPT_Data.ProvName, RPT_Data.MonDt HAVING RPT_Data.ProvName = "LEVIN MD, ELSIE" ORDER BY RPT_Data.ProvName, RPT_Data.MonDt;

 

[Andrzejek]

Shot in the dark....

Try this:

strSql = "SELECT " & strTbl1 & "." & strSel1 & ", " & strTbl1 & "." & strSel2 & ", " & strTbl1 & "." & strSel3 & ", " & strSum1 & ", " & strSum2 & _
         " FROM " & strTbl1 & _
         " WHERE " & strTbl1 & "." & strWhr1 & " > " & [intWhr1] & _
         " GROUP BY " & strTbl1 & "." & strSel1 & ", " & strTbl1 & "." & strSel2 & ", " & strTbl1 & "." & strSel3 & _
         " HAVING " & strTbl1 & "." & strSel2 & " = '" & strProvName & "'" & _
         " ORDER BY " & strTbl1 & "." & strSel2 & ", " & strTbl1 & "." & strSel3 & ";" & ""

You need the outcome to be:[tt]
SELECT RPT_Data.uci, RPT_Data.ProvName,
RPT_Data.MonDt, Sum(Pmts)AS SumOfPmts, Sum(Ref)AS SumOfRef
FROM RPT_Data
WHERE RPT_Data.rptpd > 348
GROUP BY RPT_Data.uci, RPT_Data.ProvName, RPT_Data.MonDt
HAVING RPT_Data.ProvName = [red]'[/red]LEVIN MD, ELSIE[red]'[/red]
ORDER BY RPT_Data.ProvName, RPT_Data.MonDt;[/tt]

Do you see the single quotes instead of " ?

Have fun.

---- Andy

 

[dhookom]

I would try troubleshoot by replacing some of the variable names with the hard-coded values until you get it to work. This will identify the point at which your query breaks. In addition, consider modifying the code a little to shorten the SQL and include some spaces in the first couple lines below:

strSum1 = "Sum(" & strFld1 & ") AS SumOf" & strFld1
strSum2 = "Sum(" & strFld2 & ") AS SumOf" & strFld2

strSql = "SELECT " & strSel1 & ", " & strSel2 & ", " & strSel3 & ", " & strSum1 & ", " & strSum2 & _
         " FROM " & strTbl1 & _
         " WHERE " & strWhr1 & " > " & [intWhr1] & _
         " GROUP BY " & strSel1 & ", " & strSel2 & ", " & strSel3 & _
         " HAVING " & strSel2 & " = " & Chr$(34) & strProvName & Chr$(34) & _
         " ORDER BY " & strSel2 & ", " & strSel3 & ";"

Is Ref a numeric field?


Duane
Hook'D on Access
MS Access MVP

 

[vba317]

I found the error of my ways. I did want to thank everyone for your input. My strSQL was correct. I am so used it being wrong that this is where I always start. The problem was I forgot to set the database up properly by adding the following lines
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb

Sorry for my oversight

Tom