MS Access datatype mismatch error

Ian Parker parker at gol.com
Sat Feb 7 12:23:55 CET 2004


In article <mailman.1282.1076033483.12720.python-list at python.org>, Steve 
Briley <sdb1031 at hotmail.com> writes
>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!
>
>
>Choose now from 4 levels of MSN Hotmail Extra Storage - no more
>account overload!

Perhaps you don't need the single quotes around %i.  e.g. try:
 >>>rs3 = db.OpenRecordset("select * from work where ID = %i" % (idnum))

Regards
-- 
Ian Parker



More information about the Python-list mailing list