Excel Postgresql Database Connection Use Cell as Date

Question

I am trying to write the SQL command for a database that uses a cell value as a date parameter. Here is an example query without the cell value:

SELECT usda_cattle_auctions.weight_average, usda_cattle_auctions.average_price
FROM public.usda_cattle_auctions usda_cattle_auctions
WHERE (usda_cattle_auctions.report_date>={d '2012-03-24'} And usda_cattle_auctions.report_date<={d '2014-10-25'}) AND (usda_cattle_auctions.state='CO') AND (usda_cattle_auctions.gender='Steers') AND (usda_cattle_auctions.class_type='Feeder')
ORDER BY usda_cattle_auctions.report_date DESC

I read this post: Excel: Use a cell value as a parameter for a SQL query

However when I adjusted one parameter like this:

SELECT usda_cattle_auctions.weight_average, usda_cattle_auctions.average_price
FROM public.usda_cattle_auctions usda_cattle_auctions
WHERE (usda_cattle_auctions.report_date>={d [MarketValCal$d3]} And usda_cattle_auctions.report_date<={d '2014-10-25'}) AND (usda_cattle_auctions.state='CO') AND (usda_cattle_auctions.gender='Steers') AND (usda_cattle_auctions.class_type='Feeder')
ORDER BY usda_cattle_auctions.report_date DESC

The query will no longer work because I get an invalid syntax warning. How should I properly structure this query? Any help is greatly appreciated.

EDIT:

I took out the quotes and tried to do this with a none date parameter and now I get the message "the # of binded parameters < the # of parameter markers"

score 0 · Accepted Answer · answered Aug 20 '15 at 15:22

I found the best solution to my problem. You can modify use a query like so:

SELECT usda_cattle_auctions.weight_average, usda_cattle_auctions.average_price
FROM public.usda_cattle_auctions usda_cattle_auctions
WHERE (usda_cattle_auctions.report_date>= ? And usda_cattle_auctions.report_date<={d '2014-10-25'}) AND (usda_cattle_auctions.state='CO') AND (usda_cattle_auctions.gender='Steers') AND (usda_cattle_auctions.class_type='Feeder')
ORDER BY usda_cattle_auctions.report_date DESC

Modify the query by going to connection properties under the data tab and click on definition. Just substitute the command text and excel will ask for the parameters after you click ok. Then a parameter button will show up in connection properties so you can edit the parameters.

Excel Postgresql Database Connection Use Cell as Date

1 Answers1