RichNH
asked on
Parameter in Excel for ODBC connection?
I have an Excel 2007 workbook that has a worksheet connected to an MS SQL Express DB. I have an ODBC connection set up on my PC which I use to access this DB. I've attached a screen shot of the Connection Properties Definition. You'll see that the second to last line in the command string references a date. I would like to have it instead reference a parameter to be input by the user. I tried replacing the date with a question mark but Excel comes back with an error saying that I have an Invalid Parameter Number first and then an Invalid Descriptor Index after that. When I tried to edit the query (with the embedded date) Excel told me "This query can not be editted by the query wizard." Apparently the query cannot be graphically portrayed by MS Query and from what I can determine if MS Query cannot graphically represent a query then you cannot use the question mark to represent a parameter in that query. This is all I have found out.
So, is there anyone who can tell me how I can get this connection string in the connection properties to have an input parameter for the date that is in the query?
Thanks, Rich
PS Here's the text of the query:
SELECT "inventory_transactions"." transactio n_id", "users"."user_logon", "item"."item_number", "transaction_types"."trans _descripti on", "inventory_transactions"." trans_date ", "inventory_transactions"." entry_date ", "inventory_transactions"." quantity", "inventory_transactions"." lot", "sites"."site_name", "location"."description", "inventory_transactions"." pallet", "notes"."table_name", "notes"."note_text"
FROM ((((("WaspTrackInventory". "dbo"."inv entory_tra nsactions" "inventory_transactions" INNER JOIN "WaspTrackInventory"."dbo" ."location " "location" ON "inventory_transactions"." location_i d"="locati on"."locat ion_id") INNER JOIN "WaspTrackInventory"."dbo" ."transact ion_types" "transaction_types" ON "inventory_transactions"." trans_type "="transac tion_types "."trans_t ype_no") INNER JOIN "WaspTrackInventory"."dbo" ."users" "users" ON "inventory_transactions"." user_id"=" users"."us er_id") INNER JOIN "WaspTrackInventory"."dbo" ."item" "item" ON "inventory_transactions"." item_id"=" item"."ite m_id") LEFT OUTER JOIN "WaspTrackInventory"."dbo" ."notes" "notes" ON "inventory_transactions"." transactio n_id"="not es"."note_ id") INNER JOIN "WaspTrackInventory"."dbo" ."sites" "sites" ON "location"."site_id"="site s"."site_i d"
WHERE ("transaction_types"."tran s_descript ion"='Add' OR "transaction_types"."trans _descripti on" = 'Remove') AND "inventory_transactions"." entry_date ">'8/1/201 0'
ORDER BY "inventory_transactions"." entry_date ", "item"."item_number"
So, is there anyone who can tell me how I can get this connection string in the connection properties to have an input parameter for the date that is in the query?
Thanks, Rich
PS Here's the text of the query:
SELECT "inventory_transactions"."
FROM ((((("WaspTrackInventory".
WHERE ("transaction_types"."tran
ORDER BY "inventory_transactions"."
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
O bugger. I would set up an Analysis Services cube then (not sure if it's supported with express though) and pull all their data in on a schedule. From there you can do with it whatever you like.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
convert date format in excel to numeric (Format cells -> number) then sort cells ASC, save and read
Steve - I think you may have accidently posted in the wrong thread.
broomee9,
The solution I posted is correct and allows for the parameter but if the asker is not familier with VBA then it will not be a solution for him specifically. I am OK with whatever you decide on how to close this one.
Thanks,
Jerry
The solution I posted is correct and allows for the parameter but if the asker is not familier with VBA then it will not be a solution for him specifically. I am OK with whatever you decide on how to close this one.
Thanks,
Jerry
ASKER
My appologies, I was kidnapped at work and my life is gone now. I do not have the time right now (nor the permission) to try both solutions but I do understand the necessity to close the question. Both responses are excellent leads. I started to pursue the first but was then kidnapped and haven't been able to get back to the original task. I definitely would like to try the second too.
Do either of the experts mind if I split the points as both solutions appear to be good ones?
Rich
Do either of the experts mind if I split the points as both solutions appear to be good ones?
Rich
Thanks Rich - No problem from my end - I am OK with whatever you would like to do.
- Jerry
- Jerry
ASKER
Rich