Ask Your Question

Query User Inputs from Calc Cells

asked 2020-07-17 00:27:34 +0200

FeedTheChunk gravatar image

updated 2020-07-17 00:28:15 +0200

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
edit retag flag offensive close merge delete


Would setting default start and end dates be sufficient?

CivFan gravatar imageCivFan ( 2020-07-18 21:06:55 +0200 )edit

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.

FeedTheChunk gravatar imageFeedTheChunk ( 2020-07-24 17:35:06 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2020-07-23 04:19:12 +0200

Ratslinger gravatar image

updated 2020-07-23 04:54:58 +0200


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.


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.

edit flag offensive delete link more


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

FeedTheChunk gravatar imageFeedTheChunk ( 2020-07-24 17:36:05 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-07-17 00:27:34 +0200

Seen: 53 times

Last updated: Jul 23