Error: Type Mismatch - access + vb 6.0

[JerieLsky]

I'm having this error: type mismatch.. i dont actually get/understand the error.. i think the error is in the WHERE clause [tblUserAccount].[UserID].. the field [UserID] is set to a data type Autonumber, and the roleUserID is set as integer..

Global rolesDbs as new adodb.connection
Global rolesRs as new adodb.recordset

 rolesDbs.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " & App.Path & "\library.mdb;MODE=ReadWrite"
        rolesDbs.CursorLocation = adUseClient
        Set rolesRs.ActiveConnection = rolesDbs
        rolesRs.LockType = adLockOptimistic
        rolesRs.CursorType = adOpenDynamic
        rolesRs.Source = "SELECT [tblRoles].[UserID], [tblRoles].[Role] " & _
                         "FROM [tblUserAccount] INNER JOIN [tblRoles] ON ([tblUserAccount].[UserID] = [tblRoles].[UserID]) " & _
                         "WHERE [tblUserAccount].[UserID] = " + roleUserID + ""
        rolesRs.Open , rolesDbs, , , adCmdText

help me out here please.. thanks

[vb5prgrmr]

You cannot "add" strings together (+), you can however concatinate strings together (&). As for everything else, it looks fine at first glance.

Good Luck

[JerieLsky]

well if i tried changing the (+) sign using the (&) sign the error now changes to: Data type mismatch in criteria expression. So how's that? I'm not actually concatinating it, im comparing it using the WHERE clause.

[ProfessorPC]

try this

"WHERE [tblUserAccount].[UserID] = '" & roleUserID & "'"

single quotes around roleuserid

[JerieLsky]

try this

"WHERE [tblUserAccount].[UserID] = '" & roleUserID & "'"

single quotes around roleuserid

"WHERE [tblMembership].[UserID] ='" & Trim(roleUserID) & "'"

in this code if i place a single quotes the error is "Data type mismatch in criteria expression." but if i remove the single quotes, what will happen is that i am already concatinating the string together, which causes an error..

[ProfessorPC]

if you are pulling data from one table only why use the join?

"SELECT UserID, Role FROM tblRoles where UserID = '" & roleUserID & "'"

[JerieLsky]

I ain't actually getting data from only one table, i need some datas also from the other table that's why i applied the INNER JOIN, i just don't know why it shows an error with the WHERE clause.. please, need back up :D ..

[ProfessorPC]

in your query you are only pulling from one table. i guess its throwing me off. what are the fields from the other table you are using? can you test your query in your db's query analyser?

[JerieLsky]

in your query you are only pulling from one table. i guess its throwing me off. what are the fields from the other table you are using? can you test your query in your db's query analyser?

Ok, i tried this Query from only one table which is Table Membership:

"SELECT * FROM [tblMembership] WHERE [tblMembership].[UserID] = '"+ rolesUserID + "'"

if i remove the WHERE clause, i get no errors, but if place that WHERE clause in the Query it pops up the error: Type Mismatch. In the table Membership, i set the data type of UserID to Number, and the variable rolesUserID to Integer.
The rolesUserID is actually a variable that i have set to make a comparison to [tblMembership].[UserID].

rolesUserID = usersRs.Fields("UserID").Value

i dont know what's causing the problem.

[vb5prgrmr]

Seems like user id is a numeric field. Remove the single ticks (') from around your variable.

Good Luck

[JerieLsky]

Seems like user id is a numeric field. Remove the single ticks (') from around your variable.

Good Luck

Yes, UserID stores a numeric value.. The problem if i remove the (') single quotes, what would happen is that it makes a concatination which makes the SQL statement wrong or produces a SQL error..

"SELECT * FROM [tblMembership] WHERE [tblMembership].[UserID] = " + roleUserID + ""

- this produces a concatination.

[vb5prgrmr]

Because once again you are trying to add strings together (+), you are supposed to concatinate them (&)

"SELECT * FROM tblMembership WHERE UserID = " & roleUserID

Good Luck