Type Mismatch

[Yuvraj desai]

Hi team,

I am getting type mismatch error when I run below code

I am trying to fetch month & Year column from separate sheet through SQL. Interestingly only first "strSQL" only throws an error. I am getting stuck during adding data to the combo box

strSQL = "Select [OMS$].[Month] From [OMS$] " & _
"Order by [OMS$].[Sr], [OMS$].[Month]"

' strSQL = "Select [Sheet3$].[Month] From [Sheet3$] " & _
"Order by [Sheet3$].[Sr], [Sheet3$].[Month]"

closeRS
OpenDB

THIS_MONTH.Clear
PREV_MONTH.Clear
LAST_MONTH.Clear

rs.Open strSQL, cnn, adOpenKeyset, adLockOptimistic
If rs.RecordCount > 0 Then
Do While Not rs.EOF
THIS_MONTH.AddItem rs.Fields(0)
PREV_MONTH.AddItem rs.Fields(0)
LAST_MONTH.AddItem rs.Fields(0)

rs.MoveNext
Loop
Else
MsgBox "BYE", vbCritical + vbOKOnly
Exit Sub
End If

 

[Chihiro]

Check link below.
http://stackoverflow.com/questions/28729023/type-mismatch-error-on-adodb-recordset

Also depending on data type present in your column (ex. text & numeric mixed) you may get unexpected result.

 

[Yuvraj desai]

Thanks !! but in below code also I am getting the same error where all the columns are ok and has desired data type

strSQL = "SELECT [OMS$].[Region],[OMS$].[SITE],([LOGICS$].[Total Volume Shipped] + [LOGICS$].[Total Volume Received])," & _
"[PLATO$].[Jan]" & _
"FROM ([OMS$] LEFT JOIN [LOGICS$] ON [OMS$].[SITE] = [LOGICS$].[Site])" & _
"LEFT JOIN [PLATO$] ON [OMS$].[SITE] = [PLATO$].[SITE]" & _
"GROUP BY [OMS$].[Region],[OMS$].[SITE]," & _
"[LOGICS$].[Total Volume Shipped],[LOGICS$].[Total Volume Received],[PLATO$].[Jan]"


regards
Yuvraj

 

[Chihiro]

Hmm? You are getting error on String variable?

Or do you mean that when you manipulate "rs" downstream that you are getting error?

Read through answer in the link I provided and verify that there is record in your recordset. Check ConnectionString construction/syntax as well.

Also, you can look at the thread linked below for examples using ADODB with Excel file to add data to Combobox

http://forum.chandoo.org/threads/vba-combobox.32282/
http://forum.chandoo.org/threads/vba-mylookup-combobox-and-label-caption.32416/

Below link has pretty good tutorial on how to use ADO with Excel (with good list of common mistakes). Note that it is from over 10 years ago and some info is outdated (Such as Excel 8.0 section, Provider, etc). But the principle remains same.

http://www.xtremevbtalk.com/tutors-corner/217783-using-ado-excel-files.html

 

[Yuvraj desai]

Thanks !! given link was useful.
instead of doing sum operations in query, I imported [volume shipped] & [volume received column] in text data type. did sum operations in other query