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