Scientific Notation(text) and Rounding

This isn't working either...

Please Login or Register  to view this content.

Well, SQL has made my day a POS. With periods converting to # values. With reading in a workbook as a table just to find out it isn't progressing past 255 fields. To Scientific Notation and unable to convert it to the value it shows me in the formula bar.

How do I set the SQL string to the data type I want for each column of data?

Why is there a 255 field limit?

Am I not setting something up right with my connection string?

Well appearances are deceptive. I can't change the format as I am using ADODB to connect to some spreadsheets (in the example for this post I connect to the same spreadsheet). I can't enter the cell and have Excel recalculate the entered value (as the value was entered then the format set to text). The real resolution at the base of the problem is to tell the team that hands me data to stop entering values then changing format to "Text" but to set the format first then enter data. My results after the value actually being "Text":

Rounded CDec: 349812410298421
CStr: 349812410298421
Just RS Value: 349812410298421
Format to Text: 349812410298421
String Variable Conversion: 349812410298421
CDbl: 349812410298421

But that required me to enter the cell and press enter (something that I can't do with ADODB/SQL- that I know of). It looks like I am simply stuck with:

Rounded CDec: 349812410300000
CStr: 3.498124103e+014
Just RS Value: 3.498124103e+014
Format to Text: 349812410300000
String Variable Conversion: 349812410300000
CDbl: 349812410300000

all due to the fact values are entered then the range (where the data in entered) is then set to "Text"- I really just wish Microsoft would fix this as "349812410298421" is the value in the formula bar yet when set to text format it displays as "3.49812E+14" until the user enters the cell and presses enter key, thus resulting in "349812410298421" as text.

I was looking for something that I could change in my Database connection string or my SQL recordset connection. Like if there is a way to set data type (like I tried in my second post- but this failed as well) before opening the recordset.