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

MS Access datatype mismatch error

P: n/a


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!
Jul 18 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Steve Briley <sd*****@hotmail.com> pisze:
<html><div style='background-color:'>


Sorry, pal. Your message can not be read by some of us. Try again.

--
Jarek Zgoda
Unregistered Linux User #-1
http://www.zgoda.biz/ JID:zgoda-a-chrome.pl http://zgoda.jogger.pl/
Jul 18 '05 #2

P: n/a
In article <ma***************************************@python. org>, Steve
Briley <sd*****@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 deptpayroll

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
Jul 18 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.