Text Parameter input string to Saved Query is ignored

[Welshgasman]

No,


Before I upgraded to 2007 I had to copy a report object, so as to be able to create a pdf file from it.
When I upgraded to 2007, it will print direct to to pdf.

So if you have the rest of the SQL and a WHERE clause, you can replace that with your built up sql from the form controls. I would not use inputbox, cannot validate the entries.

I believe you do not need to save a qdf after modifying it, so you cannot use replace again as it will have the old criteria, so copying the query allows the replacement.
Bit like having a template in Excel, Word, or Outlook.

[CJ_London]

Also cPosSizeR is text so even if you could set parameters that way, it would probably fail due to a data type error

you can use the eval function - sql would be something like

PARAMETERS [sdate] DateTime, [edate] DateTime, [andClause] Text ( 255 );
SELECT Count(cPosSizeR) AS fieldValue
FROM StockTradesT
WHERE StockTradesT.TradeDate Between [sdate] And [edate] AND Eval(""" & [andClause] & """)=true

and because it is text, your andClause gets really complicated with single and double quotes

this is what you want to end up with

Eval("'" & [cPosSizeR] & "'>'0'")

I will leave you to work it out

[EricRex]

Hey CJ,

I tried your code above, simplified as:
PARAMETERS [andClause] Text ( 255 );
SELECT Count(cPosSizeR) AS fieldValue
FROM StockTradesT
WHERE Eval(""" & [andClause] & """)=true

with input andClause = cPosSizeR < 0

And I got back one as the answer. It should have been zero in the sample DB I provided above.

Simplifying even more for test purposes, I got this far:

PARAMETERS [andClause] Text ( 255 );
SELECT COUNT(cPosSizeR) AS fieldValue
FROM StockTradesT
WHERE Eval((Str(cPosSizeR>0));

This actually gives the result I'm looking for.

Unfortunately, this is also identical to the standard version:
WHERE cPosSizeR>0;

I have tried several combinations of replacing the cPosSizeR>0 string with [andClause] and none of them work. All give the error: This expression is typed incorrectly, or it is too complex to be evaluated...
WHERE Eval(Str(cPosSizeR>[andClause])); with input andClause = 0
WHERE Eval(Str([andClause]>0)); with input andClause = cPosSizeR
WHERE Eval(Str([andClause])); with input andClause = cPosSizeR > 0 and with andClause = "cPosSizeR > 0" and with andClause = [cPosSizeR] > 0

Am I missing something here?

[CJ_London]

Am I missing something here?

yes - you haven’t done what I suggested since your eval string is returning true - or to be more precise a value which is not false - an error is not false.

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

it doesn’t help that you are treating you evaluation as a numeric calculation when in fact as I pointed out, it is a string

[orange]

Eric,

Is this the sort of thing you are doing?

Sub Eric001()
Dim qdf As DAO.QueryDef
Dim sqlBase As String

'Start with base SQL (Basically the Parameter query without the Parameter line)

sqlBase = " SELECT Count(cPosSizeR) AS fieldValue" _
     & " FROM StockTradesT " _
     & "WHERE StockTradesT.TradeDate Between [sdate] And [edate] AND   [andClause] "
  'Replace the parms with your intended value
  'Repeat for each parm
  sqlBase = Replace(sqlBase, "[sdate]", "#4/5/2024#")
  Debug.Print sqlBase
  sqlBase = Replace(sqlBase, "[edate]", "#4/28/2024#")
  Debug.Print sqlBase
   sqlBase = Replace(sqlBase, "[andClause]", "cPosSizeR >4")
  Debug.Print sqlBase
  ' this results in intended query sql
  
  'Now create a permanent querydef named MySavedQUERY
  With CurrentDb
  Set qdf = .CreateQueryDef("MySavedQuery", sqlBase)
  End With
  
End Sub

If so, you can get parameter values from another table. I'm still not certain of the "22 base queries" I can image a "parms table" that has parm names and values as well as a query name, then processing these in a "replacement type scenario". You could, if you have not done so, experiment with your "required queries", make a Parameter query sql (and even if it doesn't work as a parameter query) parse that sql (could be a text string) and perform replacements similar to the above sample.

Perhaps you could show us some of the 22 base queries and the issues/problems.

[EricRex]

Hey orange,

I'm literally starting from the saved query shown above, trying to make it work.

I don't intend to create each query as you've done because it will result in hundreds or thousands of saved queries.

[orange]

OK. I agree t would result in many, many queries. Still trying to understand your real requirement.
What exactly are your ~22 bases?
Here's an idea.

Suppose you have your base SQL with its parameters -that doesn't work the way you want. And suppose you could, using some vba,
parse the Parameter query base and get the parameter names, loop through a dialog and get value for each parm, automatically do a replacement of parm with proper values and run the query. You get a result with this session of that parameter query. Youake no changes to the base.
Suppose you know the ~22 base queries, and you can invoke a process to select the query you want to process.
It does the replacement values and gets you the result -without changing your base query.

This doesn't have (without more design)
-any validation as such
-steps to ensure # are supplied for dates
-more

In effect you would have your 22 base queries and procedures to run them. You would supply the values for the parms and have logic to validate/adjust as needed.

You could have something like

Sub eric002()

    Dim qdf As DAO.QueryDef
    Dim prm As Integer
    Dim p1 As String, i As Long
    Dim arr
    For Each qdf In CurrentDb.QueryDefs
        If qdf.name = "ParmBase1" Then
            For prm = 0 To qdf.Parameters.Count - 1
                ' Debug.Print qdf.Parameters(prm).name  ', qdf.Parameters(prm).Type
                p1 = p1 & qdf.Parameters(prm).name & "|" & InputBox("enter value for " & qdf.Parameters(prm).name, "My Title") & "|"
                
            Next prm
     '       Debug.Print p1
        End If
       Next qdf
       arr = Split(p1, "|")
       ' Debug.Print LBound(arr); UBound(arr)
        For i = LBound(arr) To UBound(arr) - 1 Step 2
            Debug.Print arr(i) & " >>VALUE >> " & arr(i + 1)
        Next i
   
End Sub

that would produce a result :

[sdate] >>VALUE >> #4/1/24#
[edate] >>VALUE >> #11/30/24#
[andClause] >>VALUE >> cPosSizeR = 3

This could be adjusted and used with the earlier code(Eric001) to validate and supply values in the Replace functions.

I know the replacement SQL works. I did not find a way to get Eval to work, but it isn't something I use.


ParmBase1 was this Parameter query

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

Added a ParmBase2

PARAMETERS [sdate] DateTime, [edate] DateTime, [andClause] Text ( 255 ), [andSymbol] text (10);
SELECT Count(cPosSizeR) AS fieldValue
FROM StockTradesT
WHERE StockTradesT.TradeDate Between [sdate] And [edate] AND   [andClause] and [andSymbol]

Processed it to get

[sdate] >>VALUE >> #2/15/24#
[edate] >>VALUE >> #6/23/24#
[andClause] >>VALUE >> cPosSizeR <6
[andSymbol] >>VALUE >> Symbol ="Tesla"

[CJ_London]

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?

Whilst criteria is all 'AND's or all 'OR's building criteria is straightforward. Gets a lot more complicated when you mix them. So if your sql was

WHERE fld1>=p1 and fld1<=p2 OR fld1=p3

it has to be written as

WHERE (fld1>=p1 and fld1<=p2) OR fld1=p3

or perhaps the requirement is

WHERE fld1>=p1 and (fld1<=p2 OR fld1=p3)

have a continuous form with perhaps 3 columns

user can select a field name in the first column
in the second they select from ><= etc
in the third they enter a value

repeat for another criteria

your vba can then validate the criteria based on field datatype whether you need to add quotes etc

and apply it as a filter to the base query

[EricRex]

All,

I attached 3 screenshots of the most important aspects of my project as well as a spreadsheet that contains the baseline SQL Query info. Other tables have the detailed query info.

For each field in each group (weekday, pattern, etc.), the code I mentioned earlier takes the query info for each field and fills in the dates from the form, the weekday or pattern (or other group type) from the table, and replaces the %keyword variables to create a valid SQL query. That query returns a single value that's populated into the form. Obviously, the filters fields (dates and symbol), alter the %keyword variables in the query.

From this, I hope you understand how creating a Saved Query for each field X each group (Mon, Tue, etc), X each form X each date combination isn't useful or practical.

Thus, my attempt to create query templates that can take input WHERE and AND parameters. Sadly, I don't remotely understand CJ's posts about how to do that. I obviously am much too inexperienced to understand the first part of his solution. I haven't been able to make heads or tails of his simplest statements and haven't made any progress in trying to implement his recommendations.

Cheers,
Eric

[davegri]

ericrex-davegri-v01.zip

See if this approach will work for you. Queries are created on the fly, based on SQL statements which include the criteria. No need for replacement code. If the query already exists, it will be updated with the new SQL.

[Welshgasman]

And the buttons could just as easy be a combo entry and one button to run it?
That is how I ran various queries and reports.

[orange]

Eric,

Review DaveGri's sample - his vba code shows you how to modify/manipulate (or create) a query's SQL based on inputs on a Form. As Welshgasman said, the inputs could be selections from combo. You could have additional options for Between , > or < etc.. depending on your detail needs. I think Dave's underlying vba/functions do much of what are asking.

Aside: Have you moved toward "Alpha Vantage" as mentioned back in May? Or, building on with YahooFin.

[EricRex]

See if this approach will work for you. Queries are created on the fly, based on SQL statements which include the criteria. No need for replacement code. If the query already exists, it will be updated with the new SQL.

Hey davegri,
Thanks for your ideas. Although I need to think about your sample some more, my first impressions are:
1. This is an "input" type form where you feed the code the parameters from the form, and the queries are hardcoded in VBA. whereas mine is very much an "output" or report-style form where there are a whole bunch of preset queries stored in the DB and the few inputs come from the form, but the outputs are on that same form. The queries are all well-defined as I showed in my excel output of the table where they're stored.
2. It seems to me that this sample's purpose is to generate queries. Am I correct in that understanding? But won't it generate a new query for every set of parameters? Or will it simply update the existing query? Either way, I'm not sure it makes sense in my situation. In my "report" form, I'm analyzing my trades looking for results, trading patterns to enhance or correct, trading errors and mistakes, and so on. The queries will change literally every time I look at the forms. Storing those queries in the DB seems an unnecessary step.
3. The real value I get from your sample is the idea of putting the query templates in the Code instead of the DB. As you pointed out, I could avoid all of the string replacement code, the arbitrarily defined keywords and the ever-increasing complexity of the queries and code to go with it.

I'm not sure which is better: Change the code when I add complexity, or change both the code and the DB that drives the code? As it is now, there's a single routine that generates all of my forms output regardless of level of detail. I kinda like that.

Did I understand your project sufficiently or am I missing something?

Cheers,
Eric

[EricRex]

Eric,

Review DaveGri's sample - his vba code shows you how to modify/manipulate (or create) a query's SQL based on inputs on a Form. As Welshgasman said, the inputs could be selections from combo. You could have additional options for Between , > or < etc.. depending on your detail needs. I think Dave's underlying vba/functions do much of what are asking.

Aside: Have you moved toward "Alpha Vantage" as mentioned back in May? Or, building on with YahooFin.

Hey orange,

I did review, as you can see. Here's the thing, the inputs are really somewhat beside the point. Yes, they're necessary but relatively few and fixed. Stock symbol and start and end dates are all you see in my sample forms. However, the "hidden" inputs are many and complex, although more or less fixed. Examples of hidden first level inputs: day of the week, chart pattern, trade direction. Second level inputs combine the first level inputs with more criteria: time of day is the easiest to understand. So, I'd have a report-style, or analysis form if you prefer, that you saw in my screenshots that would have the same 20+ output values (numtrades, numwins, etc.) but filtered by time of day and chart pattern. Or the same style of form with time of day and trade direction.

Hope that helps explain how the inputs are not really the issue or my focus.

I really want to have 22ish Saved Queries that I can feed the parameterized WHERE clauses and AND clauses that are the core of this entire project.

More examples of how to create queries is missing the point.

Aside, yes, I moved into AlphaVantage which was fruitful. Then I found Polygon.io which is even better for a number of reasons.

Cheers,
Eric

[davegri]

Did I understand your project sufficiently or am I missing something?

Well, it's a pretty simple example to understand and it does what is stated. It creates queries on the fly based on user input for the query name and its criteria.
As far as creating too many queries, that is exactly what can be avoided. For example, if you are creating queries for report recordsources, every report can have the same query name for its recordsource. Just use the code to customize the query before opening the report.
In your case, you might be able to get by with one named query instead of 20ish.

However, the "hidden" inputs are many and complex, although more or less fixed. Examples of hidden first level inputs: day of the week, chart pattern, trade direction. Second level inputs combine the first level inputs with more criteria: time of day is the easiest to understand. So, I'd have a report-style, or analysis form if you prefer, that you saw in my screenshots that would have the same 20+ output values (numtrades, numwins, etc.) but filtered by time of day and chart pattern. Or the same style of form with time of day and trade direction.

Couldn't those "hidden" inputs also be strings passed to the SQL configurator?