Excel SQL retrieval
Posted by Tomc on April 03, 2001 9:34 AM
In Excel Data retrieval from MSAccess, how do you
use a fixed location in your SQL query statement
in your vis macro.
"WHERE (T1.Expr1 Like '%Ford%')" Works fine, but it
is manual.. Need spreadsheet location to work
ie Like 'Sheet1!A5'.....This method does not work??
nor select,range
Any clues..thx
Posted by Mark W. on April 03, 2001 9:45 AM
Enter "Ford" into cell A5 and use:
="WHERE (T1.Expr Like '%"&A5&"%')"
Posted by tomc on April 03, 2001 10:38 AM
Thank you very much.
I think I have some syntax work to do now. (syntax error). I have direction now..Thx...
ORIGINAL
.Sql = Array( _
"SELECT T1.Expr1, T1.list1, T1.list2, T1.list3, T1.`Sum Of NET1`" & Chr(13) & "" & Chr(10) & "FROM `c:\acc_samps\test2`.T1 T1" & Chr(13) & "" & Chr(10) & "WHERE (T1.Expr1 Like '%FORD%')" _
)
----------------------
REVISED
.Sql = Array( _
"SELECT T1.Expr1, T1.list1, T1.list2, T1.list3, T1.`Sum Of NET1`" & Chr(13) & "" & Chr(10) & "FROM `c:\acc_samps\test2`.T1 T1" & Chr(13) & "" & Chr(10) & "WHERE (T1.Expr1 Like '%"&A5&"%')" _
)
-----------------------
Posted by Mark W. on April 03, 2001 10:47 AM
I trust...
...that you know that you could do all this with
Excel's Data Get External Data menu command.
Have you used it?
Posted by tomc on April 03, 2001 11:26 AM
Re: reply...
Thanks Again.
I used that originally...started Macro-record
-ran 'get external data'
-selected Query1 from Accessfile
-selected like 'gm' ...
-returned data to excel
The external data Menu will allow not you to point to any item while it is running (Ie a cell on a worksheet)..you must type it in or select.
This is meant to actually refer to a reference
cell in excel that the sales rep will select from a drop down validate, and then it will pull in all his sales based on his selection of a group in excel. My retrieval worked, but not looking
at his/her selection.. I had to manually change the vb app code for each customer group. Since we
have reps all around the world, we wished to
use Excel and not msaccess since their laptops
all have excel.
Do you work with the MrExcel firm, or are you
a helpful viewer?
Thx again
Posted by Mark W. on April 03, 2001 11:41 AM
Re: reply...
> The external data Menu will allow not you to point to any item while it is running (Ie a cell on a worksheet)..you must type it in or select.
Yes, it will... read the Excel Help topic for
"parameter queries". And see the Data Get
External Data Parameters... command.
