We help IT Professionals succeed at work.

Data Type Mismatch in Recordset

vmccune
vmccune asked
on
Hello,

I am getting a DataType Mismatch on this attempt to select a recordset from another recordsets ID field.  I have verified that both ID's in the tables are numbers.

Set rst = CurrentDb.OpenRecordset("SELECT * FROM [Here Are My Records] WHERE [ID] = '" & rstSend![ID] & "';")
Comment
Watch Question

Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
[Here Are My Records] --> you have table name correct ?
Since the ID is a number you don't need the single quotes.  Those are for used for Text data types.
Set rst = CurrentDb.OpenRecordset("SELECT * FROM [Here Are My Records] WHERE [ID] = " & rstSend![ID]) 

Open in new window


Ron
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Please try this -

You dont need a singleQuote if you are comparing numbers.

Set rst = CurrentDb.OpenRecordset("SELECT * FROM [Here Are My Records] WHERE [ID] = " & rstSend![ID] & "")

Author

Commented:
yes.

Author

Commented:
It is a query actually
Database Expert
Awarded 2016
Top Expert 2016
Commented:
Then use this

Set rst = CurrentDb.OpenRecordset("SELECT * FROM ([Here Are My Records]) as k WHERE [ID] = " & rstSend![ID] & "")

Author

Commented:
[Here are My Records]
Description      Our Information      Corrections      Id      Sort
First Name      Abhay            1986      1
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Please try my last suggestion..

Author

Commented:
that thows up an error Syntax Error in From Clause

Author

Commented:
Got it.

Took out "As K"

Author

Commented:
Set rst = CurrentDb.OpenRecordset("SELECT * FROM ([Here Are My Records]) WHERE [ID] = " & rstSend![ID] & "")
Have you tried my suggestion?  Btw @Pawan, you don't need to add the ampersand and quotes at the end since its a number and not text.

Ron
Wow, I feel like a ghost here.  I gave the correct answer in the 2nd post in this thread and I was ignored completely.
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
@IrogSinta - You missed the brackets around [Here Are My Records].
I have brackets around [Here Are My Records].  If you're referring to parenthesis, then those are not needed.  Neither is "as K" and the ending ampersand and quotes.

It would work fine just as:
Set rst = CurrentDb.OpenRecordset("SELECT * FROM [Here Are My Records] WHERE [ID] = " & rstSend![ID])
@vmccune, in order to steer you correctly when writing similar SQL statements in Access, even though the answer you selected works, it has syntax that is unnecessary.   It doesn't matter if it's a table or query you're referencing, parenthesis ( ) around that object's name is not needed.  Now if your table name has spaces or certain reserved symbols (e.g. #, %) then you need brackets [ ] around the name.  You also don't need to end this particular statement with an ampersand and quotes since there's nothing inside the quotes.

Ron