SQL query returning null values for few columns

[sneha2409]

Hi,

I am trying to use an excel worksheet as database.
The query to fetch records is working fine except for the fact that it is returning null values for few columns where data is present

The query I am using is
With cn
.Provider = "Microsoft.Jet.OLEDB.12.0"
.ConnectionString = "Data Source=N:\sneha\trial\BMT_3.xls;" & _
"Extended Properties=Excel 8.0;"
.Open
End With
Set rs = cn.Execute("SELECT DISTINCT * FROM [NLX_FIN$A:BU]WHERE FIN= '" & bpidPMR & "' and F4='" & accTypePMR & "' and F8='" & instIDPMR & "' ")
Do Until rs.EOF

[techgnome]

yeah... that happens sometimes when the data changes causing a confusion of datatypes... Excel data isn't necessarily typed... so it has to infer the data type when you select it... I forget if it uses just the first row, or the first couple of rows... but the point is that the first couple lines of data ... so if it sees numbers in a column, it assumes it's a numeric column and will process it as such. If it encounters non-numeric data, it can't change the datatype in the datatable (it's too late) ... so it simply assigns NULL to the non-numeric cells.

-tg

[sneha2409]

Thanks tg...I have already set the format of all cells as general.
So datatype should be an issue i guess

[techgnome]

that jsut controls the format anyways... it doesn't affect the datatype... even if you format as a number of some kind... because in theory you can still enter "ABC" if you wanted to.

-tg

[sneha2409]

yes.
got the problem... there was a row of merged cells above it..probably that was the reason of the problem

thanks