Is there a way to allow the user to select the comparison option for a combo-box?

[HSXWillH]

I have a large query with 6 selection fields that I allow the user to choose any and all from 1 single input form.

The 6 option fields on Form SearchForm are: Track, Distance, Surface, Track_Condition , Final_Pos and Race_Type. The 6 particular combo boxes are cboTrack, cboDistance, cboSurface, cboTrkCond, cboFinalPos and cboRaceType.

I've set up a query/filter that works using the selection criteria [Forms]![SearchForm]![cboTrack]etc... in alternating manners so that any combination of the 6 combo-boxes will filter the data to exactly what is chosen. This works exactly how I wish it to.

My question is, I wish to allow the user to select in conjunction with the FinalPos combo-box, an operator option <, <=, =, >, >= along with the cboFinalPos input so that in theory, they could say < 3 and filter out all data except Positions 1 & 2...and so on and so forth.

My effort at this point has been to create a new combo-box entitled cboOperators but I am running into difficulty in getting the existing query to work. I attempted, in the Final_Pos field of the filter query, to place the code [Forms]![SearchForm]![cboOperators][Forms]![SearchForm]![cboFinishPos] in, hoping that if the user selected "<", Access would read it as <[cboFinishPos] and filter accordingly. The error is "The Expression You Entered Contains Invalid Syntax. You may have entered an operand without an operator."

Is what I'm trying to do possible first off and if so, what am I doing wrong?

[ajalwaysus]

Yes it is possible, but what you need to do is build and "IF" or "Case" statement that evaluates the value of the drop down that contains all the operators you want, because you cannot pass operators as text as you are doing now.

Try something like this...

    SELECT CASE cboFinishPos
    CASE ">"
          strWhere = " WHERE Final Position > " & me.cboFinalPos.value
    CASE "<"
          strWhere = " WHERE Final Position < " & me.cboFinalPos.value
      .
      .
      .
    END SELECT

    strSQL = strSQL & strWhere

Basically you need to build you query piece by piece then bring it together in the end before you execute it.

I know this is a little vague, but I am hoping this gets you in the right direction, if you need any more help don't hesitate to ask, my purpose here is to just get you started.

Also, if you do have any questions on how to implement this, we need a better detail of what you are doing, please post your code with any questions, and don't forget to use the [CODE] brackets when posting code.

-AJ

[HSXWillH]

Okay, I'll be honest...I have limited knowledge of Case and SQL itself to know where to begin implementing this.

Right now, my filter query is entitled Selection_All and the SQL for that is huge. All my knowledge on how to do the SQL for it is out of the basic MS Access Query Design screen so the code here is far more massive than I expected so my apologies in advance. If there's a more streamlined method to do this, please let me know.

And I'm hoping to figure out the method that

((FPS_Matrix.Final_Pos)=[Forms]![SearchForm]![cboFinishPos])

has the = replaced with the cboOperator selection.

SELECT FPS_Matrix.Date, FPS_Matrix.Race_No, FPS_Matrix.Horse_Name, FPS_Matrix.Track, FPS_Matrix.Distance, FPS_Matrix.Surface, FPS_Matrix.Track_Condition, FPS_Matrix.Final_Pos, FPS_Matrix.Race_Type, FPS_Matrix.[1FR], FPS_Matrix.[1st_Pos], FPS_Matrix.[2FR], FPS_Matrix.[2nd_Pos], FPS_Matrix.[3FR], FPS_Matrix.AP, FPS_Matrix.EP, FPS_Matrix.SP, FPS_Matrix.Fx, FPS_Matrix.[%E], [Date] & ":" & [Track] & ":" & [Race_No] AS RaceID
FROM FPS_Matrix
WHERE (((FPS_Matrix.Track)=[Forms]![SearchForm]![cboTrack2]) AND ((FPS_Matrix.Distance)=[Forms]![SearchForm]![cboDistance]) AND ((FPS_Matrix.Surface)=[Forms]![SearchForm]![cboSurface]) AND ((FPS_Matrix.Track_Condition)=[Forms]![SearchForm]![cboTrkCond]) AND ((FPS_Matrix.Final_Pos)=[Forms]![SearchForm]![cboFinishPos]) AND ((FPS_Matrix.Race_Type)=[Forms]![SearchForm]![cboRaceType])) OR (((FPS_Matrix.Distance)=[Forms]![SearchForm]![cboDistance]) AND ((FPS_Matrix.Surface)=[Forms]![SearchForm]![cboSurface]) AND ((FPS_Matrix.Track_Condition)=[Forms]![SearchForm]![cboTrkCond]) AND ((FPS_Matrix.Final_Pos)=[Forms]![SearchForm]![cboFinishPos]) AND ((FPS_Matrix.Race_Type)=[Forms]![SearchForm]![cboRaceType]) AND (([Forms]![SearchForm]![cboTrack2]) Is Null)) OR (((FPS_Matrix.Track)=[Forms]![SearchForm]![cboTrack2]) AND ((FPS_Matrix.Surface)=[Forms]![SearchForm]![cboSurface]) AND ((FPS_Matrix.Track_Condition)=[Forms]![SearchForm]![cboTrkCond]) AND ((FPS_Matrix.Final_Pos)=[Forms]![SearchForm]![cboFinishPos]) AND ((FPS_Matrix.Race_Type)=[Forms]![SearchForm]![cboRaceType]) AND (([Forms]![SearchForm]![cboDistance]) Is Null)) OR...etcetcetc.....
ORDER BY FPS_Matrix.Date DESC;

[ajalwaysus]

Before I take this on, what is this query being used for? Is it a recordset for a form?

-AJ

[HSXWillH]

Basically, I have a large amount of data entry of horse running lines. That data is in table Horse_Lines. I then do some basic calculations in the FPS_Matrix query to come up with my interpretation of the data.

I have a form SearchForm that the user chooses any of the 6 selections (Track/Distance/Surface/FinalPos/Track_Condition/Race_Type) and Selection_All query filters from the form selections as in the above larger code snippet. The remaining data pieces are then used in various reports to determine averages of times that only meet the user-selected criteria.

I am probably not explaining this well at all so my apoligies in advance.

[NeoPa]

Technically, if I understand your original question correctly, you cannot do what you are proposing.

What you can do, and I believe AJ is planning to lead you this way, is to create a SQL string within your code, and set the .RecordSource (or even better the .Filter) of a form or report (or set .SQL within a query) to the intelligently adjusted SQL you can build.

This is an example of where building references to Form controls within your query is quite limited. Why many avoid that method and prefer manipulating the SQL.

[HSXWillH]

Okay....a complete overhaul seems like the path forward. And given what I've read today, it's way over my head. Time to hit the books.

[NeoPa]

Before I take this on, what is this query being used for? Is it a recordset for a form?

Always a good idea to read up Will, but now you've explained that the main use will be as a recordset (or maybe filter) for various reports in your database AJ is likely to be able to assist and lead you along.

[ajalwaysus]

What I am suggesting you do, as NeoPa said was, I think the best way to tackle this is to break this query out, so that we can build each "Where" piece as we evaluate it.

Example:

Dim strSQL as String
Dim strSelectSQL as String
Dim strFinal_PosClause as String
Dim strOrderByCaluse as String

  strSelectSQL = "SELECT FPS_Matrix.Date, FPS_Matrix.Race_No," & _ 
FPS_Matrix.Horse_Name, FPS_Matrix.Track, FPS_Matrix.Distance, " & _
FPS_Matrix.Surface, FPS_Matrix.Track_Condition, FPS_Matrix.Final_Pos," &_
FPS_Matrix.Race_Type, FPS_Matrix.[1FR], FPS_Matrix.[1st_Pos]," & _
FPS_Matrix.[2FR], FPS_Matrix.[2nd_Pos], FPS_Matrix.[3FR]," & _
FPS_Matrix.AP, FPS_Matrix.EP, FPS_Matrix.SP, FPS_Matrix.Fx," & _
FPS_Matrix.[%E], [Date] & ":" & [Track] & ":" & [Race_No] AS RaceID" & _
FROM FPS_Matrix"

  SELECT CASE cboFinalPositionOperator
  CASE ">"
        strFinal_PosClause = " WHERE FPS_Matrix.Final_Pos > " & me.cboFinalPos.value
  CASE "<"
        strFinal_PosClause = " WHERE FPS_Matrix.Final_Pos < " & me.cboFinalPos.value
    .
    .
    .
  END SELECT

  strOrderByClause = "ORDER BY FPS_Matrix.Date DESC" 
  'This is where you put the Select, Where and OrderBy clauses together to make a valid query.
  strSQL = strSQL & strFinal_PosClause & strOrderByClause

  [Your_Form_Name_Here].Form.Recordsource = strSQL

This is a very basic example, but I wanted you to get an idea of which way I'm leaning, so that you can keep this in mind as you study up on this method.

Let me know if you have any questions,
-AJ

[NeoPa]

AJ,

Can I suggest that you lean towards a static query (SQL or QueryDef) for the RecordSource of the objects themselves, and merely build up the WhereCondition (or SQL filter) string using the code. This keeps the original object (report) intact, and allows you to specify this as a string value on the fly upon opening the report. The effect is the same, it just involves a little less work (and doesn't require such a detailed understanding of what's already in as the base SQL or QueryDef).

Please don't think I'm trying to teach you to suck eggs here. Just a point to consider.

[HSXWillH]

I appreciate all the assistance. Right now, I'm realizing how amateur I really am at this stuff becuase none of it's making much sense to me.

[NeoPa]

We can take it step-by-step.

You have a bunch of reports that you would like to be able to run filtered. Is that right?

[NeoPa]

Please respond to the previous post when you can, but in the meantime, and when you get a spare few minutes, have a look through Example Filtering on a Form. It will introduce you to some important concepts related to this. It also provides an example database as an attachment which you can play with and really understand what is going on.

[HSXWillH]

We can take it step-by-step.

You have a bunch of reports that you would like to be able to run filtered. Is that right?

Yes. Basic query with some calculated fields that upon filtering will be the source for more reports and more summations. Basically, I'm going to look for grouped averages and standard deviations from the filtered data in various formatted reports.

[NeoPa]

Yes. Basic query with some calculated fields that upon filtering will be the source for more reports and more summations. Basically, I'm going to look for grouped averages and standard deviations from the filtered data in various formatted reports.

I'm trying to simplify. Break it down into easy steps.

Yes is a fine answer. The rest leaves me totally confused.

Is the filtering required for reports or is the intention to use the filtered results to feed through into other objects (queries, forms, reports, etc.)?