How to create a Parameter Query with General Form Reference

[EricRex]

I have several queries that populate a Modern Chart dataset. Each of these queries use a common subform's start and end date as parameters. They are of the form:



PARAMETERS [Forms]![formName]![subfrmFilters]![txtStartDate] DateTime, [Forms]![formName]![subfrmFilters]![txtEndDate] DateTime;
SELECT DSum("columnName","StockTrades","TradeDate BETWEEN Forms![formName]!subfrmFilters!txtStartDate AND Forms![formName]!subfrmFilters!txtEndDate AND TradeDate <= #" & TradeDate & "#")) AS TradeDate
FROM StockTrades
WHERE TradeDate Between Forms!formName!subfrmFilters!txtStartDate And Forms!formName!subfrmFilters!txtEndDate;

In the above, formName is the name of the form the query is called from. These queries populate a Modern Chart in the Form.
This works great but the Parameters as shows requires one query per form because it is specific to the particular formName that it's being invoked from.

I'd like to know if it's possible to replace formName with the equivalent of Me.Name from VBA. That is, how can I make these queries detect the form they're invoked from, rather than hard-code each of query per form?

It would simplify the queries and allow me to use them and possibly combine them in multiple charts. For example, I could create one query for Trade Profitability, another for Trade Risk, and so on. But, as it stands, each form/chart requires a custom query.

Thanks!
Eric

[CJ_London]

number of options.

1. have your subform save the start/end date values to a parameters table and modify your sql

SELECT DSum("columnName","StockTrades","TradeDate BETWEEN " & tblParameters.StartDate & " AND " tblParameters.EndDate & " AND TradeDate <= #" & TradeDate & "#")) AS TradeDate
FROM StockTrades, tblParameters
WHERE TradeDate Between tblParameters.StartDate And tblParameters.EndDate;

2. use code to set the parameters - but not sure this would work for modern charts

sql:
PARAMETERS StartDate DateTime, EndDate DateTime;
SELECT DSum("columnName","StockTrades","TradeDate BETWEEN " & [StartDate] & " AND " & [EndDate] & " AND TradeDate <= #" & TradeDate & "#")) AS TradeDate
FROM StockTrades
WHERE TradeDate Between [StartDate] And [EndDate]

vba - something like this - (google 'access vba set parameters' or similar to find out more)
currentdb.querydefs("myquery").parameters("StartDa te")=txtStartDate
currentdb.querydefs("myquery").parameters("EndDate")=txtEndDate
now open your chart

[EricRex]

number of options.

1. have your subform save the start/end date values to a parameters table and modify your sql
...

2. use code to set the parameters - but not sure this would work for modern charts

sql:
...
vba - something like this - (google 'access vba set parameters' or similar to find out more)
...
now open your chart

Hey CJ_London,

#1 is a really interesting idea which I will explore. The only challenge is having multiple forms with multiple charts that would use the same date filters set by whichever form/subform/chart happened to change the dates most recently. That might not be as big a problem as it might suggest because I've been pondering setting a reasonable initial setting versus the entire dataset. The dozen or so tables of data is from the Federal Reserve Economic Data website, aka, FRED, and spans several decades. However, mostly I don't need to see that whole range.

#2 is problematic because the form contains both the subform and the chart, so modifying the start and end dates in the subform means the chart is already open. However, worth considering in some yet-undefined, modified form.

Thanks!
Eric

[CJ_London]

re #2 - might be as simple as requery the chart?

[June7]

I Refresh form to update chart display.

[Welshgasman]

You could also use TempVars as the parameters. That is what I used to do. I never tied any query to a particular form.

[EricRex]

number of options.

1. have your subform save the start/end date values to a parameters table and modify your sql

SELECT DSum("columnName","StockTrades","TradeDate BETWEEN " & tblParameters.StartDate & " AND " tblParameters.EndDate & " AND TradeDate <= #" & TradeDate & "#")) AS TradeDate
FROM StockTrades, tblParameters
WHERE TradeDate Between tblParameters.StartDate And tblParameters.EndDate;

Thank you so much for this idea.

The table idea turns out to be a wonderful improvement because I use the table for all of the charts and forms which makes it easy to focus in on a particular timeframe, rather than changing it for each form.

To ensure future viewers get the right info, the dates need #'s:

SELECT DSum("columnName","StockTrades","TradeDate BETWEEN #" & tblParameters.StartDate & "# AND #" tblParameters.EndDate & "# AND TradeDate <= #" & TradeDate & "#")) AS TradeDate
FROM StockTrades, tblParameters
WHERE TradeDate Between tblParameters.StartDate And tblParameters.EndDate;

Thanks again,
Eric

[CJ_London]

Just be aware if your app is going to be used outside of the USA, date text formats are (mostly) different. SQL expects either the US format of mm/dd/yyyy, the sql standard of yyyy-mm-dd or in a format that is unambiguous such as dd mmm yyyy (e.g. 10 Jun 2024).

In the UK date formats are dd/mm/yyyy. If the date was 13/6/2024 (13th June) it is unambiguous since there are not 13 months in the year, but a UK date of 5/11/2024 (5th November) would be interpreted as 11th May.

So you might want to use

............ BETWEEN #" & format(tblParameters.StartDate,"yyyy-mm-dd") & "# AND ............

As I am in the UK, I am well used to this issue.

[EricRex]

You could also use TempVars as the parameters. That is what I used to do. I never tied any query to a particular form.

Interesting idea that spurred me to learn about TempVars which I've never used. I don't see the advantage of using TempVars in place of a table name that actually has the data.

I implemented CJ_London's idea of putting the values in a table which allows all forms/charts of the same kind to get the current start/end dates.

It seems to me that TempVars would proliferate to the point that everything is stored there, but nothing is semi-permanent as it is in a table.

Can you help me understand why you prefer TempVars?

Cheers,
Eric

[Welshgasman]

The TempVars are purely to hold the parameters. Full stop.
If you want to have a form to enter the data into a table which you then read to get those parameters, or put the values direct into the Tempvars.

I would use a table if the parameters were the same most if not all of the time?

When I used TempVars they were not, they could be a start and end date, or a list of users. Criteria was dynamic, so a table does not help, unles you want to store what was used last. i did not.

[EricRex]

I would use a table if the parameters were the same most if not all of the time?

Ah, that makes a lot of sense. You would have been all over the map keeping track of what parameters you're using at the moment. Thanks for the explanation.

In my case, the parameters are always the same. Stock symbol, Timeframe (# of Weeks, Months, Years), and start and end date. Thus, a table is easy and fixed.

Cheers,
Eric