Data Type Mismatch in Recordset

vmccune
vmccune used Ask the Experts™
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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] & "")
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial