469,271 Members | 1,466 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,271 developers. It's quick & easy.

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

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

Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM shippingtable
  3. 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.
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub cmdFilter_Click()
  5. Dim strWhere As String                  'The criteria string.
  6.     Dim lngLen As Long
  7.  
  8.     If Not IsNull(Me.Job) Then
  9.         strWhere = strWhere & "([job] = " & Me.searcha >= Me.searchb & ")"
  10.     End If
  11.  
  12. End Sub
  13.  
  14. Private Sub cmdReset_Click()
  15.     'Purpose:   Clear all the search boxes in the Form Header, and show all records again.
  16.     Dim ctl As Control
  17.  
  18.     'Clear all the controls in the Form Header section.
  19.     For Each ctl In Me.Section(acHeader).Controls
  20.         Select Case ctl.ControlType
  21.         Case acTextBox, acComboBox
  22.             ctl.Value = Null
  23.         Case acCheckBox
  24.             ctl.Value = False
  25.         End Select
  26.     Next
  27.  
  28.     'Remove the form's filter.
  29.     Me.FilterOn = False
  30. End Sub
  31.  
  32. Private Sub Command152_Click()
  33.     'Purpose:   Clear all the search boxes in the Form Header, and show all records again.
  34.     Dim ctl As Control
  35.  
  36.     'Clear all the controls in the Form Header section.
  37.     For Each ctl In Me.Section(acHeader).Controls
  38.         Select Case ctl.ControlType
  39.         Case acTextBox, acComboBox
  40.             ctl.Value = Null
  41.         Case acCheckBox
  42.             ctl.Value = False
  43.         End Select
  44.     Next
  45.  
  46.     'Remove the form's filter.
  47.     Me.FilterOn = False
  48. End Sub
  49.  
  50.  
  51. Private Sub Form_BeforeInsert(Cancel As Integer)
  52.     'To avoid problems if the filter returns no records, we did not set its AllowAdditions to No.
  53.     'We prevent new records by cancelling the form's BeforeInsert event instead.
  54.     'The problems are explained at http://allenbrowne.com/bug-06.html
  55.     Cancel = True
  56.     MsgBox "You cannot add new clients to the search form.", vbInformation, "Permission denied."
  57. End Sub
  58.  
  59. Private Sub Form_Open(Cancel As Integer)
  60.     'Remove the single quote from these lines if you want to initially show no records.
  61.     Me.Filter = "(False)"
  62.     Me.FilterOn = True
  63. 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.
Attached Files
File Type: zip Drawing List.zip (158.2 KB, 70 views)
Sep 15 '10 #1
12 1813
Stewart Ross
2,545 Expert Mod 2GB
Hi Ian, and Welcome to Bytes!

The general form of a BETWEEN clause is

Expand|Select|Wrap|Line Numbers
  1. WHERE somefield BETWEEN firstvalue AND lastvalue
This is equivalent to

Expand|Select|Wrap|Line Numbers
  1. 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:

Expand|Select|Wrap|Line Numbers
  1. strWhere = "[job] BETWEEN " & Me.searcha  & " AND " &  Me.searchb 
  2. DoCMd.OpenForm "your job_3 form name", acFormDS,, strWhere
The alternative to BETWEEN would be:

Expand|Select|Wrap|Line Numbers
  1. strWhere = "[job] >= " & Me.searcha  & " AND [job] <= " &  Me.searchb 
  2. 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:

Expand|Select|Wrap|Line Numbers
  1. Me.Filter = "[job] BETWEEN " & Me.searcha  & " AND " &  Me.searchb 
  2. Me.FilterOn = True
Sep 15 '10 #2
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
Sep 16 '10 #3
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.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub cmdFilter_Click()
  5.     'Purpose:   Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
  6.     'Notes:     1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
  7.                         we remove the trailing " AND " at the end.
  8.     '           2. The date range works like this: _
  9.                         Both dates      = only dates between (both inclusive. _
  10.                         Start date only = all dates from this one onwards; _
  11.                         End date only   = all dates up to (and including this one).
  12.     Dim strWhere As String                  'The criteria string.
  13.     Dim lngLen As Long                      'Length of the criteria string to append to.
  14.  
  15.     '***********************************************************************
  16.     'Look at each search box, and build up the criteria string from the non-blank ones.
  17.     '***********************************************************************
  18.     'Text field example. Use quotes around the value in the string.
  19.     If Not IsNull(Me.filtercompany) Then
  20.         strWhere = strWhere & "([Company] = """ & Me.filtercompany & """) AND "
  21.     End If
  22.  
  23.     If Not IsNull(Me.filterangle) Then
  24.         strWhere = strWhere & "([Angle] = """ & Me.filterangle & """) AND "
  25.     End If
  26.  
  27.     If Not IsNull(Me.filterdwg) Then
  28.         strWhere = strWhere & "([Drawing] Like ""*" & Me.filterdwg & "*"") AND "
  29.     End If
  30.  
  31.     If Not IsNull(Me.searcha) Then
  32.         strWhere = strWhere & "([Job] >= " & Me.searcha & " AND [Job] <= " & Me.searchb & """) AND "
  33.     End If
  34.  
  35.     '***********************************************************************
  36.     'Chop off the trailing " AND ", and use the string as the form's Filter.
  37.     '***********************************************************************
  38.     'See if the string has more than 5 characters (a trailng " AND ") to remove.
  39.     lngLen = Len(strWhere) - 5
  40.     If lngLen <= 0 Then     'Nah: there was nothing in the string.
  41.         MsgBox "No criteria", vbInformation, "Nothing to do."
  42.     Else                    'Yep: there is something there, so remove the " AND " at the end.
  43.         strWhere = Left$(strWhere, lngLen)
  44.         'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
  45.         'Debug.Print strWhere
  46.  
  47.         'Finally, apply the string as the form's Filter.
  48.         Me.Filter = strWhere
  49.         Me.FilterOn = True
  50.     End If
  51. End Sub
  52.  
  53. Private Sub cmdReset_Click()
  54.     'Purpose:   Clear all the search boxes in the Form Header, and show all records again.
  55.     Dim ctl As Control
  56.  
  57.     'Clear all the controls in the Form Header section.
  58.     For Each ctl In Me.Section(acHeader).Controls
  59.         Select Case ctl.ControlType
  60.         Case acTextBox, acComboBox
  61.             ctl.Value = Null
  62.         Case acCheckBox
  63.             ctl.Value = False
  64.         End Select
  65.     Next
  66.  
  67.     'Remove the form's filter.
  68.     Me.FilterOn = False
  69. End Sub
  70.  
  71. Private Sub Command152_Click()
  72.     'Purpose:   Clear all the search boxes in the Form Header, and show all records again.
  73.     Dim ctl As Control
  74.  
  75.     'Clear all the controls in the Form Header section.
  76.     For Each ctl In Me.Section(acHeader).Controls
  77.         Select Case ctl.ControlType
  78.         Case acTextBox, acComboBox
  79.             ctl.Value = Null
  80.         Case acCheckBox
  81.             ctl.Value = False
  82.         End Select
  83.     Next
  84.  
  85.     'Remove the form's filter.
  86.     Me.FilterOn = False
  87. End Sub
  88.  
  89.  
  90. Private Sub Form_BeforeInsert(Cancel As Integer)
  91.     'To avoid problems if the filter returns no records, we did not set its AllowAdditions to No.
  92.     'We prevent new records by cancelling the form's BeforeInsert event instead.
  93.     'The problems are explained at http://allenbrowne.com/bug-06.html
  94.     Cancel = True
  95.     MsgBox "You cannot add new clients to the search form.", vbInformation, "Permission denied."
  96. End Sub
  97.  
  98. Private Sub Form_Open(Cancel As Integer)
  99.     'Remove the single quote from these lines if you want to initially show no records.
  100.     'Me.Filter = "(False)"
  101.     'Me.FilterOn = True
  102. End Sub
  103.  
  104.  
  105.  
Sep 16 '10 #4
Stewart Ross
2,545 Expert Mod 2GB
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):

Expand|Select|Wrap|Line Numbers
  1. ([Job] >= 1 AND [Job] <= 2") AND 
To correct this is straightforward. Line 32 should be

Expand|Select|Wrap|Line Numbers
  1. 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

Expand|Select|Wrap|Line Numbers
  1. msgbox strWhere
to show you what the filter is set to before it is applied to the form.

-Stewart
Sep 16 '10 #5
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.

Expand|Select|Wrap|Line Numbers
  1.    Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub cmdFilter_Click()
  5.     'Purpose:   Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
  6.     'Notes:     1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
  7.                         we remove the trailing " AND " at the end.
  8.     '           2. The date range works like this: _
  9.                         Both dates      = only dates between (both inclusive. _
  10.                         Start date only = all dates from this one onwards; _
  11.                         End date only   = all dates up to (and including this one).
  12.     Dim strWhere As String                  'The criteria string.
  13.     Dim lngLen As Long                      'Length of the criteria string to append to.
  14.  
  15.     '***********************************************************************
  16.     'Look at each search box, and build up the criteria string from the non-blank ones.
  17.     '***********************************************************************
  18.     'Text field example. Use quotes around the value in the string.
  19.     If Not IsNull(Me.filtercompany) Then
  20.         strWhere = strWhere & "([Company] = """ & Me.filtercompany & """) AND "
  21.     End If
  22.  
  23.     If Not IsNull(Me.filterangle) Then
  24.         strWhere = strWhere & "([Angle] = """ & Me.filterangle & """) AND "
  25.     End If
  26.  
  27.     If Not IsNull(Me.filterdwg) Then
  28.         strWhere = strWhere & "([Drawing] Like ""*" & Me.filterdwg & "*"") AND "
  29.     End If
  30.  
  31.     If Not IsNull(Me.searcha) Then
  32.         strWhere = strWhere & "([Job] >= " & Me.searcha & " AND [Job] <= " & Me.searchb & ") AND "
  33.     End If
  34.  
  35.     '***********************************************************************
  36.     'Chop off the trailing " AND ", and use the string as the form's Filter.
  37.     '***********************************************************************
  38.     'See if the string has more than 5 characters (a trailng " AND ") to remove.
  39.     lngLen = Len(strWhere) - 5
  40.     If lngLen <= 0 Then     'Nah: there was nothing in the string.
  41.         MsgBox "No criteria", vbInformation, "Nothing to do."
  42.     Else                    'Yep: there is something there, so remove the " AND " at the end.
  43.         strWhere = Left$(strWhere, lngLen)
  44.         'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
  45.         'Debug.Print strWhere
  46.  
  47.         'Finally, apply the string as the form's Filter.
  48.         Me.Filter = strWhere
  49.         Me.FilterOn = True
  50.     End If
  51. 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.
Sep 16 '10 #6
Stewart Ross
2,545 Expert Mod 2GB
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
Sep 16 '10 #7
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@allenbrowne.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.
Attached Files
File Type: zip Drawing List_test.zip (194.9 KB, 59 views)
Sep 16 '10 #8
Stewart Ross
2,545 Expert Mod 2GB
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
Sep 16 '10 #9
Thats odd, Ill try again!

tested it, should work now...
Attached Files
File Type: zip Drawing List_test.zip (194.7 KB, 57 views)
Sep 16 '10 #10
Stewart Ross
2,545 Expert Mod 2GB
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
Sep 16 '10 #11
Stewart Ross
2,545 Expert Mod 2GB
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

Expand|Select|Wrap|Line Numbers
  1. strWhere = strWhere & "([Job] >= '" & Me.searcha & "' AND [Job] <= '" & Me.searchb & "') AND "
-Stewart
Sep 16 '10 #12
Stewart,

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

Amazing.

Ian
Sep 16 '10 #13

Post your reply

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

Similar topics

2 posts views Thread by Mike Kanski | last post: by
8 posts views Thread by Emanuele Blanco | last post: by
2 posts views Thread by Colin J. Williams | last post: by
4 posts views Thread by Michal Bozon | last post: by
5 posts views Thread by Boris Borcic | last post: by
6 posts views Thread by newbarker | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.