VB/Windows CE, Error 3849 when doing SELECT on floating-point field (type adDouble)

Error 3849 when doing SELECT on floating-point field (type adDouble)

Author	Message
Terry McKierna #1 / 6	Error 3849 when doing SELECT on floating-point field (type adDouble) Anyone know why I would be getting Error 3849 when trying to use ADOCE 3.1 to search a field of type adDouble (decimal number)? I have a database application that uses ADOCE 3.1 to read data in a Pocket Access database. The database has several tables with fields of various types, including adChar, adVarChar, adInteger and adDouble. I have a routine, PerformLookup(), that searches the database and displays results, taking search parameters from a form. Searches on all field types work except for those fields of type adDouble. For those, even the simplest queries fail with error 3849, whether there are records matching the criteria or not. The same queries work on other numeric fields like those of type adInteger. Example query: SELECT * FROM Contacts WHERE [NumEmployees] = 500 That works great on the field [NumEmployees] which is type adInteger. The same query on a field of type adDouble fails: SELECT * FROM Contacts WHERE [MonthlyBonus] = 500 where field [MonthlyBonus] is a double, created in Access 2000 as type Double and then converted to Pocket Access via ActiveSynch. Any ideas? Why would queries not work on numeric fields that have decimal places? Is there some trick to the syntax I'm missing? The exact same syntax works on Access 2000. I've also tried it with quotes and double-quotes around with search value, i.e. '500' and it still works in Access 2000 but not through ADOCE 3.1 on the Pocket PC. Thanks for any help! -- Terry Terry McKiernan pinpoint tools www.pinpointtools.com We Make ACT! Work Better!
Fri, 11 Mar 2005 00:50:53 GMT

Terry McKierna #2 / 6	Error 3849 when doing SELECT on floating-point field (type adDouble) I have the answer to this now so maybe this will help someone else. Thanks to Mark Kelly and Chris Tacke who pointed me in the right direction. Basically, this is a known bug but it is very poorly documented, hidden in a note about the SELECT statement in MS's docs on ADOCE. The = and <> operators just don't work with floating-point values. So I did some testing on the other operators, such as <, >, <= adn >=. These all work fine. You can thus rewrite your = and <> statements to use a pair of constraints: Change: SELECT * FROM MyTable WHERE MyField = 5 to: SELECT * FROM MyTable WHERE ((MyField <= 5) AND (MyField >= 5)) And change: SELECT * FROM MyTable WHERE MyField <> 5 to: SELECT * FROM MyTable WHERE ((MyField < 5) OR (MyField > 5)) It's a hassle, and the SELECTs probably run slower, but it works. -- Terry Terry McKiernan pinpoint tools www.pinpointtools.com We Make ACT! Work Better! Quote: > Anyone know why I would be getting Error 3849 when trying to use ADOCE 3.1 > to search a field of type adDouble (decimal number)? > I have a database application that uses ADOCE 3.1 to read data in a Pocket > Access database. The database has several tables with fields of various > types, including adChar, adVarChar, adInteger and adDouble. I have a > routine, PerformLookup(), that searches the database and displays results, > taking search parameters from a form. > Searches on all field types work except for those fields of type adDouble. > For those, even the simplest queries fail with error 3849, whether there are > records matching the criteria or not. The same queries work on other numeric > fields like those of type adInteger. > Example query: > SELECT * FROM Contacts WHERE [NumEmployees] = 500 > That works great on the field [NumEmployees] which is type adInteger. The > same query on a field of type adDouble fails: > SELECT * FROM Contacts WHERE [MonthlyBonus] = 500 > where field [MonthlyBonus] is a double, created in Access 2000 as type > Double and then converted to Pocket Access via ActiveSynch. > Any ideas? Why would queries not work on numeric fields that have decimal > places? Is there some trick to the syntax I'm missing? The exact same syntax > works on Access 2000. I've also tried it with quotes and double-quotes > around with search value, i.e. '500' and it still works in Access 2000 but > not through ADOCE 3.1 on the Pocket PC. > Thanks for any help! > -- > Terry > Terry McKiernan > pinpoint tools > www.pinpointtools.com > We Make ACT! Work Better!
Sat, 12 Mar 2005 03:30:48 GMT

Keld Laurse #3 / 6	Error 3849 when doing SELECT on floating-point field (type adDouble) Quote: > Basically, this is a known bug but it is very poorly documented, hidden in a > note about the SELECT statement in MS's docs on ADOCE. The = and <> > operators just don't work with floating-point values. All I'm wondering is: Will there ever be a fix for all the known bugs? Probably no, as MS wants us all to move to the .NET platform. But then what about the bugs on that platform? Do we dare to use it as MS platforms seem to be abandoned almost as fast as they get designed? Just wondering. Keld Laursen
Sat, 12 Mar 2005 05:22:07 GMT

Chris Tacke, eMV #4 / 6	Error 3849 when doing SELECT on floating-point field (type adDouble) That's assuming this is a bug, which I'm not convinced it is. When using floating point variables (which is the case here) equality and inequality operators should never be used, because of the simple nature of floating point numbers. As I was telling Terry offline, setting the field to a value of "1" does not mean that it will inherently be exactly 1. It will very likely be near the value 1, but will probably differ somewhere out a dozen or so decimal places. If you need to do comparisons against integers, use an integer field or use range comparisons. -- Chris Tacke, eMVP Windows CE Product Manager Applied Data Systems www.applieddata.net Quote: > > Basically, this is a known bug but it is very poorly documented, hidden in > a > > note about the SELECT statement in MS's docs on ADOCE. The = and <> > > operators just don't work with floating-point values. > All I'm wondering is: Will there ever be a fix for all the known bugs? > Probably no, as MS wants us all to move to the .NET platform. But then what > about the bugs on that platform? Do we dare to use it as MS platforms seem > to be abandoned almost as fast as they get designed? > Just wondering. > Keld Laursen
Sat, 12 Mar 2005 05:46:06 GMT

Terry McKierna #5 / 6	Error 3849 when doing SELECT on floating-point field (type adDouble) Chris, I respect your opinions, you've been very helpful to me in the past with your postings, I think I even have one of your books ... but I can't agree with you here. This is a bug. It's not an issue with floating-point representation. Keld has already gone into some of the details of the IEEE floating-point format in a different posting so I won't belabor that here, and besides that's not really my point. OK, so the Microsoft docs (adoce31.chm) say: "An error results if an SQL statement uses the equal operator (=) or the not equal (<>) operator to compare floating-point data types. ... Floating point comparisons are approximate. Testing for equality against floating point values is not supported and returns an invalid field comparison error. " Hmm. So, it's documented, does that mean it's not a bug? Can I let Microsoft off the hook here just because they wrote it down and said "sorry it doesn't do that?" I don't think so. The floating-point comparisons in the SELECTs from my original post may be a bad idea for some values because of approximation issues, true. But, they are still perfectly valid, well-formed SQL statements. If they fail to find any records because of approximate representation in floating-point numbers, so be it -- return 0 records and let me figure out why. I have to do that on all searches, let the user know what happened, take appropriate recovery actions etc. Normal stuff for a database app. Raising an error, on the other hand, is entirely inappropriate. There is no error in the SQL statement itself. I have to structure my code differently and write a workaround each time this situation comes up. And, this same problem does not occur in SQL Server, MS Access 97 & 2000, and probably lots of other database platforms that implement SQL SELECT statements. They just convert the search value to a float and do the search and return the records, exactly as you'd expect. So I say it's a bug. Bug bug bug bug bug bug bug. So there, Microsoft! You have a bug! Does Microsoft care? Does my considered opinion wound them deeply? Well, no. They don't give a hoot. Am I bitter? Oh no, not me... -- Terry Terry McKiernan pinpoint tools www.pinpointtools.com We Make ACT! Work Better! Quote: > That's assuming this is a bug, which I'm not convinced it is. > When using floating point variables (which is the case here) equality and > inequality operators should never be used, because of the simple nature of > floating point numbers. As I was telling Terry offline, setting the field > to a value of "1" does not mean that it will inherently be exactly 1. It > will very likely be near the value 1, but will probably differ somewhere > out a dozen or so decimal places. > If you need to do comparisons against integers, use an integer field or use > range comparisons. > -- > Chris Tacke, eMVP > Windows CE Product Manager > Applied Data Systems > www.applieddata.net > > > Basically, this is a known bug but it is very poorly documented, hidden > in > > a > > > note about the SELECT statement in MS's docs on ADOCE. The = and <> > > > operators just don't work with floating-point values. > > All I'm wondering is: Will there ever be a fix for all the known bugs? > > Probably no, as MS wants us all to move to the .NET platform. But then > what > > about the bugs on that platform? Do we dare to use it as MS platforms seem > > to be abandoned almost as fast as they get designed? > > Just wondering. > > Keld Laursen
Sun, 13 Mar 2005 00:06:29 GMT

Terry McKierna #6 / 6	Error 3849 when doing SELECT on floating-point field (type adDouble) Oh and one other thing: the error number raised when you try to use = or <> on floating-point fields is 3849 / 0xF09. This isn't documented anywhere, as far I can tell -- I've searched all over MSDN, Google, devbuzz, etc. Microsoft does give a list of possible SQL errors, including the following ones in the help file I was quoting: a.. E_OUTOFMEMORY a.. DB_E_ERRORSINCOMMAND a.. DB_E_NOTABLE a.. DB_E_BADCOLUMNID a.. DB_E_CANTCONVERTVALUE a.. DB_E_DATAOVERFLOW But none of these is equal to 3849. That's what got me off on this wild goose chase to begin with. If the error code had at least been documented, I would have found a kbase article on it and Microsoft "it doesn't work so don't do it" statement a long time ago. -- Terry Terry McKiernan pinpoint tools www.pinpointtools.com We Make ACT! Work Better! Quote: > Chris, I respect your opinions, you've been very helpful to me in the past > with your postings, I think I even have one of your books ... but I can't > agree with you here. This is a bug. It's not an issue with floating-point > representation. Keld has already gone into some of the details of the IEEE > floating-point format in a different posting so I won't belabor that here, > and besides that's not really my point. > OK, so the Microsoft docs (adoce31.chm) say: > "An error results if an SQL statement uses the equal operator (=) or the not > equal (<>) operator to compare floating-point data types. > ... > Floating point comparisons are approximate. Testing for equality against > floating point values is not supported and returns an invalid field > comparison error. " > Hmm. So, it's documented, does that mean it's not a bug? Can I let > Microsoft off the hook here just because they wrote it down and said "sorry > it doesn't do that?" I don't think so. > The floating-point comparisons in the SELECTs from my original post may be a > bad idea for some values because of approximation issues, true. But, they > are still perfectly valid, well-formed SQL statements. If they fail to find > any records because of approximate representation in floating-point numbers, > so be it -- return 0 records and let me figure out why. I have to do that > on all searches, let the user know what happened, take appropriate recovery > actions etc. Normal stuff for a database app. > Raising an error, on the other hand, is entirely inappropriate. There is no > error in the SQL statement itself. I have to structure my code differently > and write a workaround each time this situation comes up. And, this same > problem does not occur in SQL Server, MS Access 97 & 2000, and probably lots > of other database platforms that implement SQL SELECT statements. They just > convert the search value to a float and do the search and return the > records, exactly as you'd expect. > So I say it's a bug. Bug bug bug bug bug bug bug. So there, Microsoft! > You have a bug! > Does Microsoft care? Does my considered opinion wound them deeply? Well, > no. They don't give a hoot. Am I bitter? Oh no, not me... > -- > Terry > Terry McKiernan > pinpoint tools > www.pinpointtools.com > We Make ACT! Work Better! > > That's assuming this is a bug, which I'm not convinced it is. > > When using floating point variables (which is the case here) equality and > > inequality operators should never be used, because of the simple nature of > > floating point numbers. As I was telling Terry offline, setting the field > > to a value of "1" does not mean that it will inherently be exactly 1. It > > will very likely be near the value 1, but will probably differ somewhere > > out a dozen or so decimal places. > > If you need to do comparisons against integers, use an integer field or > use > > range comparisons. > > -- > > Chris Tacke, eMVP > > Windows CE Product Manager > > Applied Data Systems > > www.applieddata.net > > > > Basically, this is a known bug but it is very poorly documented, > hidden > > in > > > a > > > > note about the SELECT statement in MS's docs on ADOCE. The = and <> > > > > operators just don't work with floating-point values. > > > All I'm wondering is: Will there ever be a fix for all the known bugs? > > > Probably no, as MS wants us all to move to the .NET platform. But then > > what > > > about the bugs on that platform? Do we dare to use it as MS platforms > seem > > > to be abandoned almost as fast as they get designed? > > > Just wondering. > > > Keld Laursen
Sun, 13 Mar 2005 00:20:50 GMT

Page 1 of 1

[ 6 post ]