Queries / Excel
 

You folks always have lots of good insights - thanks very much for your
past responses... hopefully you'll be able to point me in a good
direction for this dillemma.

I do a lot of work between Excel and a Sybase database. One of the
sheets I put together queries our database for a list of items where the
shipped quantity is less than the requested quantity. I'd like to limit
the date range from which it pulls - which is possible if I go into the
data menu and physically edit the query every time. What I'd like to do
is to make it easier - so that I don't have to modify it all the time
for the people who use it.

Is there a way for me to place a text field on the spreadsheet and a
query button, so that I can have the user enter the date into the text
box, click the query button, and have Execel go out to the database,
query it based on the original string, which looks like this:

--------------

SELECT v_TransferWorksheetData.WRK_Name,
v_TransferWorksheetData.ITW_RequestDate,
v_TransferWorksheetData.ITW_SupplyDate,
v_TransferWorksheetData.WRK_TimestampCommitted,
v_TransferWorksheetData.ITD_RequestQuantity,
v_TransferWorksheetData.ITD_SupplyQuantity,
v_InventoryMaster.INV_ScanCode, v_InventoryMaster.INV_ReceiptAlias
FROM ecrs.v_InventoryMaster v_InventoryMaster,
ecrs.v_TransferWorksheetData v_TransferWorksheetData
WHERE v_TransferWorksheetData.ITD_INV_FK = v_InventoryMaster.INV_PK AND
((v_TransferWorksheetData.WRK_Name Like '%HQ%') AND
(v_TransferWorksheetData.WRK_Status Like '%Shipped%') AND
(v_TransferWorksheetData.ITD_SupplyQuantity=0) AND
(v_InventoryMaster.STO_Name Like '%Headquarters%') AND
(v_InventoryMaster.PI2_Description Like 'WH%') AND
(v_TransferWorksheetData.WRK_TimestampCommitted{t s '2005-06-01
00:00:00'}) OR (v_TransferWorksheetData.WRK_Name Like '%HQ%') AND
(v_TransferWorksheetData.WRK_Status Like '%Committed%') AND
(v_TransferWorksheetData.ITD_SupplyQuantity=0) AND
(v_InventoryMaster.STO_Name Like '%Headquarters%') AND
(v_InventoryMaster.PI2_Description Like 'WH%') AND
(v_TransferWorksheetData.WRK_TimestampCommitted{t s '2005-06-01
00:00:00'}))
ORDER BY v_TransferWorksheetData.WRK_Name

----------------

You'll note the date / time stamp at the very end here... that's what I
need to modify. I already have the sheet set up to use filters, so the
user can (if they're savvy enough) just filter for anything past a
certain date... but not all of my office users are even that savvy.

Sorry if the sql is a little messy - the msquery tool tends to create
some redundant code.

If anyone has some suggestions as to texts or sites that provide good
in-depth Excel / SQL integration tips, please feel free to pass them on.
I'm always looking for new things.

Ben

Queries / Excel
 

Nick Hodge has an example of setting up a parameter query using a query
tables. You don't say what mechanism you are using, but this may be useful.
There should be an option to get the value from the parameter from a
worksheet cell.

http://nickhodge.co.uk/gui/datamenu/...taexamples.htm

--
Regards,
Tom Ogilvy

"Ben Adler" wrote in message
om...
You folks always have lots of good insights - thanks very much for your
past responses... hopefully you'll be able to point me in a good
direction for this dillemma.

I do a lot of work between Excel and a Sybase database. One of the
sheets I put together queries our database for a list of items where the
shipped quantity is less than the requested quantity. I'd like to limit
the date range from which it pulls - which is possible if I go into the
data menu and physically edit the query every time. What I'd like to do
is to make it easier - so that I don't have to modify it all the time
for the people who use it.

Is there a way for me to place a text field on the spreadsheet and a
query button, so that I can have the user enter the date into the text
box, click the query button, and have Execel go out to the database,
query it based on the original string, which looks like this:

--------------

SELECT v_TransferWorksheetData.WRK_Name,
v_TransferWorksheetData.ITW_RequestDate,
v_TransferWorksheetData.ITW_SupplyDate,
v_TransferWorksheetData.WRK_TimestampCommitted,
v_TransferWorksheetData.ITD_RequestQuantity,
v_TransferWorksheetData.ITD_SupplyQuantity,
v_InventoryMaster.INV_ScanCode, v_InventoryMaster.INV_ReceiptAlias
FROM ecrs.v_InventoryMaster v_InventoryMaster,
ecrs.v_TransferWorksheetData v_TransferWorksheetData
WHERE v_TransferWorksheetData.ITD_INV_FK = v_InventoryMaster.INV_PK AND
((v_TransferWorksheetData.WRK_Name Like '%HQ%') AND
(v_TransferWorksheetData.WRK_Status Like '%Shipped%') AND
(v_TransferWorksheetData.ITD_SupplyQuantity=0) AND
(v_InventoryMaster.STO_Name Like '%Headquarters%') AND
(v_InventoryMaster.PI2_Description Like 'WH%') AND
(v_TransferWorksheetData.WRK_TimestampCommitted{t s '2005-06-01
00:00:00'}) OR (v_TransferWorksheetData.WRK_Name Like '%HQ%') AND
(v_TransferWorksheetData.WRK_Status Like '%Committed%') AND
(v_TransferWorksheetData.ITD_SupplyQuantity=0) AND
(v_InventoryMaster.STO_Name Like '%Headquarters%') AND
(v_InventoryMaster.PI2_Description Like 'WH%') AND
(v_TransferWorksheetData.WRK_TimestampCommitted{t s '2005-06-01
00:00:00'}))
ORDER BY v_TransferWorksheetData.WRK_Name

----------------

You'll note the date / time stamp at the very end here... that's what I
need to modify. I already have the sheet set up to use filters, so the
user can (if they're savvy enough) just filter for anything past a
certain date... but not all of my office users are even that savvy.

Sorry if the sql is a little messy - the msquery tool tends to create
some redundant code.

If anyone has some suggestions as to texts or sites that provide good
in-depth Excel / SQL integration tips, please feel free to pass them on.
I'm always looking for new things.

Ben