Ask Your Question
0

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

Comments

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
1

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

Ratslinger gravatar image

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

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.

edit flag offensive delete link more

Comments

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

Stats

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

Seen: 53 times

Last updated: Jul 23