By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,836 Members | 1,871 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,836 IT Pros & Developers. It's quick & easy.

"datatype mismatch error" in MS Access

P: n/a
For some reason, my posts are scrubbed as attachments.
Lets hope that sending from the yahoo account works.

I'm new to Python and I'm trying to do some database
work with MS Access, but I can't seem to get around a
"datatype mismatch error". Here's an example table
that I'm working with...

ID name dept
1 steve acct
2 mike acct
3 george payroll
4 frank payroll
import win32com.client
engine = win32com.client.Dispatch("DAO.DBEngine.36") db = engine.OpenDatabase(r"c:\python-access\db4.mdb") rs3 = db.OpenRecordset("work") rs3 = db.OpenRecordset("select * from work where ID = 3 ")dept = rs3.Fields("dept")
print dept payroll

The above does exactly what I want, except I'd like to
use a variable instead of the number 3.
When I try I get the following...
idnum = 3
rs3 = db.OpenRecordset("select * from work where ID

= '%i'" %(idnum))
Traceback (most recent call last):
File "<interactive input>", line 1, in ?
File
"win32com\gen_py\00025E01-0000-0000-C000-000000000046x0x5x0.py",
line 508, in OpenRecordset
ret = self._oleobj_.InvokeTypes(1610809383, LCID,
1, (9, 0), ((8, 1), (12, 17), (12, 17), (12,
17)),Name,
Type, Options, LockEdit)com_error: (-2147352567,
'Exception occurred.', (0, 'DAO.Database', 'Data type
mismatch in
criteria expression.', 'jeterr40.chm', 5003464,
-2146824824), None)
The data type for the ID field in the table is
"number" so why am I recieving the datatype mismatch
error?
I'd really appreciate some help, if anyone has any
suggestions. Thanks!
__________________________________
Do you Yahoo!?
Yahoo! Finance: Get your refund fast by filing online.
http://taxes.yahoo.com/filing.html

Jul 18 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a

"Stephen Briley" <sd*****@yahoo.com> schrieb im Newsbeitrag
news:ma***************************************@pyt hon.org...
| For some reason, my posts are scrubbed as attachments.
| Lets hope that sending from the yahoo account works.
|
| I'm new to Python and I'm trying to do some database
| work with MS Access, but I can't seem to get around a
| "datatype mismatch error". Here's an example table
| that I'm working with...
|
| ID name dept
| 1 steve acct
| 2 mike acct
| 3 george payroll
| 4 frank payroll
| >>> import win32com.client
| >>> engine =
| win32com.client.Dispatch("DAO.DBEngine.36")
| >>> db =
| engine.OpenDatabase(r"c:\python-access\db4.mdb")
| >>> rs3 = db.OpenRecordset("work")
|
| >>>rs3 = db.OpenRecordset("select * from work where ID
| = 3 ")
| >>>dept = rs3.Fields("dept")
| >>>print dept
| payroll
|
| The above does exactly what I want, except I'd like to
| use a variable instead of the number 3.
| When I try I get the following...
|
| >>>idnum = 3
| >>>rs3 = db.OpenRecordset("select * from work where ID
| = '%i'" %(idnum))

Have you tried it without the single quotes around %i?

Regards,
Vincent Wehren


| Traceback (most recent call last):
| File "<interactive input>", line 1, in ?
| File
| "win32com\gen_py\00025E01-0000-0000-C000-000000000046x0x5x0.py",
| line 508, in OpenRecordset
| ret = self._oleobj_.InvokeTypes(1610809383, LCID,
| 1, (9, 0), ((8, 1), (12, 17), (12, 17), (12,
| 17)),Name,
| Type, Options, LockEdit)com_error: (-2147352567,
| 'Exception occurred.', (0, 'DAO.Database', 'Data type
| mismatch in
| criteria expression.', 'jeterr40.chm', 5003464,
| -2146824824), None)
|
|
| The data type for the ID field in the table is
| "number" so why am I recieving the datatype mismatch
| error?
| I'd really appreciate some help, if anyone has any
| suggestions. Thanks!
|
|
| __________________________________
| Do you Yahoo!?
| Yahoo! Finance: Get your refund fast by filing online.
| http://taxes.yahoo.com/filing.html
|
Jul 18 '05 #2

P: n/a
Stephen Briley <sd*****@yahoo.com> wrote:

For some reason, my posts are scrubbed as attachments.
That's because you are posting in HTML. Switch back to plain text for
newsgroups and everyone will be much happier.
I'm new to Python and I'm trying to do some database
work with MS Access, but I can't seem to get around a
"datatype mismatch error". Here's an example table
that I'm working with...
...
When I try I get the following...
idnum = 3
rs3 = db.OpenRecordset("select * from work where ID

= '%i'" %(idnum))
Traceback (most recent call last):
...
'Exception occurred.', (0, 'DAO.Database', 'Data type
mismatch in criteria expression.', 'jeterr40.chm', 5003464,
-2146824824), None)

The data type for the ID field in the table is
"number" so why am I recieving the datatype mismatch
error?


Two reasons. First, the sprintf code for numbers is %d, not %i. Second,
you are wrapping the number in single quote marks, which turns it into a
string. Try this:

rs3 = db.OpenRecordset("select * from work where ID = %d" % idnum)
--
- Tim Roberts, ti**@probo.com
Providenza & Boekelheide, Inc.
Jul 18 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.