SQL detection
 

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

SQL detection
 

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" wrote
in message ...

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

SQL detection
 

:confused: 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

SQL detection
 

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" wrote
in message ...

:confused: 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