Query User Inputs from Calc Cells

[FeedTheChunk]

Can I create a query that uses data pulled from specific cells in Calc as the user input for a query?

I have a query that I built in the Query Designer that requests a start and dates from the user using >= :StartDate and <= :EndDate. I would prefer to be able to grab the start and end dates from cells on a Calc worksheet.

Now that I have a working query I open a new Calc workbook, View > Data Sources and then I drop the above query into Calc. Works great but I still have to manually enter the start and end dates for the query from the pop up box.

Maybe my method is flawed but I haven’t been able to find any other methods for getting user entered parameters into a query.

This is the section of my query that’s getting the user input:

`TRANSACTIONS`.`transaction_date` >= :StartDate AND `TRANSACTIONS`.`transaction_date` <= :EndDate ORDER BY `TRANSACTIONS`.`transaction_date` ASC

[CivFan]

Would setting default start and end dates be sufficient?

[FeedTheChunk]

No. That’s what I have now. I dont want to have update / change the query every time I need to change the date or some other criteria. I could make a separate query for every possible combination and permutation but man that could be a lot of queries.

[Ratslinger]

Hello,

Yes you can do this using a macro.

When you load the query data into Calc, a database range is created. It is given a name. From menu Data->Define range should give the name. With that name you can use the code in this question → MySQL DATE_ADD not recognized in datagrid. You will need to add your code to extract the dates to be used & resulting code for the macro would be similar (depends upon DB used) to:

"TRANSACTIONS.transaction_date " & myStartDate & " AND TRANSACTIONS.transaction_date " & myEndDate & " ORDER BY TRANSACTIONS.transaction_date AS"

Only posted section you provided. Would need to enter entire select in macro.

Did test last year, and re-tested recently. Was even able to use this method to return data from different databases with separate executions.

Edit:

If not known before, the extracted dates used in the query must be in the format of yyyy-mm-dd (this is text because of the hyphens). This is standard in a database. It need not be that format in your cell.

[FeedTheChunk]

Thank you. I think I can use that and make it work for what I want.