I have literally hundreds of queries going against a back-end sql server database. We are in the process of upgrading to the new version of this database (a third party tool) and I'm testing the queries to make sure they'll work with the new version of the database. There are several fields in one particular table that are supposed to represent quantities. Historically Access has recognized these as numeric fields but now with the new version of the DB Access thinks they're text fields. I've confirmed with both the tool manufacturer and our sql server dba that these fields are in fact numeric. Has anyone out there run into this situation? If so, any guidance you can give would be greatly appreciated. Needless to say, I'm not at all enthusiastic of going through all these queries and converting the data to numeric... Thanks, mpj -- Michal Joyce Project Management IS Analyst Aflac - Project Management Office
Do you mean numeric as in decimal, or numeric as in any kind of number? Is the data stored in Jet? Are the queries Access queries against Jet data? I'm not sure how Access "thinks" they're character fieldsif in fact they're stored in some numeric format in Jet. Can you be more specific? --Mary On Thu, 23 Mar 2006 09:13:29 -0800, MJatAflac wrote: > >I have literally hundreds of queries going against a back-end sql server >database. We are in the process of upgrading to the new version of this >database (a third party tool) and I'm testing the queries to make sure >they'll work with the new version of the database. There are several fields >in one particular table that are supposed to represent quantities. >Historically Access has recognized these as numeric fields but now with the >new version of the DB Access thinks they're text fields. I've confirmed with >both the tool manufacturer and our sql server dba that these fields are in >fact numeric. Has anyone out there run into this situation? If so, any >guidance you can give would be greatly appreciated. Needless to say, I'm not >at all enthusiastic of going through all these queries and converting the >data to numeric... > >Thanks, > >mpj
These are fields from SQL Server tables that I am linking into an Access DB via ODBC. They are stored as integers in the SQL Server tables but when I look at them in design view within my Access DB they are described as text fields. If I try to do mathematical comparisons and operations on these fields I get data type mismatch errors. Hope this clarifies... m -- Michal Joyce Project Management IS Analyst Aflac - Project Management Office "Mary Chipman [MSFT]" wrote: > Do you mean numeric as in decimal, or numeric as in any kind of > number? Is the data stored in Jet? Are the queries Access queries > against Jet data? I'm not sure how Access "thinks" they're character > fieldsif in fact they're stored in some numeric format in Jet. Can you > be more specific? > > --Mary > > On Thu, 23 Mar 2006 09:13:29 -0800, MJatAflac > wrote: > > > > >I have literally hundreds of queries going against a back-end sql server > >database. We are in the process of upgrading to the new version of this > >database (a third party tool) and I'm testing the queries to make sure > >they'll work with the new version of the database. There are several fields > >in one particular table that are supposed to represent quantities. > >Historically Access has recognized these as numeric fields but now with the > >new version of the DB Access thinks they're text fields. I've confirmed with > >both the tool manufacturer and our sql server dba that these fields are in > >fact numeric. Has anyone out there run into this situation? If so, any > >guidance you can give would be greatly appreciated. Needless to say, I'm not > >at all enthusiastic of going through all these queries and converting the > >data to numeric... > > > >Thanks, > > > >mpj >
MJatAflac wrote: > These are fields from SQL Server tables that I am linking into an > Access DB via ODBC. They are stored as integers in the SQL Server > tables but when I look at them in design view within my Access DB > they are described as text fields. If I try to do mathematical > comparisons and operations on these fields I get data type mismatch > errors. > > Hope this clarifies... Are they "Integers" in SQL Server or "BigIntegers". Access has no numeric type that corresponds to BigInteger so the values are mapped as Text. Actual Integers though shoudl be mapped to LongInteger in Access and work just fine. If your type is actually Integer then I would see if you are running the latest version of the ODBC driver and have all patches applied. -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com
They are integers but one important thing I failed to mention as I often do is that this is an Access 97 issue. When I look at the data using Access 2003 the data is numeric. As I understand it both versions of Access would use the same ODBC driver since that is determined when I set up the ODBC data source. Is that correct? -- Michal Joyce Project Management IS Analyst Aflac - Project Management Office "Rick Brandt" wrote: > MJatAflac wrote: > > These are fields from SQL Server tables that I am linking into an > > Access DB via ODBC. They are stored as integers in the SQL Server > > tables but when I look at them in design view within my Access DB > > they are described as text fields. If I try to do mathematical > > comparisons and operations on these fields I get data type mismatch > > errors. > > > > Hope this clarifies... > > Are they "Integers" in SQL Server or "BigIntegers". Access has no numeric > type that corresponds to BigInteger so the values are mapped as Text. > Actual Integers though shoudl be mapped to LongInteger in Access and work > just fine. > > If your type is actually Integer then I would see if you are running the > latest version of the ODBC driver and have all patches applied. > > > -- > I don't check the Email account attached > to this message. Send instead to... > RBrandt at Hunter dot com > > >
MJatAflac wrote: > They are integers but one important thing I failed to mention as I > often do is that this is an Access 97 issue. When I look at the data > using Access 2003 the data is numeric. > > As I understand it both versions of Access would use the same ODBC > driver since that is determined when I set up the ODBC data source. > Is that correct? Jet 3.51 (Access97) does not support the Decimal DataType whereas Jet 4.0 (Access2003) does, but there is no difference between them concerning Integers/LongIntegers. -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com
According to my SQL Server DBA the field is defined in SQL Server as Numeric 17,4. He did a describe on the table and it shows up as int 17,4. I guess the long and short of it is that we need to convert to Access 2003. Thanks for all your guidance and help on this. You guys are life-savers even when you are only telling me what I already suspected. m -- Michal Joyce Project Management IS Analyst Aflac - Project Management Office "Rick Brandt" wrote: > MJatAflac wrote: > > They are integers but one important thing I failed to mention as I > > often do is that this is an Access 97 issue. When I look at the data > > using Access 2003 the data is numeric. > > > > As I understand it both versions of Access would use the same ODBC > > driver since that is determined when I set up the ODBC data source. > > Is that correct? > > Jet 3.51 (Access97) does not support the Decimal DataType whereas Jet 4.0 > (Access2003) does, but there is no difference between them concerning > Integers/LongIntegers. > > > -- > I don't check the Email account attached > to this message. Send instead to... > RBrandt at Hunter dot com > > >
MJatAflac wrote: > According to my SQL Server DBA the field is defined in SQL Server as > Numeric 17,4. He did a describe on the table and it shows up as int > 17,4. I guess the long and short of it is that we need to convert to > Access 2003. Okay well that's not an Integer then is it ;-) Decimal and Numeric in Access 97 will be treated as Singles or Doubles unless they are too long (17 is too long) in which case they are shown as text. This would no doubt be mapped to the new DECIMAL type in the newer versions. > Thanks for all your guidance and help on this. You guys are > life-savers even when you are only telling me what I already > suspected. -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com
