Text Parameter input string to Saved Query is ignored

[EricRex]

Personally I would use a form to capture the criteria and use vba to create the sql - what are you going to do with the query once you have it? display in a subform? export to excel or csv file? something else?

Hey CJ,

If you look at my uploaded screenshots, and look at my response to orange's comments, my criteria are all AND's, and quite fixed. The point of parameterizing the queries is to allow me to simply execute the query with a WHERE clause and multiple AND clauses that vary by Form purpose. The Summary Form is the most basic with the only inputs being the Filters choices of Stock Symbol and start and end dates. The By Weekday Form is slightly more complex with all of the Summary WHERE clause info plus an AND clause of Weekday = one of: Mon, Tue, etc. The By Pattern Form is again, the Summary Form, plus AND clause of Pattern = one of: pattern1, pattern2, etc. The next level of complexity is the By Direction and Pattern Form which I haven't created yet. For this report, I would simply add another AND clause of AND Direction = either long or short.

I hope by this you can see the value of having parameterized WHERE and AND clauses in Template Queries. The WHERE is relatively fixed and parameters come from the Filters subform on the Form. The ANDs vary by Form type.

Sadly, I don't have the skills or understanding to do what you suggested I do which is to treat the sample WHERE clause as a string. I'm relatively new to Access and SQL, and your samples, while promising, are out of my reach. I hope you see that I'm willing to work hard on this, but I simply don't know how to do what you're suggesting, even at the most basic level. For example, you suggested:

remove the count and group by and see what the eval function is actually returning

I tried a bunch of combinations of that and didn't get results that meant anything to me. I see the promise your approach holds, but don't know what to do. Google hasn't provided any insights either.



Would you be willing to help me with this at a very beginner level?

Cheers,
Eric

[orange]

Eric,

I'm pretty sure that DaveGri's approach - adding your selected parameter values to appropriate base SQL(s) from a form(s) and processing via some reusable vba/functions will get what you need. You don't necessarily have to save the individual, modified queries- they could be temporary querydefs, or you could have a cleanup process to delete them.

I'd like to see/hear more of you "polygon.io" approach ---perhaps PM.

[EricRex]

I'm pretty sure that DaveGri's approach - adding your selected parameter values to appropriate base SQL(s) from a form(s) and processing via some reusable vba/functions will get what you need. You don't necessarily have to save the individual, modified queries- they could be temporary querydefs, or you could have a cleanup process to delete them.

I'd like to see/hear more of you "polygon.io" approach ---perhaps PM.

It's surely workable although I'm pretty sure it will require continued string replacement. I'm thinking about the desirability of defining the queries in the code vs. in the DB. Not convinced that it's an improvement from what I already have working with the SQL chunks in the DB, assembled in the code. If it's not an improvement, I'm quite sure I don't want to re-do what I already have.

Re polygon.io: https://polygon.io/docs/stocks/getting-started
Similar to Alpha-Vantage. You send them a URI, they send you back JSON data. You parse the JSON (I use VBA-JSON but there are others), and you shove it in your DB. They have a more flexible choice of data, although you either get a little, or you get 20 years of data. It's good enough for now. Also, they have a key indicator value I want.

Cheers,
Eric

[orange]

Eric,

A few observations, re your BaseSQL

noteID	note
1	All WHERE clauses are the same " TradeDate Between X and Y" should be WHERE TradeDate BETWEEN #%start_date# AND #%end_date# <<<<< no need for second "and TradeDate"
2	What is cTradePL ??
3	Why not have DetailAnd in your table?? Similar to your "AndClause"???
4	No cTradePL in BaseSQL 6 AvgDollarWin, 8 StDevDollarWin, 13 AvgDollarLoss, 15 StDevDollarLoss, 19 StDevDollarsAll, 21 CumDollars <<<needs attention since SQL fails

[EricRex]

Eric,

A few observations, re your BaseSQL

noteID	note
1	All WHERE clauses are the same " TradeDate Between X and Y" should be WHERE TradeDate BETWEEN #%start_date# AND #%end_date# <<<<< no need for second "and TradeDate"
2	What is cTradePL ??
3	Why not have DetailAnd in your table?? Similar to your "AndClause"???
4	No cTradePL in BaseSQL 6 AvgDollarWin, 8 StDevDollarWin, 13 AvgDollarLoss, 15 StDevDollarLoss, 19 StDevDollarsAll, 21 CumDollars <<<needs attention since SQL fails

Hey orange,

Yes, I saw that same thing about the where clauses. Surprisingly, those weird WHERE's still worked!
2. cTradePL is the actual dollars and cents trade profit/loss.
3. There is a detailand clause in the current, live table. It's for first level detail reports such as By Weekday.
4. That has been fixed in the current, live table.

As a side note, I realized that all of the first level reports are simple Group By clauses. It's a big change compared to my current string replacement code, but it would allow me to have the parameterized queries I asked about at the beginning of this post.

The query for NumWins in the By Weekday report is:
PARAMETERS [startDate] DateTime, [endDate] DateTime;
SELECT Count (cPosSizeR) AS NumWins
FROM StockTrades
WHERE TradeDate BETWEEN [startDate] AND [endDate] AND cPosSizeR > 0
GROUP BY cWeekday
ORDER BY SWITCH(cWeekday = 'Mon', 1, cWeekday = 'Tue', 2, cWeekday = 'Wed', 3, cWeekday = 'Thu', 4, cWeekday = 'Fri', 5);

The ORDER BY clause is only relevant for the Weekday report because it's the only way to get a sensible order.

Not sure this type of query would be useful for second level queries which involve N x M results from N queries. For example, there are 2 trade directions, short and long, and 5 weekdays. Those queries would be something like:
PARAMETERS [startDate] DateTime, [endDate] DateTime, [tdirection] Text, [tweekday] Text;
SELECT Count (cPosSizeR) AS NumWins
FROM StockTrades
WHERE Direction = [tdirection] AND Weekday = [tweekday] AND TradeDate BETWEEN [startDate] AND [endDate] AND cPosSizeR > 0

That's not an easy transposition from the original, but perhaps I could get the text from those queries and append the new parts of those queries. Will have to investigate. This is still going down the route I don't like, which is bunches of queries for bunches of report types.

Cheers,
Eric

[orange]

Eric,

I don't know your current processes and detailed procedures. It seems to me that you separately have a set up to interact with polygon.io and retrieve data to your local table(s). Further, it appears that your ~25 BaseSQL queries (that I extracted from your xslx file) are used to process the data stored in your local table StockTrades. I have looked at the material that DaveGri offered and that I thought would be an option for you. Based on his code I created a "demo/proof of concept" that involves string/parameter replacement that I mentioned earlier.

The proof of concept is a work in progress since I am working with these mock-tables.
StockTrades TABLE
StockTradeID
sName
TradeDate
tVolume
cPosSizeR

and I created table StatsBaseSQLT based on the xlsx
ID
FormFieldName
SQLStatement
WhereClausend
AndClause
Rounding

NOTE:
StockTrades does not have a cTradePL field --so queries requesting it fail.
StatsBaseSQLT does not have a detailand clause so that has not been implemented.

I'm attaching a zip file containing a gif. The gif is fairly short and shows a form with a Listbox of FormFielNames. Select a formFieldName. The SqlStatement for that baseSQL query is loaded and the AndClause,if it exists, is displayed on the form. and Enter a StartDate and an EndDate. Click the genQuery button and the revised query SQL is displayed showing the parameter replacement(s), and a temporary query def is created and used to obtain the FieldValue from StockTrades. The revised query SQL and the FieldValue result are printed in the immediate window. The temporary query def is set to nothing.You can pick another BaseSQL from the listbox, keep the same dates (or change if appropriate) and click genQuery to run the sql and get another FieldValue.

Here are some debug.prints from running a few queries with the same dates.

row 11 ( 0 Based numbers)  value NumLosses  was selected

SELECT Count (cPosSizeR) AS fieldValue FROM StockTrades %whereclause %andclause %detail_andclause
AND cPosSizeR < 0
SELECT Count (cPosSizeR) AS fieldValue FROM StockTrades  WHERE TRADEDATE BETWEEN #1/1/22# AND #2/4/25#   AND cPosSizeR < 0 
The result of Revised base query : NumLosses is FieldValue = 1

row 8 ( 0 Based numbers)  value Expectancy  was selected

SELECT Avg(cPosSizeR) AS fieldValue FROM StockTrades %whereclause %andclause %detail_andclause
Null
SELECT Avg(cPosSizeR) AS fieldValue FROM StockTrades  WHERE TRADEDATE BETWEEN #1/1/22# AND #2/4/25#   AND cPosSizeR < 0 
The result of Revised base query : Expectancy is FieldValue = -3

row 15 ( 0 Based numbers)  value PctLosses  was selected

SELECT COUNT(*) / (SELECT COUNT(*) FROM StockTrades %whereclause %detail_andclause) * 100 AS fieldValue FROM StockTrades %whereclause %andclause %detail_andclause
AND cPosSizeR < 0
SELECT COUNT(*) / (SELECT COUNT(*) FROM StockTrades  WHERE TRADEDATE BETWEEN #1/1/22# AND #2/4/25#  ) * 100 AS fieldValue FROM StockTrades  WHERE TRADEDATE BETWEEN #1/1/22# AND #2/4/25#   AND cPosSizeR < 0 
The result of Revised base query : PctLosses is FieldValue = 25

row 9 ( 0 Based numbers)  value MaxLoss  was selected

SELECT Min(cPosSizeR) AS fieldValue FROM StockTrades %whereclause %andclause %detail_andclause
AND cPosSizeR < 0
SELECT Min(cPosSizeR) AS fieldValue FROM StockTrades  WHERE TRADEDATE BETWEEN #1/1/22# AND #2/4/25#   AND cPosSizeR < 0 
The result of Revised base query : MaxLoss is FieldValue = -3

[davegri]

Orange,

Based on his code I created a "demo/proof of concept" that involves string/parameter replacement that I mentioned earlier.

Pretty nifty animated GIF.

[orange]

Orange,


Pretty nifty animated GIF.

Thanks Dave.
I hope it helps Eric.

[EricRex]

Hey there,

I know I'm pushing the boundaries here, but my use case demands that I try to do this for multiple forms doing the same basic queries with several different sets of criteria.

I have a saved query named QryNumTrades with SQL =
PARAMETERS startDate Date, endDate Date, andClause Text;
SELECT Count (cPosSizeR) AS fieldValue FROM StockTrades WHERE TradeDate >= startDate AND TradeDate <= endDate AND [andClause];

Hey all,

Still pushing the boundaries here. After a lot of trials and errors, I finally found a webpage with mostly what I'm trying to do with parameterizing my Columns in a saved Query:

That website is: https://stackoverflow.com/questions/...lect-the-field

My version below is much, much uglier than his because I have more column choices, but it makes my code much, much cleaner, and gets rid of a bunch of spurious tables with summary info and queries that were mostly identical to each other except the primary Select and Group By column name. By the way, this is the simplest of my 4 Saved Parameter Queries because there's not much info on Trades that made exactly zero dollars. Winning and Losing Trades have 13 fields each! And the Summary Stats have another 9 or so.

PARAMETERS [ResultsBy] Text ( 255 );
SELECT Switch
(
[ResultsBy] = 'Direction', [Direction],
[ResultsBy] = 'Pattern', [Pattern],
[ResultsBy] = 'Weekday', [cWeekday],
[ResultsBy] = 'EntryOrder', [EntryOrder],
[ResultsBy] = 'ExitOrder', [ExitOrder],
[ResultsBy] = 'Symbol', [Symbol],
[ResultsBy] = 'Summary', "Scratches"
) AS Label1, Count(cPosSizeR) AS NumScratch, Round(COUNT(cPosSizeR) / (SELECT COUNT(cPosSizeR) FROM StockTrades) * 100, 2) AS PctScratch
FROM StockTrades
WHERE cPosSizeR = 0
GROUP BY Switch
(
[ResultsBy] = 'Direction', [Direction],
[ResultsBy] = 'Pattern', [Pattern],
[ResultsBy] = 'Weekday', [cWeekday],
[ResultsBy] = 'EntryOrder', [EntryOrder],
[ResultsBy] = 'ExitOrder', [ExitOrder],
[ResultsBy] = 'Symbol', [Symbol],
[ResultsBy] = 'Summary', "Scratches"
);

I would still love to figure out how to parameterize the [cPosSizeR] > 0 (or = 0, or < 0) and I have to figure out how to add my ORDER BY Clause for weekdays. But this is so close!

Cheers,
Eric

[orange]

Eric,

I'm sending my current mock up that I showed previously. You may get some ideas, or get some questions for further discussion.
When you open the database, the form to use is frmWorkerRev.
The file is available here.
NOTE: This link only available for 24 hrs.

Also, Hans suggested using a different approach.