How to use the >= range syntax for a VBA script.

[Ian Anderson]

Hello there everyone, thank you in advance for any help you may have to offer here. I have a seemingly simple request to help me finish off this db for a customer.

I have looked everywhere high and low for a sample that allows a user to input two integers in two different input boxes, and have a vb script filter a query based on those two fields.

I am very new to Access, and am having a hell of a time trying to make what SQl does in its sleep

SELECT *
FROM shippingtable
WHERE zip_code between search_a and search_b

But I would really really appreciate some help form the community on this.

I willa ttach below my feeble attempt at getting this to work using the >= expression but I just cant seem to figure it out.

Option Compare Database
Option Explicit

Private Sub cmdFilter_Click()
Dim strWhere As String                  'The criteria string.
    Dim lngLen As Long
       
    If Not IsNull(Me.Job) Then
        strWhere = strWhere & "([job] = " & Me.searcha >= Me.searchb & ")"
    End If
    
End Sub

Private Sub cmdReset_Click()
    'Purpose:   Clear all the search boxes in the Form Header, and show all records again.
    Dim ctl As Control
    
    'Clear all the controls in the Form Header section.
    For Each ctl In Me.Section(acHeader).Controls
        Select Case ctl.ControlType
        Case acTextBox, acComboBox
            ctl.Value = Null
        Case acCheckBox
            ctl.Value = False
        End Select
    Next
    
    'Remove the form's filter.
    Me.FilterOn = False
End Sub

Private Sub Command152_Click()
    'Purpose:   Clear all the search boxes in the Form Header, and show all records again.
    Dim ctl As Control
    
    'Clear all the controls in the Form Header section.
    For Each ctl In Me.Section(acHeader).Controls
        Select Case ctl.ControlType
        Case acTextBox, acComboBox
            ctl.Value = Null
        Case acCheckBox
            ctl.Value = False
        End Select
    Next
    
    'Remove the form's filter.
    Me.FilterOn = False
End Sub


Private Sub Form_BeforeInsert(Cancel As Integer)
    'To avoid problems if the filter returns no records, we did not set its AllowAdditions to No.
    'We prevent new records by cancelling the form's BeforeInsert event instead.
    'The problems are explained at http://allenbrowne.com/bug-06.html
    Cancel = True
    MsgBox "You cannot add new clients to the search form.", vbInformation, "Permission denied."
End Sub

Private Sub Form_Open(Cancel As Integer)
    'Remove the single quote from these lines if you want to initially show no records.
    Me.Filter = "(False)"
    Me.FilterOn = True
End Sub

To clarify,

I have built a small DB for them to enter various job related information. One of the tables is called Job. I would like to have

search_a (text box in my form) &
search_b (another text box)

create a result of all records equal to or less than search_b.

All job numbers are a 5 digit number with no letters to confuse the issue. The query is called job_3, which pulled all the numbers in the Job column from the main table.

I am again VERY new to access and am grateful for any help.

[Stewart Ross]

Hi Ian, and Welcome to Bytes!

The general form of a BETWEEN clause is

WHERE somefield BETWEEN firstvalue AND lastvalue

This is equivalent to

WHERE somefield >= firstvalue AND somefield <= Lastvalue

You mention that you want to filter a query. Filtering a form would be simple; filtering a query is not so simple, at least not from VBA code. To avoid getting into the creation of temporary QueryDef objects for your filtered query I'd suggest that you use the wizards to create a form in datasheet or continuous form view bound to your query job_3, and that you filter the form when you open it. It is this application I show below, with datasheet view assumed.

The BETWEEN operator can indeed be used in your WHERE clause, but note that it is the value of the textboxes which are placed in the where string, not references to their names:

strWhere = "[job] BETWEEN " & Me.searcha  & " AND " &  Me.searchb 
DoCMd.OpenForm "your job_3 form name", acFormDS,, strWhere

The alternative to BETWEEN would be:

strWhere = "[job] >= " & Me.searcha  & " AND [job] <= " &  Me.searchb 
DoCMd.OpenForm "your job_3 form name", acFormDS,, strWhere

I hope you find this of assistance.

-Stewart

PS If the form on which your two textboxes is placed is bound to query job_3 you could filter it directly without devising another form:

Me.Filter = "[job] BETWEEN " & Me.searcha  & " AND " &  Me.searchb 
Me.FilterOn = True

[Ian Anderson]

Thank you so much Stuart. I'll take your advice here, and see what i can implement. I would rather filter my form, as that's how i handled the search page, I created for individual search items.

Again i really appreciate your time with this. =D

[Ian Anderson]

Hi again,

Stuart, or anyone familiar with this, again i ask for your help.

I have amalgamated the search boxes "searcha" and "searchb" into a form called frm_search. The form already has 3 working filters that apply to the database, Company, Angle, and a text field that searches the user input for results.

I put at the end of that section of script, the fields you suggested in the same format that was used previously, but it isn't working properly.

I have tried to manually filter my form using two methods of filtering, either with the BETWEEN command, or the ">= AND <= "

Both seem to return an error, as though the filter syntax isn't correct.

Once debugged, it doesn't seem to input the fields correctly... but thats just me guessing.

Is there any clues in this code? I have BOLD the items that i entered in an already working search form, so its clear what was changed.

Option Compare Database
Option Explicit

Private Sub cmdFilter_Click()
    'Purpose:   Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
    'Notes:     1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
                        we remove the trailing " AND " at the end.
    '           2. The date range works like this: _
                        Both dates      = only dates between (both inclusive. _
                        Start date only = all dates from this one onwards; _
                        End date only   = all dates up to (and including this one).
    Dim strWhere As String                  'The criteria string.
    Dim lngLen As Long                      'Length of the criteria string to append to.
    
    '***********************************************************************
    'Look at each search box, and build up the criteria string from the non-blank ones.
    '***********************************************************************
    'Text field example. Use quotes around the value in the string.
    If Not IsNull(Me.filtercompany) Then
        strWhere = strWhere & "([Company] = """ & Me.filtercompany & """) AND "
    End If
    
    If Not IsNull(Me.filterangle) Then
        strWhere = strWhere & "([Angle] = """ & Me.filterangle & """) AND "
    End If
    
    If Not IsNull(Me.filterdwg) Then
        strWhere = strWhere & "([Drawing] Like ""*" & Me.filterdwg & "*"") AND "
    End If
    
    [B]If Not IsNull(Me.searcha) Then
        strWhere = strWhere & "([Job] >= " & Me.searcha & " AND [Job] <= " & Me.searchb & """) AND "
    End If[/B]
    
    '***********************************************************************
    'Chop off the trailing " AND ", and use the string as the form's Filter.
    '***********************************************************************
    'See if the string has more than 5 characters (a trailng " AND ") to remove.
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then     'Nah: there was nothing in the string.
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strWhere = Left$(strWhere, lngLen)
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        'Debug.Print strWhere
        
        'Finally, apply the string as the form's Filter.
        [B]Me.Filter = strWhere
        Me.FilterOn = Tru[/B]e
    End If
End Sub

Private Sub cmdReset_Click()
    'Purpose:   Clear all the search boxes in the Form Header, and show all records again.
    Dim ctl As Control
    
    'Clear all the controls in the Form Header section.
    For Each ctl In Me.Section(acHeader).Controls
        Select Case ctl.ControlType
        Case acTextBox, acComboBox
            ctl.Value = Null
        Case acCheckBox
            ctl.Value = False
        End Select
    Next
    
    'Remove the form's filter.
    Me.FilterOn = False
End Sub

Private Sub Command152_Click()
    'Purpose:   Clear all the search boxes in the Form Header, and show all records again.
    Dim ctl As Control
    
    'Clear all the controls in the Form Header section.
    For Each ctl In Me.Section(acHeader).Controls
        Select Case ctl.ControlType
        Case acTextBox, acComboBox
            ctl.Value = Null
        Case acCheckBox
            ctl.Value = False
        End Select
    Next
    
    'Remove the form's filter.
    Me.FilterOn = False
End Sub


Private Sub Form_BeforeInsert(Cancel As Integer)
    'To avoid problems if the filter returns no records, we did not set its AllowAdditions to No.
    'We prevent new records by cancelling the form's BeforeInsert event instead.
    'The problems are explained at http://allenbrowne.com/bug-06.html
    Cancel = True
    MsgBox "You cannot add new clients to the search form.", vbInformation, "Permission denied."
End Sub

Private Sub Form_Open(Cancel As Integer)
    'Remove the single quote from these lines if you want to initially show no records.
    'Me.Filter = "(False)"
    'Me.FilterOn = True
End Sub

[Stewart Ross]

Hi Ian. Allen Browne's code is fine, but in adapting it you can sometimes be misled about how to apply the lines within it. You have too many double-quotes in the comparison line you've added for the [Job] >= ... part - what you've added on the right with three quote marks would work for a text string value but not a numeric one. That in itself will be a syntax error in the editor, as the line you have added at line 32 comes down to this (with 1 and 2 substituted for me.searcha and me.searchb):

([Job] >= 1 AND [Job] <= 2") AND

To correct this is straightforward . Line 32 should be

strWhere = strWhere & "([Job] >= " & Me.searcha & " AND [Job] <= " & Me.searchb & ") AND "

I am assuming from what you told us in post 1 that your job number is a number and not a string, which would have to be enclosed in single or double quotes - which is why the Allen Browne line you've half copied contains an extra right-hand double quote. It was the end-quote for a string value, but my line is based on a numeric value so it has no opening quote to match with the end one.

If you still have problems with the where clause after you clear syntax errors you should look at what the where string contains to see if it is properly formed. At line 47 you could add

msgbox strWhere

to show you what the filter is set to before it is applied to the form.

-Stewart

[Ian Anderson]

Thanks again Stewart!,

That seemed to work, only now it seems to have an issue with code later on. I can see that it actually enters

"([Job] >= 0 AND [Job] <= 30318)" properly into my filter, but it doesn't actually pass the debug, nor apply the filter.

   Option Compare Database
Option Explicit

Private Sub cmdFilter_Click()
    'Purpose:   Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
    'Notes:     1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
                        we remove the trailing " AND " at the end.
    '           2. The date range works like this: _
                        Both dates      = only dates between (both inclusive. _
                        Start date only = all dates from this one onwards; _
                        End date only   = all dates up to (and including this one).
    Dim strWhere As String                  'The criteria string.
    Dim lngLen As Long                      'Length of the criteria string to append to.
    
    '***********************************************************************
    'Look at each search box, and build up the criteria string from the non-blank ones.
    '***********************************************************************
    'Text field example. Use quotes around the value in the string.
    If Not IsNull(Me.filtercompany) Then
        strWhere = strWhere & "([Company] = """ & Me.filtercompany & """) AND "
    End If
    
    If Not IsNull(Me.filterangle) Then
        strWhere = strWhere & "([Angle] = """ & Me.filterangle & """) AND "
    End If
    
    If Not IsNull(Me.filterdwg) Then
        strWhere = strWhere & "([Drawing] Like ""*" & Me.filterdwg & "*"") AND "
    End If
    
    If Not IsNull(Me.searcha) Then
        strWhere = strWhere & "([Job] >= " & Me.searcha & " AND [Job] <= " & Me.searchb & ") AND "
    End If
    
    '***********************************************************************
    'Chop off the trailing " AND ", and use the string as the form's Filter.
    '***********************************************************************
    'See if the string has more than 5 characters (a trailng " AND ") to remove.
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then     'Nah: there was nothing in the string.
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strWhere = Left$(strWhere, lngLen)
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        'Debug.Print strWhere
        
        'Finally, apply the string as the form's Filter.
        Me.Filter = strWhere
        Me.FilterOn = True
    End If
End Sub

This returns in VBA debug

"Data type mismatch in the criteria expression" Occurs even manually applying the filter in design view and viewing the form in form view.


LINE 49 Me.FilterOn = True




Being the next debug problem. Am I doing something wrong again? My Numbers i'm searching are 5 digits and yes only numerical.

Thanks so much Stewart.

[Stewart Ross]

Hi Ian. Most obvious questions are again about text vs numeric values.

Is your filterangle a number? In line 24 it is being treated as a string.

Is your filterdwg field value numeric? You are not just treating it as a string in line 28, you are using partial matching with the Like operator and wildcards on either side.

In this context type mismatches mean the query engine is expecting a value of one type (string, say) and finding a different type in the actual expression that it cannot reconcile at run-time - such as a numeric that cannot be used with 'Like', for instance.

It would help to know the exact line that the compiler is flagging the error upon, and to see what the actual filter string looks like at that point - hence the suggestion to use the msgbox function to pop up its value for you to copy down before it is applied.

-Stewart

[Ian Anderson]

Hi again,

I have attached the DB in the zip associated with my response here.

My form had those initial search options, that searched 3 different fields in my table.

They all worked, it seemed, and the type and style of search was tailored for each function. Please feel free to tinker around in my db to get a feel for what the script actually does, as it's been a series of

Change this - break it... fix it, change this... ahh that's the result i want, kind of design. The initial code was in fact Allen Browne (allen@allenbro wne.com), June 2006. but it never actually took into account, that someone may wish to search a range of data within your table.

So I utilized the code for the individual filters, which worked great with combo boxes, and a text box (with the LIKE modifier) each filtering their perspective items.

The last was to have a range, which in SQL seems to be a lot easier than access's dependency on VBA, but i know a lot of my confusion is my ignorance with Access.

Again if you could take a look at the actauly database, and get a feel for the frm_search, and how it works, it may become clearer what i'm intending to do.

Thanks again Stewart.


PS. Angle is a pull down with the number, dwg is a format like rd-078-0923-pl-0-1 kinda thing, and company is a pull down with the various company's already in the db.

[Stewart Ross]

Sorry, Ian, but when I try to open the accdb file I'm getting an unrecognised database format error (from Access 2007). Any chance of re-zipping a new copy?

-Stewart

[Ian Anderson]

Thats odd, Ill try again!

tested it, should work now...

[Stewart Ross]

Still no joy, Ian. Even tried compact/repair but didn't get to first base with it.

If you want to send me a private message with your e-mail address I'll be happy to send my home e-mail address to you so you can attach an unzipped copy of the DB if you wish.

-Stewart

[Stewart Ross]

Hi Ian. Many thanks for persisting through several versions until the A2003 one which worked.

The problem is that the Job Number is not a number, but five-digit text that looks like a number. String literal values must be enclosed in quotes to be interpreted correctly, and this is easily achieved in line 32 by changing it to

strWhere = strWhere & "([Job] >= '" & Me.searcha & "' AND [Job] <= '" & Me.searchb & "') AND "

-Stewart

[Ian Anderson]

Stewart,

That worked like a charm, thank you so much for taking the time.

Amazing.

Ian