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.
12 2042
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
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
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
-
-
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
-
-
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
-
-
-
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
to show you what the filter is set to before it is applied to the form.
-Stewart
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.
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
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.
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
Thats odd, Ill try again!
tested it, should work now...
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
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
Stewart,
That worked like a charm, thank you so much for taking the time.
Amazing.
Ian
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Mike Kanski |
last post by:
I have an asp page that contains a simple insert statement and redirect
i.e.:
Page1.asp
<%oconn.execute "insert into table 1 values(1)"
response.redirect "newpage.asp"
%>
I have to add some...
|
by: Emanuele Blanco |
last post by:
We all know that p->item is the most common syntax for (*p).item (or am I
wrong ? ^^). However, if p isn't a struct * but a struct **, what's the
correct way to choose a member of the...
|
by: flit |
last post by:
Hello All,
I am trying to get information from a form and send it to a python
script without success..
Here is my objective:
User enters data in form --form send variables to python script...
|
by: Colin J. Williams |
last post by:
One of the little irritants of Python is that the range syntax is rather
long-winded:
>>range(3, 20, 6)
>>>
It would be nice if one could have something like 3:20:6.
I've just come across...
|
by: -Karl |
last post by:
Couls someone please advise me on this error. What I am trying to do
is be able to convert an XML document into arrays. I read that the
subs & functions have to be in <scripttags.
Thanks!
...
|
by: Michal Bozon |
last post by:
many Python newcomers are confused why
range(10), does not include 10.
If there was a proposal for the new
syntax for ranges, which is known
e.g. from Pascal or Ruby...
....is there a...
|
by: Boris Borcic |
last post by:
Given the ABC innovation, maybe an infix syntax for isinstance() would be good.
Possibilities :
- stealing "is" away from object identity. As a motivation, true use cases for
testing object...
|
by: newbarker |
last post by:
I'm looking through the C++ standard and see statements like this:
yields: the smallest q in
[p,p+n) such that
X::eq(*q,c) is true, zero
otherwise.
I'm not from a scientific/math background...
|
by: dlite922 |
last post by:
I'm trying to convert an SQLITE database to MySQL syntax and it seems every resource I find on google is only half complete.
I want to create this thread to sum them all up in one central...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |