# Access: RunTime Error '13' - Type Mismatch



## Rollin_Again (Sep 4, 2003)

I am pulling my hair out trying to figure this one out. 

I'm using VBA in IBM Reflections to call an Access database and run a query. I am gettting a type mismatch error on the last line of the code below. This code works fine on about 6 other machines but keeps giving the mismatch error on one specific pc that is running the exact same version of Access and has the exact same references set. Can anyone shed some light on what might be causing this? Each machine has reference to Microsoft DAO 3.6 and none have any reference to ADO.


```
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qd As DAO.QueryDef
Set objaccess = CreateObject("Access.Application")
objaccess.OpenCurrentDatabase filepath:=dbname  ' dbname is a variable value.
Set dbs = objaccess.CurrentDb
Set qd = dbs.QueryDefs!full_range_by_key 'This line gives the error
```
Regards,
Rollin


----------



## OBP (Mar 8, 2005)

Rollin, are the Underscores in the Query Name?
If not try using the square brackets
Set qd = dbs.QueryDefs![full range by key]


----------



## Rollin_Again (Sep 4, 2003)

Yes the underscores are part of the query name. Prior to your suggestion I had already tried with brackets, parentheses, double quotes, etc. Nothing seems to work and I have no clue why only one machine is giving the error. Do you know of any other way to pass the query values via VBA and have the results returned directly to a recordset? In the past I have used a form to store my variable values and this allows me to run the query and display the results on the screen but in this case I need the results returned directly to a recordset so that I can pass the values to another application.


Regards,
Rollin


----------



## rconverse (Sep 8, 2007)

Rollin_Again said:


> Yes the underscores are part of the query name. Prior to your suggestion I had already tried with brackets, parentheses, double quotes, etc. Nothing seems to work and I have no clue why only one machine is giving the error. *Do you know of any other way to pass the query values via VBA and have the results returned directly to a recordset?* In the past I have used a form to store my variable values and this allows me to run the query and display the results on the screen but in this case I need the results returned directly to a recordset so that I can pass the values to another application.
> 
> Regards,
> Rollin


Both you and OBP are waaaaaaaay beyond my knowledge in Access, so I feel foolish even offering a suggestion, because I am sure anything I could come up with you've already thought through.

But, if you want to return results of a query to a recordset, you could use this:


```
dim db as databse, rst as recordset, strsql as string

set db = currentdb()

strsql = ""

set rst = openrecordset(strsql)
```
Then you can reference the fields:

rst!field1
rst!feild2
etc...

HTH - or at the minimum wasn't too much of a waste of time.

Roger


----------



## Rollin_Again (Sep 4, 2003)

If the SQL statement was simple I could easily code this. The database however was designed by someone else and the query is a little too complex for me. I have played around with it but am unable to code the correct VBA equivalent.

If I view the QUERY in SQL view in Access it shows as what is listed below. My problem is translating this into the correct VBA syntax. I basically need the VBA equivalent of what is listed below but I would like to replace *[Forms]![Form1]![Text0]* and *[Forms]![Form1]![Text2]* with variable names instead.


```
SELECT CUSTOMERS_WITH_LIST.*, separators.title, Replace([agency_soundex],"@","-") AS barcode
FROM separators INNER JOIN CUSTOMERS_WITH_LIST ON separators.businessID = CUSTOMERS_WITH_LIST.AGENT_FLAG
WHERE (((CUSTOMERS_WITH_LIST.AGENT_FLAG)=[Forms]![Form1]![Text0]) AND ((CUSTOMERS_WITH_LIST.ACTUAL_KEY)=[Forms]![Form1]![Text2]))
ORDER BY CUSTOMERS_WITH_LIST.NODEID9;
```
Regards,
Rollin


----------



## Rollin_Again (Sep 4, 2003)

OK I thought I had the SQL statement fixed but now I am getting an error telling me "Too Few Parameters: Expected 2"

This error is being thrown on the last line of code below when I try to open the recordset.


```
strSql = "SELECT CUSTOMERS_WITH_LIST.*, separators.title, Replace([agency_soundex],""@"",""-"") AS barcode " & vbCrLf & _
"FROM separators INNER JOIN CUSTOMERS_WITH_LIST ON separators.businessID = CUSTOMERS_WITH_LIST.AGENT_FLAG " & vbCrLf & _
"WHERE (((CUSTOMERS_WITH_LIST.AGENT_FLAG)=" & vType & ") AND ((CUSTOMERS_WITH_LIST.ACTUAL_KEY)=" & vNode & ")) " & vbCrLf & _
"ORDER BY CUSTOMERS_WITH_LIST.NODEID9;"

Set rst = db.openrecordset(strSql, dbOpenDynaset)
```
Regards,
Rollin


----------



## rconverse (Sep 8, 2007)

Hi Rollin,

Not sure what you are trying to do here. What is dbopendynaset?


```
Set rst = db.openrecordset(strSql, dbOpenDynaset)
```
I typically just use:


```
set rst = OpenRecordset(strSql)
```
It definitely appears that there is an issue, because the O and R should be capitalized in openrecordset.

If you have a couple extra seconds, could you provide a quick tutorial on the benefits of the change you are making. I don't use variables, so I am intrigued.

Thanks,
Roger


----------



## Rollin_Again (Sep 4, 2003)

I finally got this sorted. My SQL statement above was fine I simply forgot to wrap the variable values in double quotes.

The code worked fine once I wrapped the variable with the *chr(34)* tag


```
"WHERE (((CUSTOMERS_WITH_LIST.AGENT_FLAG)=" & chr(34) & vType & chr(34) & ") AND ((CUSTOMERS_WITH_LIST.ACTUAL_KEY)=" & chr(34) & vNode & chr(34) & "))
```
Regards,
Rollin


----------



## Rollin_Again (Sep 4, 2003)

BTW....The second argument that I was passing *dbOpenDynaset* tells the program to open the recordset dynamically. This allows you to add records, delete records and update records as if the recordset was a table. The argument is optional and is NOT required. I removed it in my final code since I only included it as part of my troubleshooting.

Regards,
Rollin


----------



## rconverse (Sep 8, 2007)

Rollin_Again said:


> BTW....The second argument that I was passing *dbOpenDynaset* tells the program to open the recordset dynamically. This allows you to add records, delete records and update records as if the recordset was a table. The argument is optional and is NOT required. I removed it in my final code since I only included it as part of my troubleshooting.
> 
> Regards,
> Rollin


Oh, okay, good to know.

Could you please describe why you need ch34 and why you switched to variables as opposed to the contols?

Thanks,
Roger


----------



## OBP (Mar 8, 2005)

Rollin, well done, as usual working through the problem and trying to explain to others seems to have worked :up:


----------



## Rollin_Again (Sep 4, 2003)

rconverse said:


> Could you please describe why you need ch34 and why you switched to variables as opposed to the contols?
> 
> Thanks,
> Roger


When using a string variable in a SQL statement you need to enclose the value in quotes. If the variable was a numeric value no quotes would be needed. The *chr* function takes the ASCII value of 34 and converts it to the actual double quotation character ( " ). This allows the program to interpret the variable as a "literal" value.

The reason I am using a variables in my SQL statement is because the values that are being used are coming from a different application. Originally I was manually entering these values into the form and clicking on a button to manually run a macro that automatically created a barcode. Since I am automating the entire process I need to find a way to pass the values directly to the query. I could have used code to write the values to the form for me and then run the query using the control names but this would just be an extra uneccesary step.

Regards,
Rollin


----------



## rconverse (Sep 8, 2007)

Okay, I understand.

Thanks!!

Roger


----------



## Rollin_Again (Sep 4, 2003)

I really am about to flip out now. I added the new code to the machine that was giving the problems and am still getting the "Type Mismatch" error. I really have no clue what is going on. I've test the new code on my own machine and it works fine. Both PC's are using the same database and the code is the exact same. What the heck is going on with this thing??

Regards,
Rollin


----------



## JohnWill (Oct 19, 2002)

Unsolved.


----------



## slurpee55 (Oct 20, 2004)

I am sure you have thought of this - just my way of watching this thread, to be honest, but have you tried assigning the value as a variant?
I can't imagine this is really needed - if it works on one machine, it should on the other...oh well, good luck


----------

