Excel cannot 'FIND' data imported from oracle

R

Raul

You can try modifying the query you're using to get the
data out of Oracle to cast the data as interger, real,
string, etc.

for example:
SELECT CAST(ordernumber as integer) newordernum
FROM ......
WHERE......

Are you using ADO to get the data and place it directly
into Excel, or are you using Oracle SQLplus to get the
data and then pasting the data into Excel?

If you're using ADO and you're putting the data into an
array, you can use the Dim statement to set the datatype
to string or double, etc.

Hope this helps,
Raul
 
J

jjanzen

Raul,

Thanks. That solved the problem where I lookup numbers which are
integers. However, I also need to use VLOOKUP on a column of data which
returns integers as well as text/number combinations. For example 62202
is one invoice number, while another is EN030544.

Is it possible to have one column returned as integers and the other as
text? I tried doing this with no success. I am an SQL rookie so maybe
I am doing something wrong. If I can return the 'combination' column as
text, I should be able to get the VLOOKUP working.

NOTE: I am using linked tables if that makes a difference.



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
 
J

JWolf

Syntax

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Have you tried setting range_lookup to FALSE ?
 
O

onedaywhen

If you *are* using ADO, you could use a disconnected recordset (i.e.
client-side cursor) and a cursor type which supports bookmarks (e.g.
static cursor) and retain the recordset object by keeping it in scope
somehow (e.g. module-level variable). When you wanted to find a row
based on a column value, you could go back to your recordset and apply
a filter using its Filter property e.g.

With MyRecordset
.Filter = "invoice_nbr = 'EN030544'"
If .RecordCount > 0 Then
' Do something with filtered rows
End If
End With

Look up Filter in the help:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdprofilter.asp

Otherwise, you may find it a better approach to hit the database each
time, rather than filter the results in the Excel GUI e.g.

SELECT ...
FROM ...
WHERE invoice_nbr = 'EN030544'

--
 
Top