Please help,

I frequently import data from databases contained in
Oracle using SQL. Once i get the data into the
spreadsheet and try to do VLOOKUPs or even simply 'FIND' a
number or text string my query has returned, i get an
error message. I have two solutions for this.

1. If the returned column is made up of only numbers (not
text), I can multiply the contents by 1, then do a VLOOKUP
or FIND on the new column. The numbers are the same, but
Excel only recognizes them after I have multiplied by one.

2. If there is text contained in the field returned, I
cannot use the above trick. I have to save as a ".csv"
file, then copy/paste special (values only) back into the
sheet I want to perform the VLOOKUP in. This appears to
change the format of all fields to 'text' and not 'number'
so a VLOOKUP referencing a typed-in number doesn't work.
I can VLOOKUP on the text, but not on columns that are
strictly numbers.


Is this some kind of weird formating issue? I have tried
formating the cells, but that doesn't work. The problem
seems deeper than that. Does anyone know a solution other
than the time consuming way I am doing it now??

Thanks for any help.

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

-----Original Message-----
Please help,

I frequently import data from databases contained in
Oracle using SQL. Once i get the data into the
spreadsheet and try to do VLOOKUPs or even simply 'FIND'
a
number or text string my query has returned, i get an
error message. I have two solutions for this.

1. If the returned column is made up of only numbers
(not
text), I can multiply the contents by 1, then do a
VLOOKUP
or FIND on the new column. The numbers are the same,
but
Excel only recognizes them after I have multiplied by
one.

2. If there is text contained in the field returned, I
cannot use the above trick. I have to save as a ".csv"
file, then copy/paste special (values only) back into
the
sheet I want to perform the VLOOKUP in. This appears to
change the format of all fields to 'text' and
not 'number'
so a VLOOKUP referencing a typed-in number doesn't
work.
I can VLOOKUP on the text, but not on columns that are
strictly numbers.


Is this some kind of weird formating issue? I have
tried
formating the cells, but that doesn't work. The problem
seems deeper than that. Does anyone know a solution
other
than the time consuming way I am doing it now??

Thanks for any help.
.

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!

Syntax

VLOOKUP(lookup_value,table_array,col_index_num,ran ge_lookup)

Have you tried setting range_lookup to FALSE ?

jjanzen wrote:
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!

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/de...dprofilter.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'

--

JWolf wrote in message ...
Syntax

VLOOKUP(lookup_value,table_array,col_index_num,ran ge_lookup)

Have you tried setting range_lookup to FALSE ?

jjanzen wrote:
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!