Connecting Multiple Option Buttons to One Text Box

[Guirg]

Hey all,
Almost there !!! Im having trouble setting multiple option buttons to one text box: heres a simplified version of my code so far, but it all has the same subroutines and all that stuff.... whats happening is its not filtering... not sure what to do....

Private Sub btnSearch_Click()
    Me.frmWork.Form.RecordSource = "SELECT * FROM qryWork " & BuildFilter
    Me.frmWork.Requery
End Sub
 
Private Sub Form_Load()
  ' Clear the search form
    btnClear_Click
End Sub
 
Private Function BuildFilter() As Variant
    Dim varWhere As Variant
    Dim varItem As Variant
    Dim intIndex As Integer
    
    varWhere = Null  ' Main filter
  ' Check for min date
    If Me.txtdatemin > "" Then
        varWhere = varWhere & "[Date] >= " & Me.txtdatemin & " AND "
    End If
  ' Check for max date
    If Me.txtdatemax > "" Then
        varWhere = varWhere & "[Date] <= " & Me.txtdatemax & " AND "
    End If
    
  ' Check if there is a filter to return...
    If IsNull(varWhere) Then
        varWhere = ""
    Else
        varWhere = "WHERE " & varWhere
        
      ' strip off last "AND" in the filter
        If Right(varWhere, 5) = " AND " Then
            varWhere = Left(varWhere, Len(varWhere) - 5)
        End If
    End If
    
    BuildFilter = varWhere + varWhere1
    
End Function

Private Sub opt1_Click()
    If opt1 = -1 Then
    opt2 = 0
    opt3 = 0 
    
Dim varWhere1 As Variant
    varWhere1 = Null  ' Main filter
    varWhere1 = varWhere1 & "[Col1] >= " & Me.txtMin & " AND "
    varWhere1 = varWhere1 & "[Col1] <= " & Me.txtMax & " AND "
    BuildFilter = varWhere1
    
    End If
End Sub

 

[DCrake]

You need to remember the golden rule of building criteria clauses.

Numeric data does not need quoting
Text data needs quoting
Dates need hashes

In your criteria you are not encompasssing your dates with #'s to let Access know it is a date.

Also the use of the word Date as a field name is one of the biggest no no's going. Using Access Function/Command names as actual field names will do nothing but confuse the issue. Rename as soon as possible.

David

 

[Guirg]

sorry little confused do you mean it should look like that?? because im getting a mismatched error..

If Me.txtdatemin > "" Then
varWhere = varWhere & "[Date] >= & #Me.txtdatemin# & " And ""
End If

The date search box works if the user inputs the # before and after manually with / separators... its the option buttons that dont kick in properly... the singular option works a treat but the varWhere filter doesnt work

 

[DCrake]

You should not rely on the user to enter the mask for you. The user wants to enter a little as possible. you should have an input mask on your text box and your code should include the #'s

... [DateField] =#" & Me.TxtField & "#...


David

 

[Guirg]

hey hey we have success!! cheers for that!! still stuck on the option part tho

 

[DCrake]

So what part of the option section is not working?

 

[Guirg]

Ill make a selection on the option buttons and input the max and min ranges into the text boxs, run the search, which runs then switches over to the "Private Function BuildFilter() As Variant" and nothing'll happen. Ive tried having a second variant and add it to the BuildFilter but still nothing... What im trying to do is when the option button is selected it automatically applies on a filter from the text box so when i hit search all the buildfilter subroutine does is search for input into the datefield textbox and compiles the other filter i have (which is identical to the one im trying to impliment now... so hasnt been built) ...

Not sure if this makes sense... what i have is 28 labels, and 2 option buttons for each and a max, min date function. for each column of option buttons i want to connect 2 text boxes... so in the end i have 6 text boxes(including date filter), and 56 option buttons. What the user does is select one option button and then goes to the text box and inputs max and min values, a date range, or two option buttons and then hits search which will filter according to what he wants to do

 

[DCrake]

Can you provide a screen dump of your form so I can see what you are attempting to describe? Or better still send a sample mdb to look at.

David

 

[Guirg]

Ok i hope this helps, had to remove some stuff but ive explained what was there... Thanks again for this no idea whered id be without ur help!!!

 

[DCrake]

As a matter of interest have you tried using Between x AND y for your min and max options.

 

[Guirg]

Ohhh no i have because i know the user will say i want to see all values above x rather than between x and y for some of the data

 

[DCrake]

You would only apply the Between x And y if both values have been populated on the form.

What I suggerst you do is to put a breakpoint on your code and see what it is actually doing, or more importantly not doing. What is being returned by your buildfilter

David

 

[Guirg]

Ohh would an if then else statement on a new filter work? as in i have:
If opt1 = -1 then
varWhere1 = varWhere1 & "[Col1] >= " & Me.txtMin & " AND "
varWhere1 = varWhere1 & "[Col1] <= " & Me.txtMax & " AND "
Elseif opt2 = -1 then
varWhere1 = varWhere1 & "[Col2] >= " & Me.txtMin & " AND "
varWhere1 = varWhere1 & "[Col2] <= " & Me.txtMax & " AND "

Would that work? then then with the search button have:
Private Sub btnSearch_Click()
Me.frmWork.Form.RecordSource = "SELECT * FROM qryWork " & BuildFilter & BuildFIlter1
Me.frmWork.Requery
End Sub

 

[DCrake]

I think the clouds are finally beginning to disperse. From what I gather all these option boxes relate to different fields in your underlying query and based upon which option box is ticked denotes which field you want to apply the min amd max to. Am I correct?

If so, How many option groups have you? and what are the labels for the options?
By hiding the label captions in your screen shot does not help to distinguish your needs.

David

 

[Guirg]

Bingo ahahah how did it take u only one line to describe!!! ive got two option groups and one date field so 3 filters in all... and i think i just got it working!! ohh damn that a massive load off!!! Thanks for everything!!!

 

[DCrake]

Glad to see you have found the light at the end of the tunnel In future remember not to forget where it is