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 ...

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