473,320 Members | 2,202 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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?
Sep 24 '09 #1

✓ answered by NeoPa

Replace your " =" at the end of line #4 with the reference to the ComboBox.
Expand|Select|Wrap|Line Numbers
  1. 'cboRaceDate - Date
  2.     If Me!cboRaceDate > "" Then _
  3.         strFilter = strFilter & _
  4.             " AND ([Date]" & _
  5.             Me.cboComparison2 & _
  6.             Format(CDate(Me!cboRaceDate), "\#m/d/yyyy\#") & ")"
You seem to pick this up quite well :)

PS. It seems you have avoided the use of the With Me construct. Not a very good idea, but ultimately your choice and what you're happiest with.

40 2175
ajalwaysus
266 Expert 100+
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...

Expand|Select|Wrap|Line Numbers
  1.     SELECT CASE cboFinishPos
  2.     CASE ">"
  3.           strWhere = " WHERE Final Position > " & me.cboFinalPos.value
  4.     CASE "<"
  5.           strWhere = " WHERE Final Position < " & me.cboFinalPos.value
  6.       .
  7.       .
  8.       .
  9.     END SELECT
  10.  
  11.     strSQL = strSQL & strWhere 
  12.  
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
Sep 24 '09 #2
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
Expand|Select|Wrap|Line Numbers
  1. ((FPS_Matrix.Final_Pos)=[Forms]![SearchForm]![cboFinishPos]) 
has the = replaced with the cboOperator selection.

Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM FPS_Matrix
  3. 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.....
  4. ORDER BY FPS_Matrix.Date DESC;
  5.  
Sep 24 '09 #3
ajalwaysus
266 Expert 100+
Before I take this on, what is this query being used for? Is it a recordset for a form?

-AJ
Sep 24 '09 #4
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.
Sep 24 '09 #5
NeoPa
32,556 Expert Mod 16PB
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.
Sep 24 '09 #6
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.
Sep 24 '09 #7
NeoPa
32,556 Expert Mod 16PB
@ajalwaysus
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.
Sep 24 '09 #8
ajalwaysus
266 Expert 100+
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:
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL as String
  2. Dim strSelectSQL as String
  3. Dim strFinal_PosClause as String
  4. Dim strOrderByCaluse as String
  5.  
  6.   strSelectSQL = "SELECT FPS_Matrix.Date, FPS_Matrix.Race_No," & _ 
  7. FPS_Matrix.Horse_Name, FPS_Matrix.Track, FPS_Matrix.Distance, " & _
  8. FPS_Matrix.Surface, FPS_Matrix.Track_Condition, FPS_Matrix.Final_Pos," &_
  9. FPS_Matrix.Race_Type, FPS_Matrix.[1FR], FPS_Matrix.[1st_Pos]," & _
  10. FPS_Matrix.[2FR], FPS_Matrix.[2nd_Pos], FPS_Matrix.[3FR]," & _
  11. FPS_Matrix.AP, FPS_Matrix.EP, FPS_Matrix.SP, FPS_Matrix.Fx," & _
  12. FPS_Matrix.[%E], [Date] & ":" & [Track] & ":" & [Race_No] AS RaceID" & _
  13. FROM FPS_Matrix"
  14.  
  15.   SELECT CASE cboFinalPositionOperator
  16.   CASE ">"
  17.         strFinal_PosClause = " WHERE FPS_Matrix.Final_Pos > " & me.cboFinalPos.value
  18.   CASE "<"
  19.         strFinal_PosClause = " WHERE FPS_Matrix.Final_Pos < " & me.cboFinalPos.value
  20.     .
  21.     .
  22.     .
  23.   END SELECT
  24.  
  25.   strOrderByClause = "ORDER BY FPS_Matrix.Date DESC" 
  26.   'This is where you put the Select, Where and OrderBy clauses together to make a valid query.
  27.   strSQL = strSQL & strFinal_PosClause & strOrderByClause
  28.  
  29.   [Your_Form_Name_Here].Form.Recordsource = strSQL 
  30.  
  31.  
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
Sep 25 '09 #9
NeoPa
32,556 Expert Mod 16PB
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.
Sep 25 '09 #10
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.
Sep 28 '09 #11
NeoPa
32,556 Expert Mod 16PB
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?
Sep 28 '09 #12
NeoPa
32,556 Expert Mod 16PB
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.
Sep 28 '09 #13
@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.
Sep 29 '09 #14
NeoPa
32,556 Expert Mod 16PB
@HSXWillH
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.)?
Sep 29 '09 #15
I intend on using the filtered results to feed through to summary reports.

For instance, if I have 100 records in the master query and upon filtering, I'm left with 10...my reports use those remaining 10 records and group/summarize them with average results.

The basic query calculation I'm utilizing here is FeetPerSecond or FPS. Each horse has an FPS per running line that varies according to its speed. Each running line has over-arcing properties, be it distance, surface, race-track, race-type or finish position.

This filter will filter down all existing running lines into those that meet the specified conditions. I then run reports off the filtered query lines that average out the FPS for all the remaining filtered items.

NeoPa, if necessary, I have the .mdb I'm using currently available so you can see what I'm clearly not explaining well.
Sep 29 '09 #16
NeoPa
32,556 Expert Mod 16PB
So, without getting into anything technical horesewise, might it be accurate to say that in all cases the original data will be filtered before being processed by the various reports that it must feed?
  1. Bearing in mind that filtering may be applied to a report, at the point of opening it, using code.
  2. Reports can apply aggregate functions to their data (RecordSource).
  3. Reports can handle many levels of sorting and grouping within.
Sep 29 '09 #17
Yes, that is how things currently are in the database. The end goal is to have reports that summarize only the records that match the user's selected criteria from among the initial 6 combo boxes.
Sep 29 '09 #18
NeoPa
32,556 Expert Mod 16PB
That's good. Is the base query for all your reports the same?

I expect it is saved as a QueryDef. Can you post the SQL here anyway. That will give me the fields that it should return.

Can you also make a short list of the controls on your form that you intend to use to filter the data intelligently.
Sep 29 '09 #19
The SQL of the query is this:

Expand|Select|Wrap|Line Numbers
  1. SELECT Race_Hdr.Date, Race_Hdr.Track, Race_Hdr.Race_No, Race_Hdr.Distance, Race_Hdr.Surface, Race_Hdr.Track_Condition, Race_Hdr.Race_Type, Horse_Lines.Horse_Name, IIf([Distance]<8,(1320-[1st_Len]*10)/[1st_Time],(2640-[1st_Len]*10)/[1st_Time]) AS 1FR, Horse_Lines.[1st_Pos], Horse_Lines.[1st_Len], (1320+(([1st_Len]-[2nd_Len])*10))/([2nd_Time]-[1st_Time]) AS 2FR, Horse_Lines.[2nd_Pos], Horse_Lines.[2nd_Len], ((([2nd_Len]-[Final_Len])*10)+IIf([Distance]<8,(([Distance]-4)*660),([Distance]-6)*660))/([Final_Time]-[2nd_Time]) AS 3FR, Horse_Lines.Final_Pos, Horse_Lines.Final_Len, IIf([Distance]<8,([1FR]+[2FR]+[3FR])/3,([EP]+[SP])/2) AS AP, IIf([Distance]<8,(2640-[2nd_Len]*10)/[2nd_Time],(3960-[2nd_Len]*10)/[2nd_Time]) AS EP, ([EP]+[3FR])/2 AS SP, IIf([Distance]<8,([1FR]+[3FR])/2) AS Fx, [EP]/([EP]+[3FR]) AS [%E]
  2. FROM Race_Hdr INNER JOIN Horse_Lines ON (Race_Hdr.Date = Horse_Lines.Date) AND (Race_Hdr.Track = Horse_Lines.Track) AND (Race_Hdr.Race_No = Horse_Lines.Race_No)
  3. ORDER BY Race_Hdr.Date, Race_Hdr.Track, Race_Hdr.Race_No, Horse_Lines.Final_Pos;
I read up on that form filter thing and have created a form filter using the query and the first effort I have is as follows for that.

Expand|Select|Wrap|Line Numbers
  1. Private Sub txtTrack_AfterUpdate()
  2.     Call CheckFilter
  3. End Sub
  4.  
  5. Private Sub txtSurface_AfterUpdate()
  6.     Call CheckFilter
  7. End Sub
  8.  
  9. Private Sub txtDistance_AfterUpdate()
  10.     Call CheckFilter
  11. End Sub
  12.  
  13. Private Sub txtTrkCond_AfterUpdate()
  14.     Call CheckFilter
  15. End Sub
  16.  
  17. Private Sub txtRaceType_AfterUpdate()
  18.     Call CheckFilter
  19. End Sub
  20.  
  21. Private Sub txtFinishPos_AfterUpdate()
  22.     Call CheckFilter
  23. End Sub
  24.  
  25. 'CheckFilter produces the new Filter depending on the values currently in
  26. 'txtTrack, txtSurface, txtDistance, txtTrkCond, txtRaceType and txtFinishPos.
  27. Private Sub CheckFilter()
  28.     Dim strFilter As String, strOldFilter As String
  29.  
  30.     strOldFilter = Me.Filter
  31.     'txtTrack - Text
  32.     If Me!txtTrack > "" Then _
  33.         strFilter = strFilter & _
  34.                     " AND ([Track] Like '" & _
  35.                     Me!txtTrack & "*')"
  36.     'txtSurface - Text
  37.     If Me!txtSurface > "" Then _
  38.         strFilter = strFilter & _
  39.                     " AND ([Surface] Like '" & _
  40.                     Me!txtSurface & "*')"
  41.     'txtDistance - Numeric
  42.     If Me!txtDistance > "" Then _
  43.         strFilter = strFilter & _
  44.                     " AND ([Distance]=" & _
  45.                     Me!txtDistance & ")"
  46.     'txtTrkCond - Text
  47.     If Me!txtTrkCond > "" Then _
  48.         strFilter = strFilter & _
  49.                     " AND ([Track_Condition]='" & _
  50.                     Me!txtTrkCond & "')"
  51.     'txtRaceType - Text
  52.     If Me!txtRaceType > "" Then _
  53.         strFilter = strFilter & _
  54.                     " AND ([Race_Type] Like '" & _
  55.                     Me!txtRaceType & "*')"
  56.     'txtFinishPos - Numeric
  57.     If Me!txtFinishPos > "" Then _
  58.         strFilter = strFilter & _
  59.                     " AND ([Final_Pos]=" & _
  60.                     Me!txtFinishPos & ")"
  61.     'Debug.Print ".Filter = '" & strOldFilter & "' - ";
  62.     'Debug.Print "strFilter = '" & strFilter & " '"
  63.     'Tidy up results and apply IF NECESSARY
  64.     If strFilter > "" Then strFilter = Mid(strFilter, 6)
  65.     If strFilter <> strOldFilter Then
  66.         Me.Filter = strFilter
  67.         Me.FilterOn = (strFilter > "")
  68.     End If
  69. End Sub
This gets me very close to where I was via the multitude of Form!ComboBox alternatives I had initially so in that regard, I thank you immensely. Hopefully, it'll be similar to what you were suggesting up above.

The remaining challenge is creating the reports off the new query/filter and adding in the user-selected operands (<,>,=,>=, <=) in regards to the FinishPos selection box.
Sep 30 '09 #20
NeoPa
32,556 Expert Mod 16PB
@HSXWillH
Excuse me while I just retype this to give the same results, but in a slightly more readable way for a forum :
Expand|Select|Wrap|Line Numbers
  1. SELECT   tRH.Date,
  2.          tRH.Track,
  3.          tRH.Race_No,
  4.          tRH.Distance,
  5.          tRH.Surface,
  6.          tRH.Track_Condition,
  7.          tRH.Race_Type,
  8.          tHL.Horse_Name,
  9.          IIf([Distance]<8,(1320-[1st_Len]*10)/[1st_Time],(2640-[1st_Len]*10)/[1st_Time]) AS 1FR,
  10.          tHL.[1st_Pos],
  11.          tHL.[1st_Len],
  12.          (1320+(([1st_Len]-[2nd_Len])*10))/([2nd_Time]-[1st_Time]) AS 2FR,
  13.          tHL.[2nd_Pos],
  14.          tHL.[2nd_Len],
  15.          ((([2nd_Len]-[Final_Len])*10)+IIf([Distance]<8,(([Distance]-4)*660),([Distance]-6)*660))/([Final_Time]-[2nd_Time]) AS 3FR,
  16.          tHL.Final_Pos,
  17.          tHL.Final_Len,
  18.          IIf([Distance]<8,([1FR]+[2FR]+[3FR])/3,([EP]+[SP])/2) AS AP,
  19.          IIf([Distance]<8,(2640-[2nd_Len]*10)/[2nd_Time],(3960-[2nd_Len]*10)/[2nd_Time]) AS EP,
  20.          ([EP]+[3FR])/2 AS SP,
  21.          IIf([Distance]<8,([1FR]+[3FR])/2) AS Fx,
  22.          [EP]/([EP]+[3FR]) AS [%E]
  23.  
  24. FROM     Race_Hdr AS tRH INNER JOIN
  25.          Horse_Lines AS tHL
  26.   ON     (tRH.Date=tHL.Date)
  27.  AND     (tRH.Track=tHL.Track)
  28.  AND     (tRH.Race_No=tHL.Race_No)
  29.  
  30. ORDER BY tRH.Date,
  31.          tRH.Track,
  32.          tRH.Race_No,
  33.          tHL.Final_Pos
This should be a lot easier to work with as a reference.
@HSXWillH
At first glance it seems to be a pretty decent first effort. I'll look into it more deeply later when I've had a chance to digest it all.
@HSXWillH
I expect so. Let's go there and see.
@HSXWillH
This bit is actually quite easy. It involves simply passing the string you created in the earlier step to the function call that opens the report for you. The report is designed to use the full query as specified above. This may be specified in its RecordSource property either as the direct SQL or a saved QueryDef that is comprised of that SQL.
Sep 30 '09 #21
NeoPa
32,556 Expert Mod 16PB
Moving on a bit now, you mention the names of all the relevant controls in your first post - All cbo... Your latest post however, indicates that you're working with txt... fields instead. I need to know what the current status is and what all the relevant controls are named. This needs to include whichever controls you use to allow the operator to specify the comparison operators.

Is there a comparison operator control for each comparison, or is it just the FinalPos that needs special treatment in this regard?
Sep 30 '09 #22
When you gave me the coding example in that extra link, the boxes were labeled txt...so I kept that naming convention going forward from that point even as the user inputs are combo-boxes. If necessary, I could change them, but I know what they are from the code.

I did not, on this new filter-form attempt, add any comparison operator yet. I wanted to make sure the basics were done and then add wrinkels after the foundation was working properly. The only box that will need special treatment is the FinalPos field.
Sep 30 '09 #23
NeoPa
32,556 Expert Mod 16PB
While I'm waiting (I have a job taking a long time to run at work and my progress depends on the results) I will suggest equivalents for some of your calculated fields in the SQL :
Expand|Select|Wrap|Line Numbers
  1. SELECT   ...
  2.          ((IIf([Distance]<8,1,2)*1320)-([1st_Len]*10))/[1st_Time] AS 1FR,
  3.          ...
  4.          ((([2nd_Len]-[Final_Len])*10)+(([Distance]-IIf([Distance]<8,4,6))*660)/([Final_Time]-[2nd_Time]) AS 3FR,
  5.          ...
  6.          ((1320*IIf([Distance]<8,2,3))-([2nd_Len]*10))/[2nd_Time] AS EP,
  7.          ...
  8.  
  9. FROM     ...
  10.  
  11. ORDER BY ...
PS. Look at your calculation for Fx. It doesn't make much sense.
Sep 30 '09 #24
NeoPa
32,556 Expert Mod 16PB
@HSXWillH
In the example there were TextBoxes and ComboBoxes used. [cboFindAccountType] was the ComboBox. I was trying to use as many different options as made practical sense, to give the reader examples that match their own requirement. If all your controls are ComboBoxes I suggest that it's important to name them cbo.
@HSXWillH
That clarifies things well. I will assume, unless you tell me otherwise, that the control used for this, when it is created, will be called cboComparison, and will contain the string values "<", "<=", "=", ">=" and ">". "=" will be the default I assume. All the other selection criteria will require an exact match (=).
Sep 30 '09 #25
Fx is one of those speed calcs that's very specialized only for sprint races (ie, races less than 8 furlongs). You take the first portion and the 3rd (closing portion) scores and average them. I could probably clean it up a bit though but the basic math is correct there.

And yes, the "<", "<=", "=", ">=" and ">". are the complete listing.

I also will change the txt to cbo's.
Sep 30 '09 #26
NeoPa
32,556 Expert Mod 16PB
The first bit of code you'll need to add (an Event Procedure) is for the new control :
Expand|Select|Wrap|Line Numbers
  1. Private Sub cboComparison_AfterUpdate()
  2.     Call CheckFilter
  3. End Sub
After this it is only necessary to look at the CheckFilter() subroutine and then provide some code (probably behind a CommandButton) to invoke the reports themselves.
Sep 30 '09 #27
NeoPa
32,556 Expert Mod 16PB
@HSXWillH
It's not the math I was worried about. It's the fact that no value is specified at all should the race not be a sprint. This will result in a value of FALSE I believe. If you're happy with that then all is good.
@HSXWillH
That's good :)
Sep 30 '09 #28
@NeoPa
Okay, what type of code will I provide in the CheckFilter() subroutine to apply the proper Comparison? Is this the Case detail that was discussed up top here?

Yesterday when I was fiddling around, I was able to code a combo-box listing of reports I have placed on the filter-form. Upon clicking the command button, the test report I had would run with the filtered data provided. That is below; any comments/feedback would be appreciated on that front.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Click()
  2.  
  3. Dim strDocName As String
  4.  
  5. Let strDocName = Me![cboReports]
  6.  
  7. DoCmd.OpenReport strDocName, acPreview, , IIf(Me.FilterOn = True, Me.Filter, "")
  8.  
  9. End Sub
Sep 30 '09 #29
NeoPa
32,556 Expert Mod 16PB
@HSXWillH
I will post some example code for you shortly (hopefully - or tomorrow if I find I can continue my proper work soon).
@HSXWillH
This isn't far off at all. We now (will shortly) have a function to return the exact filter string that we require.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Click()
  2.     Call DoCmd.OpenReport(ReportName:=Me.cboReports, _
  3.                           View:=acViewPreview, _
  4.                           WhereCondition:=GetFilter())
  5. End Sub
Sep 30 '09 #30
NeoPa
32,556 Expert Mod 16PB
@NeoPa
You haven't confirmed this, yet the code you posted was certainly not set up to use exact matches. Can you confirm whether an exact match or wildcard flexibility is required / preferred (or if they vary between controls then say which should be which)?
Sep 30 '09 #31
NeoPa
32,556 Expert Mod 16PB
Right, looking at this code in more detail I remember now that it applies the filter directly to the current form. This is not what we require in this instance. What we need is a function which will return the filter string pre-formulated for use in our call to open the report(s). I will make the necessary changes to illustrate what I mean.
Expand|Select|Wrap|Line Numbers
  1. 'CheckFilter produces the new Filter depending on the values currently in
  2. 'cboTrack, cboSurface, cboDistance, cboTrkCond, cboRaceType, cboComparison
  3. 'and cboFinishPos.
  4. Private Function GetFilter()
  5.     Dim strFilter As String
  6.  
  7.     strFilter = ""
  8.     With Me
  9.         'cboTrack - Text
  10.         If .cboTrack > "" Then _
  11.             strFilter = strFilter & _
  12.                         " AND ([Track] Like '" & _
  13.                         .cboTrack & "*')"
  14.         'cboSurface - Text
  15.         If .cboSurface > "" Then _
  16.             strFilter = strFilter & _
  17.                         " AND ([Surface] Like '" & _
  18.                         .cboSurface & "*')"
  19.         'cboDistance - Numeric
  20.         If .cboDistance > "" Then _
  21.             strFilter = strFilter & _
  22.                         " AND ([Distance]=" & _
  23.                         .cboDistance & ")"
  24.         'cboTrkCond - Text
  25.         If .cboTrkCond > "" Then _
  26.             strFilter = strFilter & _
  27.                         " AND ([Track_Condition]='" & _
  28.                         .cboTrkCond & "')"
  29.         'cboRaceType - Text
  30.         If .cboRaceType > "" Then _
  31.             strFilter = strFilter & _
  32.                         " AND ([Race_Type] Like '" & _
  33.                         .cboRaceType & "*')"
  34.         'cboFinishPos - Numeric
  35.         If .cboFinishPos > "" Then _
  36.             strFilter = strFilter & _
  37.                         " AND ([Final_Pos]" & _
  38.                         .cboComparison & _
  39.                         .cboFinishPos & ")"
  40.         'Tidy up results and apply IF NECESSARY
  41.         If strFilter > "" Then GetFilter = Mid(strFilter, 6)
  42.     End With
  43. End Function
NB. This means that all the AfterUpdate event procedures are no longer necessary. Entirely redundant.
Sep 30 '09 #32
@NeoPa
I prefer the wildcard flexibility in case the user gives input from off the combobox listing. In 99% of the cases, input will be directly from the combo lists, but in the off-chance they go askew, the wildcard flexibility would be beneficial.
Sep 30 '09 #33
@NeoPa

NeoPa, when I did this, it worked for my purposes. Filtering off the form still limits my data, and I can report on that filter as much as I need to. At this point, I think it'll meet my purposes about 99% of what I was hoping for upon asking the question. Plus, I'll have a few more tools in my skillset to use in the future.
Sep 30 '09 #34
NeoPa
32,556 Expert Mod 16PB
The only field being filtered that could be changed to use wildcards but isn't in the latest code is [Track_Condition]. This can easily be changed.

I didn't quite follow your last post. Are you saying you'd like the form to be filtered too when you change the criteria. This can be done easily too. I was under the impression this was not required. If it is let me know and we can fix that easily. It will require adding the AfterUpdate events back in.
Sep 30 '09 #35
My last post was realizing that even though I didn't set off on this particular path, that the codes have been working and I'm not really wanting to tie up any more of your time than necessary; esp. given your other responsibilities.

The last question I have is if I were to put into the form filter an option for Date and add an associated comparison combobox, called cboComparisons2...what code alteration would I need?

In the current code for the FinishPos comparison, I have:

Expand|Select|Wrap|Line Numbers
  1. 'cboFinishPos - Text
  2.     If Me!cboFinishPos > "" Then _
  3.             strFilter = strFilter & _
  4.                         " AND ([Final_Pos]" & _
  5.                         Me!cboComparison & _
  6.                         Me!cboFinishPos & ")"

If I add a cboRaceDate - box, based on that prior link, to search for an Equals would be:

Expand|Select|Wrap|Line Numbers
  1. 'cboRaceDate - Date
  2.     If Me!cboRaceDate > "" Then _
  3.         strFilter = strFilter & _
  4.             " AND ([Date] =" & _
  5.             Format(CDate(Me!cboRaceDate), "\#m/d/yyyy\#") & ")"
How would I alter this particular syntax to place the Me!cboComparison2 box as it is in the first working example?
Sep 30 '09 #36
NeoPa
32,556 Expert Mod 16PB
Replace your " =" at the end of line #4 with the reference to the ComboBox.
Expand|Select|Wrap|Line Numbers
  1. 'cboRaceDate - Date
  2.     If Me!cboRaceDate > "" Then _
  3.         strFilter = strFilter & _
  4.             " AND ([Date]" & _
  5.             Me.cboComparison2 & _
  6.             Format(CDate(Me!cboRaceDate), "\#m/d/yyyy\#") & ")"
You seem to pick this up quite well :)

PS. It seems you have avoided the use of the With Me construct. Not a very good idea, but ultimately your choice and what you're happiest with.
Sep 30 '09 #37
Thanks, this worked perfect.

The With Me construct, what is that? I'm so new to this stuff, is there any beginner's books/resources that you would suggest to teach myself the foundations of coding sql as you've walked me through in this?
Oct 1 '09 #38
NeoPa
32,556 Expert Mod 16PB
With is a way whereby you needn't keep repeating a reference to an object in code. Between the With & the End With, any code starting directly with a qualifier (. or !) automatically assumes the object value stated in the With statement. This is both notationally easier to read, as well as more effecient for the code to execute (as the reference is cached so needn't be resolved on each line). Type With into your VB Editor then press F1 for the help topic which explains it more fully.

For SQL help I find the Help system really quite useful. It's not as easy to find as it should be but Finding Jet SQL Help will help.
Oct 1 '09 #39
Thank you so much for your assistance on this; I've learned a lot so far and will continue working on it. Thanks.
Oct 2 '09 #40
NeoPa
32,556 Expert Mod 16PB
That's good to hear. Good luck with your project.
Oct 2 '09 #41

Sign in to post your reply or Sign up for a free account.

Similar topics

5
by: | last post by:
Is it possible to extend this function to dynamically create a combo boxes from the db table....How would do this...I tried passing a 'Select' type to the function but got a blank entry....Any...
3
by: Cristian Martinello | last post by:
How can I add <OPTION> to a tag <SELECT> runtime ? the code combo.innerHTML+="<OPTION value='1'>Name</OPTION>" doesn't work... -- Posted via Mailgate.ORG Server - http://www.Mailgate.ORG
3
by: gekoblu | last post by:
Hi!, I want to fix via javascript the combo width to a fix value. I'd like to implement a kind of ALT / TITLE function to show the entire option when the text is longer than the combo width......
1
by: [Miren] | last post by:
Hello i have one great problem with one form. i must to know, for submmiting the form, if the user, has change the select, has select one or no. in my select i have: <select name="maquina" >...
2
by: SAN CAZIANO | last post by:
check required in a select how can i control if a combobox (a select) is not null (index is -1) or if index is 0 if the first element is for example a null value or simply a description of the...
1
by: SAN CAZIANO | last post by:
selezionare il valore di una SELECT sapendo il testo che deve avere tra le sue opzioni <select name="combo"> <option>nome</option> <option>cognome</option> <option>prova</option>...
1
by: SAN CAZIANO | last post by:
how can clear an html SELECT and next insert in it all the elements of an array () I try this but seems doesn't works. function ComboAddArrayValueWithLabel(combo,ArrayLabel,ArrayValue) { ...
5
by: Raffi | last post by:
Hi folks, I'm new to JavaScript and need some help. I have a form with a select field. Depending on what is selected in this field, I want to display or not display another select field. For...
10
vikas251074
by: vikas251074 | last post by:
I am using following code to create list <tr> <td align="right">MIS No : </td> <td> <select name="mis_no" style="width:250px "> <% ...
7
by: afaheem | last post by:
I am selecting multiple items from a selectbox and on click of a button adding them dynamically to a <div>. I am adding it as children to the div ...something like checkbox + <selecteditem>. ...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.