Link to home
Start Free TrialLog in
Avatar of vmccune
vmccuneFlag for United States of America

asked on

Data Type Mismatch in Recordset

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] & "';")
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

[Here Are My Records] --> you have table name correct ?
Avatar of IrogSinta
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
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] & "")
Avatar of vmccune

ASKER

yes.
Avatar of vmccune

ASKER

It is a query actually
ASKER CERTIFIED SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of vmccune

ASKER

[Here are My Records]
Description      Our Information      Corrections      Id      Sort
First Name      Abhay            1986      1
Please try my last suggestion..
Avatar of vmccune

ASKER

that thows up an error Syntax Error in From Clause
Avatar of vmccune

ASKER

Got it.

Took out "As K"
Avatar of vmccune

ASKER

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.
@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