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

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


Good Luck

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.

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..

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

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

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 ..

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?

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.

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

Good Luck

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.

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

"SELECT * FROM tblMembership WHERE UserID = " & roleUserID

Good Luck

This article has been dead for over six months. Start a new discussion instead.