Odd runtime error 3464 - Data Type mismatch

M

msblane

Registered User.
Local time
Today, 14:46
Joined
Aug 1, 2012
Messages
10
Hi,

I have built in code a long sql statement as follows:

SELECT Age, BirdType, Flock, House, [Lab#], Organ, Origin, SampleDate, SampleType, Site, Source, SourceGroup, [VT#], ProfileID, APIName1, APIAmts1, APILevels1, FullVals1, Match1, APIName2, APIAmts2, APILevels2, FullVals2, Match2, APIName3, APIAmts3, APILevels3, FullVals3, Match3, APIName4, APIAmts4, APILevels4, FullVals4, Match4, APIName5, APIAmts5, APILevels5, FullVals5, Match5, APIName6, APIAmts6, APILevels6, FullVals6, Match6, APIName7, APIAmts7, APILevels7, FullVals7, Match7, APIName8, APIAmts8, APILevels8, FullVals8, Match8, APIName9, APIAmts9, APILevels9, FullVals9, Match9 , APIName10, APIAmts10, APILevels10, FullVals10, Match10, APIName11, APIAmts11, APILevels11, FullVals11, Match11, APIName12, APIAmts12, APILevels12, FullVals12, Match12, APIName13, APIAmts13, APILevels13, FullVals13, Match13, APIName14, APIAmts14, APILevels14, FullVals14, Match14, APIName15, APIAmts15, APILevels15, FullVals15, Match15, APIName17, APIAmts17, APILevels17, FullVals17, Match17, APIName18, APIAmts18, APILevels18, FullVals
18, Match18, APIName19, APIAmts19, APILevels19, FullVals19, Match19 , APIName20, APIAmts20, APILevels20, FullVals20, Match20, APIName21, APIAmts21, APILevels21, FullVals21, Match21, APIName22, APIAmts22, APILevels22, FullVals22, Match22, APIName23, APIAmts23, APILevels23, FullVals23, Match23, APIName24, APIAmts24, APILevels24, FullVals24, Match24, APIName25, APIAmts25, APILevels25, FullVals25, Match25 FROM qryReportBaseLevel2 ORDER BY qryReportBaseLevel2.SourceGroup, qryReportBaseLevel2.Source, qryReportBaseLevel2.ProfileID;

When I try to open a recordset based on this sql, it gives me the runtime error - which is odd since I don't have any criteria in the statement.

I think the problem may be that vba is somehow adding a line break between "fullvals" and "18", but I don't know why it would do so and it doesn't always (only if the string is long). Can anyone help?

Thanks!
 
What's the code? If that's all on one line, have you tried adding line continuation characters and breaking it up?
 
Hi Paul,

Is the underscore the line continuation character?

The code that produces the statement is really long - but basically it checks what fields should be included in the query based on what the user has selected on a form. I can post it if you think it would be helpful. That code is in a separate procedure and passed to this procedure in three string variables.
 
msblane said:
I think the problem may be that vba is somehow adding a line break between "fullvals" and "18", but I don't know why it would do so and it doesn't always (only if the string is long). Can anyone help?

Thanks!
Click to expand...
It happens because you reach the max length of a line in the code window, so split it up.

Code: 
 sqlString = "SELECT Age, BirdType, Flock, House, [Lab#], Organ, Origin, SampleDate, SampleType, Site, Source, SourceGroup, [VT#], " _
 & "ProfileID, APIName1, APIAmts1, APILevels1, FullVals1, Match1, APIName2, APIAmts2, APILevels2, FullVals2, Match2, APIName3, APIAmts3, " _
 & "APILevels3, FullVals3, Match3, APIName4, APIAmts4, APILevels4, FullVals4, Match4, APIName5, APIAmts5, APILevels5, FullVals5, Match5, " _
 & "APIName6, APIAmts6, APILevels6, FullVals6, Match6, APIName7, APIAmts7, APILevels7, FullVals7, Match7, APIName8, APIAmts8, APILevels8, " _
 & "FullVals8, Match8, APIName9, APIAmts9, APILevels9, FullVals9, Match9 , APIName10, APIAmts10, APILevels10, FullVals10, Match10, APIName11, " _
 & "APIAmts11, APILevels11, FullVals11, Match11, APIName12, APIAmts12, APILevels12, FullVals12, Match12, APIName13, APIAmts13, APILevels13, FullVals13, " _
 & "Match13, APIName14, APIAmts14, APILevels14, FullVals14, Match14, APIName15, APIAmts15, APILevels15, FullVals15, Match15, APIName17, APIAmts17, APILevels17, FullVals17, " _
 & "Match17 , APIName18, APIAmts18, APILevels18, FullVals18, Match18, APIName19, APIAmts19, APILevels19, FullVals19, Match19 , APIName20, APIAmts20, APILevels20, FullVals20, " _
 & "Match20, APIName21, APIAmts21, APILevels21, FullVals21, Match21, APIName22, APIAmts22, APILevels22, FullVals22, Match22, APIName23, APIAmts23, APILevels23, FullVals23, " _
 & "Match23, APIName24, APIAmts24, APILevels24, FullVals24, Match24, APIName25, APIAmts25, APILevels25, FullVals25, Match25 FROM qryReportBaseLevel2 ORDER BY qryReportBaseLevel2.SourceGroup, qryReportBaseLevel2.Source, qryReportBaseLevel2.ProfileID;"
 
Hi JHB,

I tried the above and I am still getting the error - and it still breaks the line in the middle of a field name. I'm getting a tad frustrated! But thanks for your and Paul's suggestion.
 
msblane said:
Hi JHB,
... - and it still breaks the line in the middle of a field name. I'm getting a tad frustrated!
Click to expand...
It shouldn't, so I would like to see how you open the recordset, (code lines)!
Try to paste it into a query and see if you still get the error.
 
How about

SELECT * FROM qryReportBaseLevel2 ORDER BY qryReportBaseLevel2.SourceGroup, qryReportBaseLevel2.Source, qryReportBaseLevel2.ProfileID

Anybody ever mention the word normalization to you? This db appears to need it.
 
pbaldy - the database itself is indeed normalised - however what the client has asked for demands this sort of query - I have tried about 5 times to figure out another way to do it - without any luck.

JHB: here are the 6 variables and the code to create a querydef from it. The resultant string is shown at the bottom. I am still getting the error.

Debug.Print strsql1
SELECT Age, BirdType, Flock, House, [Lab#], Organ, Origin, SampleDate, SampleType, Site, Source, SourceGroup, [VT#], ProfileID, FROM qryReportBaseLevel2 ORDER BY qryReportBaseLevel2.SourceGroup, qryReportBaseLevel2.Source, qryReportBaseLevel2.ProfileID;

debug.Print strsql2
APIName1, APIAmts1, APILevels1, FullVals1, Match1, APIName2, APIAmts2, APILevels2, FullVals2, Match2, APIName3, APIAmts3, APILevels3, FullVals3, Match3, APIName4, APIAmts4, APILevels4, FullVals4, Match4, APIName5, APIAmts5, APILevels5, FullVals5, Match5

Debug.Print strsql3
, APIName6, APIAmts6, APILevels6, FullVals6, Match6, APIName7, APIAmts7, APILevels7, FullVals7, Match7, APIName8, APIAmts8, APILevels8, FullVals8, Match8, APIName9, APIAmts9, APILevels9, FullVals9, Match9

Debug.Print strsql4
, APIName10, APIAmts10, APILevels10, FullVals10, Match10, APIName11, APIAmts11, APILevels11, FullVals11, Match11, APIName12, APIAmts12, APILevels12, FullVals12, Match12, APIName13, APIAmts13, APILevels13, FullVals13, Match13, APIName14, APIAmts14, APILevels14, FullVals14, Match14, APIName15, APIAmts15, APILevels15, FullVals15, Match15

Debug.Print strsql5
, APIName16, APIAmts16, APILevels16, FullVals16, Match16, APIName17, APIAmts17, APILevels17, FullVals17, Match17, APIName18, APIAmts18, APILevels18, FullVals18, Match18, APIName19, APIAmts19, APILevels19, FullVals19, Match19

Debug.Print strsql6
, APIName20, APIAmts20, APILevels20, FullVals20, Match20, APIName21, APIAmts21, APILevels21, FullVals21, Match21, APIName22, APIAmts22, APILevels22, FullVals22, Match22, APIName23, APIAmts23, APILevels23, FullVals23, Match23, APIName24, APIAmts24, APILevels24, FullVals24, Match24, APIName25, APIAmts25, APILevels25, FullVals25, Match25






Public Function ExportReport(SFNo As Integer, SFNames As String, strRPDate As String, strSQL1 As String, strSQL2 As String, strSQL3 As String, strSQL4 As String, strSQL5 As String, strSQL6 As String, APICount As Integer) As Integer
Dim db As DAO.Database
Dim rstReportQuery As DAO.Recordset
Dim strrpttitle As String
Dim lngPID As Long
Dim lngPIDold As Long
Dim strSource As String
Dim strSourceOld As String
Dim strSourceGrp As String
Dim strSourceGrpOld As String
Dim X As Integer
Dim XL As Object
Dim XLBook As Excel.Workbook
Dim XLSheet As Excel.Worksheet
Dim XLCurrentRange As Excel.Range
Dim XLRowNo As Integer
Dim strSLabel As String
Dim Y As Integer
Dim strSLabels As String
Dim blnFirstRec As Boolean
Dim strTemp As String
Dim strSQLSelect As String
Dim strSQLFrom As String
Dim qryDef As QueryDef
Dim intQD As Integer


'Open a filtered version of qryReportBaseLevel2
Set db = CurrentDb
Set qryDef = db.CreateQueryDef("TempQD")
If strSQL1 = "" Then
db.QueryDefs.Delete ("TempQD")
Exit Function
Else
If strSQL2 = "" Then
strTemp = qryDef.SQL
intQD = InStr(1, strTemp, "FROM")
strSQLSelect = Left(strTemp, intQD - 1)
strSQLFrom = Right(strSQL1, Len(strSQL1) - intQD + 1)
qryDef.SQL = strSQLSelect _
& strSQLFrom
Else
If strSQL3 = "" Then
strTemp = qryDef.SQL
intQD = InStr(1, strTemp, "FROM")
strSQLSelect = Left(strTemp, intQD - 1)
strSQLFrom = Right(strSQL1, Len(strSQL1) - intQD + 1)
qryDef.SQL = strSQLSelect _
& strSQL2 _
& strSQLFrom
Else
If strSQL4 = "" Then
strTemp = qryDef.SQL
intQD = InStr(1, strTemp, "FROM")
strSQLSelect = Left(strTemp, intQD - 1)
strSQLFrom = Right(strSQL1, Len(strSQL1) - intQD + 1)
qryDef.SQL = strSQLSelect _
& strSQL2 _
& strSQL3 _
& strSQLFrom
Else
If strSQL5 = "" Then
strTemp = qryDef.SQL
intQD = InStr(1, strTemp, "FROM")
strSQLSelect = Left(strTemp, intQD - 1)
strSQLFrom = Right(strSQL1, Len(strSQL1) - intQD + 2)
qryDef.SQL = strSQLSelect _
& strSQL2 _
& strSQL3 _
& strSQL4 _
& strSQLFrom
Else
If strSQL6 = "" Then
strTemp = qryDef.SQL
intQD = InStr(1, strTemp, "FROM")
strSQLSelect = Left(strTemp, intQD - 1)
strSQLFrom = Right(strSQL1, Len(strSQL1) - intQD + 2)
qryDef.SQL = strSQLSelect _
& strSQL2 _
& strSQL3 _
& strSQL4 _
& strSQL5 _
& strSQLFrom
Else
strTemp = qryDef.SQL
intQD = InStr(1, strSQL1, "FROM")
strSQLSelect = Left(strSQL1, intQD - 1)
strSQLFrom = Right(strSQL1, Len(strSQL1) - intQD + 2)
qryDef.SQL = strSQLSelect _
& strSQL2 _
& strSQL3 _
& strSQL4 _
& strSQL5 _
& strSQL6 _
& strSQLFrom
End If
End If
End If
End If
End If
End If
Set qryDef = Nothing

Set rstReportQuery = db.OpenRecordset("TempQD")
rstReportQuery.MoveFirst




strX = db.QueryDefs("TempQD").SQL
debug.Print strX
SELECT Age, BirdType, Flock, House, [Lab#], Organ, Origin, SampleDate, SampleType, Site, Source, SourceGroup, [VT#], ProfileID, APIName1, APIAmts1, APILevels1, FullVals1, Match1, APIName2, APIAmts2, APILevels2, FullVals2, Match2, APIName3, APIAmts3, APILevels3, FullVals3, Match3, APIName4, APIAmts4, APILevels4, FullVals4, Match4, APIName5, APIAmts5, APILevels5, FullVals5, Match5, APIName6, APIAmts6, APILevels6, FullVals6, Match6, APIName7, APIAmts7, APILevels7, FullVals7, Match7, APIName8, APIAmts8, APILevels8, FullVals8, Match8, APIName9, APIAmts9, APILevels9, FullVals9, Match9, APIName10, APIAmts10, APILevels10, FullVals10, Match10, APIName11, APIAmts11, APILevels11, FullVals11, Match11, APIName12, APIAmts12, APILevels12, FullVals12, Match12, APIName13, APIAmts13, APILevels13, FullVals13, Match13, APIName14, APIAmts14, APILevels14, FullVals14, Match14, APIName15, APIAmts15, APILevels15, FullVals15, Match15, APIName16, APIAmts16, APILevels16, FullVals16, Match16, APIName17, APIAmts17, APILevels17, FullVals1
7, Match17, APIName18, APIAmts18, APILevels18, FullVals18, Match18, APIName19, APIAmts19, APILevels19, FullVals19, Match19, APIName20, APIAmts20, APILevels20, FullVals20, Match20, APIName21, APIAmts21, APILevels21, FullVals21, Match21, APIName22, APIAmts22, APILevels22, FullVals22, Match22, APIName23, APIAmts23, APILevels23, FullVals23, Match23, APIName24, APIAmts24, APILevels24, FullVals24, Match24, APIName25, APIAmts25, APILevels25, FullVals25, Match25 FROM qryReportBaseLevel2 ORDER BY qryReportBaseLevel2.SourceGroup, qryReportBaseLevel2.Source, qryReportBaseLevel2.ProfileID;
 
Okay it has nothing to do with the "line break".
Did you try to paste it into a query and see if you still get the error, (the result in the Immediate Window from debug.Print strX)?
Else you have to take a way one field at a time until you find the one which cause the problem.
Have you controlled that "qryReportBaseLevel2" runs ok?
 
Thanks JHB. I found one error in qryReportBaseLevel2 and fixed it and ran the query with no problem. I still got the error. So I am trying for a sixth time to find a different way to do the whole thing and think I have found a way. Fingers crossed! Thanks for your help!
 
You must log in or register to reply here.

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom