Create query table in excel

[ktb916]

I want to create a query table in excel from an SQL statement based on an access table. I have a combo box setup on a worksheet for the user to select criteria (customer name) that the SQL statement will use to filter the data. Each time the user changes the selection in the combo box the query runs and new information is displayed. I'm having difficulty connecting with the database to extract the information. I need to see the correct steps and syntax for establishing the connection and executing the SQL statement.

Thanks,

KT

 

[PHV]

Have you tried the macrorecorder ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SkipVought]

KT,

If you are in Excel, it is simple.

1. Data/Get External data/New Database Query - Access files -- your database -- your specific query , next, next, next, select the Edit Query Option, Finish

2. Add a criteria and instead of a value, enter [What Value]

3. File/Return Data to Excel

4. Select Parameters... - select the third option to Get data from the following cell -- select the cell and check the Refersh automatically when cell value changes



Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[ktb916]

Is it possible in the parameters to have it look at a combo box rather than a cell? There are a couple hunderd possible customer names and spelling is obviously crucial.

Thanks for the help.

KT

 

[SkipVought]

You are stuck with a cell.

Just assign your ComboBox selection to a cell.

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[ktb916]

That should do it.

Great tip!

Thanks a bunch!

 

[ktb916]

I have another question based on the data i have now been able to import to excel - thanks to your help - my V LookUp function is giving me strange information. I'm not completely familar with the function so maybe i'm just doing something wrong.

I want to look up a value in the imported data based on a value i entered in a cell. Easy enough - but when i get to the last argument of the V LookUp wizard it allows for a True or False entry? Is this correct? I enter false so the function will only return an exact match and that's when i end up with N/A# returned.

What am i doing wrong?

 

[SkipVought]

did you look at the HELP on VLOOKUP?

What is the lookup value?

Is there a corresponding value in the resultset in the first column?

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[ktb916]

Actually i just figured it out - the value i was looking for was padded with several trailing blank spaces so it wasn't an exact match hence the N/A#

Thanks,

KT