SQL detection

[brucelim80]

Hi
Can anyone help me with this problem? Thank

I have this Source excel with the data shown below;

Datafile
001
SVX
002
SFDAS
ERA123

I have written an interactive form in EXCEL VBA to let user to enter the figure to search for based on the datafile.
When i use adodb connection and execute a statement to extract a figure, it does not display the result as it only detect numeric figure.

How do i resolve this problem ? i need the whole row to be string

Can anyone help me ?

Thank

[NickHK]

Bruce,
Correct me if I misunderstand you:
Your source DATAFILE is a txt or csv file, not Excel.
There is a single column of data in this source file.
You are using ADO to query this datafile.
The first entry in that column of the data file is numeric

What is the connection ?
What is the SQL you are using ?

NickHK

"brucelim80" <[email protected]> wrote
in message news:[email protected]...
>
> Hi
> Can anyone help me with this problem? Thank
>
> I have this Source excel with the data shown below;
>
> DATAFILE
> 001
> SVX
> 002
> SFDAS
> ERA123
>
> I have written an interactive form in EXCEL VBA to let user to enter
> the figure to search for based on the datafile.
> When i use adodb connection and execute a statement to extract a
> figure, it does not display the result as it only detect numeric
> figure.
>
> How do i resolve this problem ? i need the whole row to be string
>
> Can anyone help me ?
>
> Thank
>
>
> --
> brucelim80
> ------------------------------------------------------------------------
> brucelim80's Profile:
http://www.excelforum.com/member.php...o&userid=32244
> View this thread: http://www.excelforum.com/showthread...hreadid=520923
>

[brucelim80]

Hi nick,
Thank you for your reply. I am using the following code as stated below;

Yes, The first row of the data is number. How do i make it to convert everything to String so that the SQL extract both datatype.


Connection

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source= C:\data\CustPv4ConcRiskCal.xls ;" & _
"Extended Properties=Excel 8.0;"
.Open
End With


Source
My source file is also another excel file

When i perform a microsoft Query, it also extract only the numeric figure from the column, it does not capture the String.

Example

My datasource look like this format in Excel;


Datafield1 Datafield2
AVd London
GIA New York
223 Hong kong
2223 singapore


So I wrote this SQL to extract as follow

Select * FROM `CustPv4ConcRiskCal`.datafield1 Where Datafield1 = AVd


It will not show the result.

However if i extract based on numeric

Select * FROM `CustPv4ConcRiskCal`.datafield1 Where Datafield1 = 223


It will show the result. I observe that if there is a mixture of datatype in a particular column, it will not work.


Can you help me with this?

Thank you

[NickHK]

Bruce,
First, there no need to shout, using CAPITAL letter.
Text values should be quoted; Where Datafield1 = "AVD".

Also, the source file should be closed when you query.
http://support.microsoft.com/default...;en-us;Q319998

NickHK


"brucelim80" <[email protected]> wrote
in message news:[email protected]...
>
> Hi nick,
> Thank you for your reply. I am using the following code as stated
> below;
>
>
>
> CONNECTION
>
> Dim cn As ADODB.Connection
> Set cn = New ADODB.Connection
> With cn
> Provider = "Microsoft.Jet.OLEDB.4.0"
> ConnectionString = "Data Source=
> C:\data\CustPv4ConcRiskCal.xls ;" & _
> "Extended Properties=Excel 8.0;"
> Open
> End With
>
>
> SOURCE[/B]
> MY SOURCE FILE IS ALSO ANOTHER EXCEL FILE
>
> WHEN I PERFORM A MICROSOFT QUERY, IT ALSO EXTRACT ONLY THE NUMERIC
> FIGURE FROM THE COLUMN, IT DOES NOT CAPTURE THE STRING.
>
> EXAMPLE
>
> MY DATASOURCE LOOK LIKE THIS FORMAT IN EXCEL;
>
>
> DATAFIELD1 DATAFIELD2
> AVD LONDON
> GIA NEW YORK
> 223 HONG KONG
> 2223 SINGAPORE
>
>
> SO I WROTE THIS SQL TO EXTRACT AS FOLLOW
>
> SELECT * FROM `CUSTPV4CONCRISKCAL`.DATAFIELD1 WHERE DATAFIELD1 = AVD
>
>
> IT WILL NOT SHOW THE RESULT.
>
> [B]HOWEVER IF I EXTRACT BASED ON NUMERIC
>
> Select * FROM `CustPv4ConcRiskCal`.datafield1 Where Datafield1 = 223
>
>
> It will show the result. I observe that if there is a mixture of
> datatype in a particular column, it will not work.
>
>
> Can you help me with this?
>
> Thank you
>
>
> --
> brucelim80
> ------------------------------------------------------------------------
> brucelim80's Profile:
http://www.excelforum.com/member.php...o&userid=32244
> View this thread: http://www.excelforum.com/showthread...hreadid=520923
>