I am collecting data from a number of sources, including an Access database, and merging the data into one dataset that I am then storing in SQL Server using an ADO.NET DataSet object.
I am using an Access View to build a DataSet and then merging it into the "main" dataset. The problem I am having is with one of the data types. My dataset has a string column but the Access table has it as numeric. So the merge process does not work.
I can't seem to find much information on the Access query language, but it does not seem to support a way to convert the data type within the view or in any query.
Is there a way to convert an Access column to another type in a query or view
Thanks!
Getting data from Access database and copying to SQL Server dataset - Data Type problem
Anonymo3qetwf
After *way* too much searching, I finally found my answer.
Format(myfield,'#')
will ensure that the numbers are numbers and not converted to scientific notation (1.23456789012345E15)
(Why isn't Access documented better somewhere )
MikG
Here is some information on the Access (Jet) query language:
http://office.microsoft.com/en-us/assistance/CH062526881033.aspx
Hope this helps,
Steve
bosstan1
That documentation was *very* basic and did not provide any useful information about any key words like Cast or CType.
As I was doing more research, however, I did stumble upon CStr. However, it is having difficulties with large numbers. For example, I have two primary keys:
1234567890123456 and 1234567890123457. Both of these convert to
1.23456789012345E15 so I am now getting a duplicate key error. Any idea where I can get more "advanced" query info that may provide details on formatting such that converting this to a string that won't create duplicate keys
Thanks!