Type mismatch Error Running A Query

[DavidWE]

I am getting a "Run-time error 13 - Type mismatch" on the following statement.

Set rs2 = db2.OpenRecordset(vSql, dbOpenDynaset, dbSeeChanges)

Here are the other lines of codes:

Dim EmpIDHold As String
Dim db2 As Database
Dim rs2 As Recordset
Dim vSql As String
Set db2 = CurrentDb

vSql = "Select * From PiecePay Where EmpID =' EmpIDHold & '"

'this line gets the error
Set rs2 = db2.OpenRecordset(vSql, dbOpenDynaset, dbSeeChanges)

EmpID is defined as a text field in the table PiecePay. Since EmpIDHold is defined as a string, I don't know how that could cause the mismatch. Do I have any of my quotes wrong in the select statement? Is there anything wrong with the Set statement? I want to edit the record if it is found. If it is not found, I will add it.

EmpIDHold has the value "1987" when the error occurs.

Thanks for any help.

 

[boblarson]

Is the EmpID field in the table TEXT or Numeric? If numeric then don't use the quotes.

 

[boblarson]

Sorry, missed that you said that.

 

[boblarson]

Is that EmpID actually in that table?

 

[datAdrenaline]

>> Dim rs2 As Recordset <<

rs2 is probably being typed as an ADO recordset (Check your references and if ADO is above DAO, the ADO will get the priority) ... So ... explicitly declare your object variable like this:

Dim rs2 As DAO.Recordset

Then you are telling Access exactly which "Recordset" object type you wish to use for rs2 object variable.

....

As a good practice, often times object variable are explicitly declared. Escpecially since DAO and ADO use many of the same identifiers (Recordset, Field, Connection) so I would recommend explicitly declaring your Database object too ...

Dim db2 As DAO.Database

...

 

[boblarson]

>> Dim rs2 As Recordset <<

rs2 is probably being typed as an ADO recordset (Check your references and if ADO is above DAO, the ADO will get the priority) ... So ... explicitly declare your object variable like this:

Dim rs2 As DAO.Recordset

Then you are telling Access exactly which "Recordset" object type you wish to use for rs2 object variable.

Duh...........I think I need to take a vacation from it all.

 

[DavidWE]

Brent,
That fixed it. I have code that works without explicitly declaring the object variable, so I am not sure why it caused an error this time. I will remember to do that from now on. Thanks for the answer.

Bob,
That particular value is not yet in the table. We now know that was not the cause of the error anyway. Thanks for responding and trying to narrow down the problem.

 

[gemma-the-husky]

vSql = "Select * From PiecePay Where EmpID =' EmpIDHold & '"

just a typo i think - the & symbol at the wrong place

try this

vSql = "Select * From PiecePay Where EmpID = " & chr(34) & EmpIDHold & chr(34)

 

[DavidWE]

Gemma,
It works both ways. Thanks for the reply.

 

[DavidWE]

I just added another parameter to the query and am now getting another error. This time the error is "Run-time error 3464 - data type mismatch in criteria expression." I know that error has to do with using quotes with a date field. I've tried placing # signs in the expression but must be positioning them wrong. Someone helped me with a similiar problem the other day. I think it was Bob.

vSql = "Select * From PiecePay Where EmpID =' EmpIDHold & '" & _
 "AND PieceDate =' DateHold & '"

The error again occurs on this code:

Set rs2 = db2.OpenRecordset(vSql, dbOpenDynaset, dbSeeChanges)

Thanks

 

[DavidWE]

The following gets rid of the error, but I am not sure if my query is finding the record it should find. That might end up as another thread if I can't figure it out.

vSql = "Select * From PiecePay Where EmpID =' EmpIDHold & '" & _
          "AND PieceDate =#" & DateHold & "#"

Can anyone see how the above might not return an accurate result?

 

[MSAccessRookie]

The following gets rid of the error, but I am not sure if my query is finding the record it should find. That might end up as another thread if I can't figure it out.

vSql = "Select * From PiecePay Where EmpID =' EmpIDHold & '" & _
          "AND PieceDate =#" & DateHold & "#"

Can anyone see how the above might not return an accurate result?

It looks to like the query might have some quotes in the wrong places (Add the text in RED and remove the code in GREEN):

vSql = "Select * From PiecePay Where EmpID ='[B][SIZE=4][COLOR=red]" &[/COLOR][/SIZE][/B] EmpIDHold & [B][SIZE=4][COLOR=darkgreen]'" &[/COLOR][/SIZE][/B] _
          "[B][SIZE=4][COLOR=red]'[/COLOR][/SIZE][/B] AND PieceDate =#" & DateHold & "#"

 

[DavidWE]

MS,
Thanks, that works. I'm getting correct results. Where to place those tick marks (') always confuses me.

 

[MSAccessRookie]

MS,
Thanks, that works. I'm getting correct results. Where to place those tick marks (') always confuses me.

Glad I could help to get it straight. I assure you that you are not alone in your frustrations. That issue of " vs. ' may be one of the more confusing aspects to using SQL queries in Visual Basic.

 

[gemma-the-husky]

i always use chr(34) - its the " symbol, rather than embedding " inside single quotes.

just chr(34) & textfield & chr(34)

you can even say
dim qut as string
qut=chr(34)

then say

qut & textfield & qut

unfortunately you CAN'T say

const qut = chr(34)

you might be able to say

const qut = "", but i'm not sure

 

[datAdrenaline]

Okay ... here's how the dbl-qoute, single quote thing works ...

If you want to use dbl-quotes in a literal string that is delimited by dbl-quotes, you can double them up ...

strString = """Hello"""

Will yeild "Hello" (including the dbl-quotes) into the variable strString

Likewise you can create a constant that is a double dbl-quote, or a single dbl-quotes and use that appropriately if you wish ..

Const cDDQ = """"""
Const cDQ = """"

....

The single quotes behave in much the same way, if you need a single quote in a string literal that is delimited by single quotes, you have to double it to get the single quote to act like a character ...

strSQL = "SELECT * FROM MyTable WHERE TextField='" & "Mike''s" & "'"

will yeild ....

SELECT * FROM MyTable WHERE TextField = 'Mike''s'
{note all single quotes}

But when evaluated by the SQL command interpreter, the literal term Mike''s will be looked upon as Mike's simply because the literal string was delimited by single quotes, thus two single quotes with in the literal will be interpreted as the single quote character.

....

Note also, that a literal string delimited by dbl-quotes can contain single quotes just fine ...

strMyString = "Mike's"

Just like a dbl-quote can can contained in a literal string delimited by single quotes ...

strSQL = "SELECT * FROM MyTable WHERE TextField = 'A dbl quote ("")'"

Which would store

SELECT * FROM MyTable WHERE TextField = 'A dbl quote (")'

In strSQL, and thus the SQL command interpreter would utilize the dbl-quote as the user intended.

....

As a point of note, sometimes folks wind up with a runtime error (3075 & 3078 are two that I recall) because of an extraneous dbl-quote ...

strSQL = "UPDATE tblTracker SET TheSource =""" & Me.RecordSource & """"
CurrentDb.Execute strSQL

Will yield an error if the RecordSource is set to something like this ...

SELECT * FROM Table ORDER BY "MyFeild" DESC

.....

Hope that helps ....