Query result: Data type mismatch

[puuts]

Hello,

I'm getting Data type mismatch for the below query, seeking for your advises please.

SqlQuery = "SELECT COUNT(*) AS Report_ID,q1.Report_Name,q1.Report_Owner FROM (SELECT Report_ID,Report_Name,Report_Owner FROM tbl_Monday UNION ALL SELECT Report_ID,Report_Name,Report_Owner FROM tbl_Tuesday UNION ALL SELECT Report_ID,Report_Name,Report_Owner FROM tbl_Wednesday) q1 INNER JOIN(SELECT Report_ID,Report_Name,Report_Owner FROM tbl_Monday UNION ALL SELECT Report_ID,Report_Name,Report_Owner FROM tbl_Tuesday UNION ALL SELECT Report_ID,Report_Name,Report_Owner FROM tbl_Wednesday) q2 on CLng(Val(q1.Report_ID)) + q1.Report_Name + q1.Report_Owner >= CLng(Val(q2.Report_ID)) + q2.Report_Name + q2.Report_Owner GROUP BY q1.Report_ID,q1.Report_Name,q1.Report_Owner"

rs.Open SqlQuery, conn

        With rs
        
            Do While Not .EOF
                Set litem = frm_ALLREPORTS.lv_ALLREPORTS.ListItems.Add(, , .Fields("Report_ID").Value)
                litem.SubItems(1) = .Fields("Report_Name").Value
                                litem.SubItems(2) = .Fields("Report_Owner").Value


.MoveNext
            Loop
            
        End With

conn.Close

Thanks in advance Gurus!

[Norie]

Where do you get the error?

[puuts]

I think on this part Norie

Actually, the original code is in SQL script, I've tried substituing CLng instead of Cast due to access cannot read Cast function.

CLng(Val(q1.Report_ID)) + q1.Report_Name + q1.Report_Owner >= CLng(Val(q2.Report_ID))

btw, Report_ID is my Primary key.

[Norie]

Does that query run when you try and execute it manually in the database?

Does the database you are querying support the functions CLng and Val?

[puuts]

It doesn't run coz' the query seems incorrect. Yes, access database support CLng and Val functions.

The original code was: (It is in MySQL script)

SqlQuery = "SELECT COUNT(*) AS Report_ID,q1.Report_Name,q1.Report_Owner FROM (SELECT Report_ID,Report_Name,Report_Owner FROM tbl_Monday UNION ALL SELECT Report_ID,Report_Name,Report_Owner FROM tbl_Tuesday UNION ALL SELECT Report_ID,Report_Name,Report_Owner FROM tbl_Wednesday) q1 INNER JOIN(SELECT Report_ID,Report_Name,Report_Owner FROM tbl_Monday UNION ALL SELECT Report_ID,Report_Name,Report_Owner FROM tbl_Tuesday UNION ALL SELECT Report_ID,Report_Name,Report_Owner FROM tbl_Wednesday) q2 on Cast(q1.Report_ID as Integer) + q1.Report_Name + q1.Report_Owner >= Cast(q2.Report_ID as Integer) + q2.Report_Name + q2.Report_Owner GROUP BY q1.Report_ID,q1.Report_Name,q1.Report_Owner"

My problem in that code is that Cast function is not supported in ms access and I've search in the internet that CLng, CInt and Val functions can substitute for Cast, but unfortunately, it still gives me an error

for the below code

Cast(q1.Report_ID as Integer) + q1.Report_Name + q1.Report_Owner >= Cast(q2.Report_ID as Integer) + q2.Report_Name + q2.Report_Owner

i've converted it to CLng or CInt, but still it doesn't work.

CInt(Val(q1.Report_ID)) + q1.Report_Name + q1.Report_Owner >= CInt(Val(q2.Report_ID)) + q2.Report_Name + q2.Report_Owner

CLng(Val(q1.Report_ID)) + q1.Report_Name + q1.Report_Owner >= CLng(Val(q2.Report_ID))

[Norie]

Why are you using Val and CInt?

Have you tried just using one of them?

Also, what happens if you don't use either of them?

[puuts]

still get the same error. Either I've use only Val,CLng or CInt.

[Norie]

Where exactly do you get the error?

[puuts]

It jumps on my Errohandler when entering on this line:

rs.Open SqlQuery, conn

My whole code is:

Sub ALL_DATA_REPORTS()


On Error GoTo ErrorHandler

OPEN_DATABASE

SqlQuery = "SELECT COUNT(*) AS Report_ID,q1.Report_Name,q1.Report_Owner FROM (SELECT Report_ID,Report_Name,Report_Owner FROM tbl_Monday UNION ALL SELECT Report_ID,Report_Name,Report_Owner FROM tbl_Tuesday UNION ALL SELECT Report_ID,Report_Name,Report_Owner FROM tbl_Wednesday) q1 INNER JOIN(SELECT Report_ID,Report_Name,Report_Owner FROM tbl_Monday UNION ALL SELECT Report_ID,Report_Name,Report_Owner FROM tbl_Tuesday UNION ALL SELECT Report_ID,Report_Name,Report_Owner FROM tbl_Wednesday) q2 on CLng(q1.Report_ID) + q1.Report_Name + q1.Report_Owner >= CLng(q2.Report_ID) + q2.Report_Name + q2.Report_Owner GROUP BY q1.Report_ID,q1.Report_Name,q1.Report_Owner"


rs.Open SqlQuery, conn

        With rs
        
            Do While Not .EOF
                Set litem = frm_ALLREPORTS.lv_ALLREPORTS.ListItems.Add(, , .Fields("Report_ID").Value)
                litem.SubItems(1) = .Fields("Report_Name").Value
                                litem.SubItems(2) = .Fields("Report_Owner").Value
                                'litem.SubItems(3) = .Fields("Function").Value
                                'litem.SubItems(4) = .Fields("Region").Value
                                'litem.SubItems(5) = .Fields("Comment").Value
                                'litem.SubItems(6) = .Fields("Cut_Off").Value
                                'litem.SubItems(7) = .Fields("Reminder_Time").Value
                                'litem.SubItems(8) = .Fields("Report_Day").Value
            .MoveNext
            Loop
            
        End With

conn.Close

Exit Sub

ErrorHandler:
msg = MsgBox(" Error: " & Err.Description, vbCritical + vbOKOnly, "ERROR FOUND!")


End Sub

[puuts]

I've fix it now. I've used CStr() function instead of CLng,Val or CInt since varchar is used in the original script.