Text Parameter input string to Saved Query is ignored

[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];

When I run it, it asks for the start and end dates, as well as the whereClause value.
When I enter andClause = cPosSizeR > 0, it acts as though I didn't enter the AND at all and returns the count of all records between start and end date.
When I enter andClause = "cPosSizeR > 0", (quoted string) it acts the same way.

Either way, I can't get it to recognize the parameter value and act on it. I would expect that if there were a syntax problem with [andClause], SQL would complain. But, when I enter blahblahblah as the value of andClause, it still doesn't complain and returns the total number of records.

I've tried doing the same thing with a QueryDef in VBA and get the same result.

Thoughts?
Eric

[Micron]

You have to delimit and concatenate, and since I don't see any quote marks or date delimiters it appears you're not doing that. That should explain why it doesn't work in either method. For vba method, maybe
"SELECT Count (cPosSizeR) AS fieldValue FROM StockTrades WHERE TradeDate >= #" & startDate & "# AND TradeDate <= #" & endDate & " AND " & [andClause]

although I don't know if SQL is a string variable or just a word you used to apply to the query sql property.

[orange]

Eric,

Can you provide a sample database with enough data to show the issue in context? I have some time to review.

I did leave a message based on our previous activity.

[EricRex]

Hey Micron,

Your example is a VBA string version of the query which isn't the same as the identically functioning query saved in an Access Query. This isn't a VBA question, but an Access Saved Query issue. I was surprised that you don't use the # for date comparisons in saved queries,

When I execute the SQL string as you wrote it with the AND clause set to "cPosSizeR > 0", it works just fine. That's exactly what I'm trying to get away from. I have several dozen lines of code to replace the WHERE clauses and AND clauses of the various SQL strings stored in a couple of tables.

I was hoping to replace all of that code with Saved Queries that I could simply pass in the appropriate WHERE clauses and AND clauses. From some additional testing, it doesn't seem possible. I could only get parameters to function on the right-hand side of operators. That is, I can't do "cPosSizeR > 0", nor can I enter "> 0". I can enter 0 as an integer parameter. Sadly that's pretty trivial and would cause me to have to create several dozen saved queries, which isn't better than my existing, working code using SQL string queries as you wrote it.

[EricRex]

Hey Orange,

Good to hear from you again. I've been working hard on this project, learning VBA and Access and SQL!

I could give you my DB, but honestly, this is purely a Saved Query question, and my DB wouldn't give you any insight. I simply created a sample query with the above SQL plus parameters to see if I can replace all of my code with a saved query. Then I ran that query with the input parameters.

Any DB table you have with multiple columns could be tested the same way. Simply parameterize an AND clause to replicate.

Cheers,
Eric

Cheers,
Eric

[orange]

Eric,

It would be helpful to know more about "I have several dozen lines of code to replace the WHERE clauses and AND clauses of the various SQL strings stored in a couple of tables."

If it's the same basic query with different values for parameter(s), then maybe some vba with a loop???

[EricRex]

Hey Orange,

You've nailed the point of this post. I already have several dozen lines of code in a loop that build the somewhat complex SQL query strings from component pieces including delimited variables replaced by actual values. I was hoping to replace all of that with Saved Queries. Have done so successfully in a few cases, but the more complex queries like the one above don't appear to work as I'd hoped.

Simple example of the component SQL strings:
NumTrades is composed of these SQL statement parts:
SQLStatement field: SELECT Count (cPosSizeR) AS fieldValue FROM StockTrades %whereclause %andclause %detail_andclause
WhereClause field: WHERE TradeDate >= #%start_date# AND TradeDate <= #%end_date#
AndClause field: <blank>

In my VBA code, I replace all of the % variables with information gathered from this and other tables along with inputs from the Form which all depends on the complexity of the form on display.
Small snippet of that code:
270 Call ReplaceVariableKeywords(strArr(1), whereClauseKeyword, strArr(2))
300 Call ReplaceVariableKeywords(strArr(1), andClauseKeyword, strArr(3))
330 Call ReplaceDateVariables(strArr(1), startDate, endDate)

You see, I've already got this mess working. I want to simplify everrything with a Saved Query with parameters that mimic the above table plus code. It would reduce the number of tables, the amount of code, and make the whole mess more elegant and easier to maintain and expand.

As it is, the single routine that populates a few different forms with the same basic info like number of trades, number of winners/losers, percent winners/losers, etc, is becoming unwieldy and I'm only at the first level of complexity. For example, looking at trade results by day of the week, by chart pattern, and trade direction (short/long). The next level includes results by chart pattern AND trade direction as well as more like that. Then there's the third level with four WHERE critera and beyond that, well...

Saved Queries with Parameters would make everything better. Afraid that's not gonna happen and I'll be stuck with mixing and matching strings ad nauseum. I am guessing that the core problem is that Save Queries aren't strings and are pre-compiled or pre-processed by the SQL engine to speed them up.

Again, the question is, How can I give a Saved Query parameters of WHERE and AND clauses?

Cheers,
Eric

[orange]

I'm just quitting for today.

I'd like to see what you have working. Still trying to understand your " WHERE clauses and AND clauses of the various SQL strings stored in a couple of tables."--- pretty sure that you can generate something using replacements, but it's the detail and usage.

Anyway, you seem to have something---good learning exercise.

?currentdb.QueryDefs("EricParmqry").sql
PARAMETERS [sdate] DateTime, [edate] DateTime, [andClause] text;
SELECT count(cPosSizeR) AS fieldValue
FROM StockTradesT
WHERE StockTradesT.TradeDate between [sdate] AND  [edate]
and [andClause];

[EricRex]

Hey Orange,

Thanks for the "between" x and Y. Good stuff.

Unfortunately, this saved query you provided above performs exactly as I expected and the same as my original post. It acts as though the AND clause isn't present. It returns the total count of records and the same as your query without the AND clause below:
PARAMETERS [sdate] DateTime, [edate] DateTime, [andClause] text;
SELECT count(cPosSizeR) AS fieldValue
FROM StockTradesT
WHERE StockTradesT.TradeDate between [sdate] AND [edate]

Try it on one of your tables?

Cheers,
Eric

[Welshgasman]

Have you tried using a Tempvars?
I would probably have a form that asks for start and endate and extra criteria?

I did something similar a few years back. I had a form that ran saved queries and reports, but a control to add extra criteria.

[CJ_London]

Suggest remove the count from your query to just return the tradedate and the field you are counting and show us what you are entering for the parameters and an example row from the output that is outside the date range specified

[orange]

Eric,

I agree with Welshgasman and CJ--- we need to see more of the data and look for patterns and options. Show us a few examples of your parameter values and the expected resultant sql. This stuff " WHERE clauses and AND clauses of the various SQL strings stored in a couple of tables" is important.

You can work with vba, some loops etc, but we need to see some of the data and parameter values you are working with.

?currentdb.QueryDefs("EricParmqry").Parameters.Cou nt
3
?currentdb.QueryDefs("EricParmqry").Parameters(0). Name
[sdate]

I am aware that I just concocted a table and query to try to understand your situation and need. It was not meant as a solution, but some info that could be helpful when generating queries from some base info.

[EricRex]

PARAMETERS [sdate] DateTime, [edate] DateTime, [andClause] text;
SELECT count(cPosSizeR) AS fieldValue
FROM StockTradesT
WHERE StockTradesT.TradeDate between [sdate] AND  [edate]
and [andClause];

Hey Orange,

I'm worried that the point of this post is missing in the above comments.

I created the exact scenario I'm talking about in my updated version of your original Yahoo Fin Demo DB and attached it below.
The Table is StockTradesT.
The Saved Query is qryNumWinners
Double-click on the query to run it.
For parameters, enter sdate = 5/29/24, edate = 6/10/24, andClause = cPosSizeR < 0
You will receive an output value of 1.

Inspecting the StockTrades table shows a single row with cPosSizeR > 0.

Rerun the query with andClause = blahblahblah and you still get the output = 1.

[Welshgasman]

Not sure you can even do that?
the AND clause is criteria not a field?

You need to compare that to something in the table?, and there is nothing.

That is my 2 cents on this?

PARAMETERS [sdate] DateTime, [edate] DateTime, [andClause] Text ( 255 );
SELECT COUNT(cPosSizeR) AS fieldValue
FROM StockTradesT
WHERE StockTradesT.TradeDate between [sdate] AND [edate] AND [andClause];

You can do this by modifying the qdf.
I would copy the base query and add the whole WHERE clause and then run the copied object, then delete.

[EricRex]

Hey Welshgasman,

You nailed it exactly. Not even sure you can do that.
As I said in the OP, I know I'm pushing the boundaries.

Moreso, I tried it and it does not work.
Therefore, you can't.

From what I can tell, a parameter only works when it's on the right side of an operator like Between, or > or Like.

You have an interesting idea to copy the whole query, create a new modified QDF from that, and so on, but that's very much what I'm already doing in the many lines of code that read the SQL strings from the DB tables and replace the keywords with actual values.

Using one simplified example from above, in my table, I have the following fields and values:
SQLStatement field = SELECT Count (cPosSizeR) AS fieldValue FROM StockTrades %whereclause %andclause
WhereClause field = WHERE TradeDate >= #%start_date# AND TradeDate <= #%end_date#
AndClause field = AND CPosSizeR > 0

The code gets the 3 fields, then replaces the %whereclause variable with the WhereClause field value, and replaces the %andClause variable with the AndClause field value. It also replaces the %start_date and %end_date variables with start and end dates from the form.

Then it executes the query to retrieve the resulting value.

I have about 22 of these base queries with varying degrees of complexity. Then, for more detailed analyses, there are additional criteria which are additional AND clauses from different tables. A simple example of that is to add the Stock Symbol such as, "AND Symbol = %symbol" Of course, the %symbol variable must be replaced with the Symbol selected in a listbox in the form.