"datatype mismatch error" in MS Access

[Stephen Briley]

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[color=blue][color=green][color=darkred]
>>> import win32com.client
>>> engine =[/color][/color][/color]
win32com.client .Dispatch("DAO. DBEngine.36")[color=blue][color=green][color=darkred]
>>> db =[/color][/color][/color]
engine.OpenData base(r"c:\pytho n-access\db4.mdb" )[color=blue][color=green][color=darkred]
>>> rs3 = db.OpenRecordse t("work")[/color][/color][/color]
[color=blue][color=green][color=darkred]
>>>rs3 = db.OpenRecordse t("select * from work where ID[/color][/color][/color]
= 3 ")[color=blue][color=green][color=darkred]
>>>dept = rs3.Fields("dep t")
>>>print dept[/color][/color][/color]
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...
[color=blue][color=green][color=darkred]
>>>idnum = 3
>>>rs3 = db.OpenRecordse t("select * from work where ID[/color][/color][/color]
= '%i'" %(idnum))
Traceback (most recent call last):
File "<interacti ve input>", line 1, in ?
File
"win32com\gen_p y\00025E01-0000-0000-C000-000000000046x0x 5x0.py",
line 508, in OpenRecordset
ret = self._oleobj_.I nvokeTypes(1610 809383, LCID,
1, (9, 0), ((8, 1), (12, 17), (12, 17), (12,
17)),Name,
Type, Options, LockEdit)com_er ror: (-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

[Vincent Wehren]

Re: &quot;dataty pe mismatch error&quot; in MS Access


"Stephen Briley" <sdb1031@yahoo. com> schrieb im Newsbeitrag
news:mailman.12 85.1076035428.1 2720.python-list@python.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.OpenData base(r"c:\pytho n-access\db4.mdb" )
| >>> rs3 = db.OpenRecordse t("work")
|
| >>>rs3 = db.OpenRecordse t("select * from work where ID
| = 3 ")
| >>>dept = rs3.Fields("dep t")
| >>>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.OpenRecordse t("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 "<interacti ve input>", line 1, in ?
| File
| "win32com\gen_p y\00025E01-0000-0000-C000-000000000046x0x 5x0.py",
| line 508, in OpenRecordset
| ret = self._oleobj_.I nvokeTypes(1610 809383, LCID,
| 1, (9, 0), ((8, 1), (12, 17), (12, 17), (12,
| 17)),Name,
| Type, Options, LockEdit)com_er ror: (-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
|

[Tim Roberts]

Re: &quot;dataty pe mismatch error&quot; in MS Access

Stephen Briley <sdb1031@yahoo. com> wrote:[color=blue]
>
>For some reason, my posts are scrubbed as attachments.[/color]

That's because you are posting in HTML. Switch back to plain text for
newsgroups and everyone will be much happier.
[color=blue]
>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...
>[color=green][color=darkred]
>>>>idnum = 3
>>>>rs3 = db.OpenRecordse t("select * from work where ID[/color][/color]
>= '%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?[/color]

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.OpenRecordse t("select * from work where ID = %d" % idnum)
--
- Tim Roberts, timr@probo.com
Providenza & Boekelheide, Inc.