Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL detection
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Color detection | Excel Worksheet Functions | |||
Hidden Row Detection | Excel Programming | |||
add-in detection | Excel Programming | |||
on press detection | Excel Programming | |||
Detection of VBA code | Excel Programming |