This problem is driving me crazy.
Hello there,
i am trying to create a search form for records in my access database. The search form will contain text boxes and a multi select list box. The user can enter their search criteria eg. surname, reg num, etc. in the text boxes. The multi select list box allows the user to select multiple counties which they have the option of including in the search. The user should be able to select or omit the criteria as they desire.
I have messed around with the code for the last days but i am unable to intergrate the user selected criteria from a multi select list box.
I have been able to fix to the basics like first name and surname search but i cant manage the user selection from the multi select list box of counties.
This in my attempt it's bad i know. - If Len(Me.List0 & vbNullString) Then
-
For Each Itm In ctl.ItemsSelected
-
strSQLWhere = "WHERE [county] Chr(34) & ctl.ItemData(Itm) & Chr(34)
-
Else
-
strSQLWhere = "WHERE [county] = " & Chr$(39) & Me.List0 & Chr$(39)
-
End If
-
-
strSQLWhere = strSQLWhere & strJoin
-
End If
Listbox info
The list box is a selection of counties its called list0 its a look up values in my counties table.
Name = List0
Row Source = SELECT [tblCounties].[CountyCode], [tblCounties].[County] FROM tblCounties;
Multi select = simple
I really like the subform the results are displayed in do you think i can make it open in a report format also?
Below is my code minus the failed list box intergration attempt to avoid confusion.
Many thaks once again all the way from Ireland. Kind regards
James - Private Sub cmdPlease_Click()
-
Dim strSQLHead As String
-
Dim strSQLWhere As String
-
Dim strSQLOrderBy As String
-
Dim strSQL As String
-
Dim strJoin As String
-
-
-
strJoin = " AND "
-
strSQLHead = "SELECT * FROM tblMemberDetails "
-
-
If Len(Me.txtSurname & vbNullString) Then
-
If (Me.chkLike) Then
-
strSQLWhere = "WHERE [surname] Like " & Chr$(39) & "*" & Me.txtSurname & "*" & Chr$(39)
-
Else
-
strSQLWhere = "WHERE [surname] = " & Chr$(39) & Me.txtSurname & Chr$(39)
-
End If
-
-
strSQLWhere = strSQLWhere & strJoin
-
End If
-
-
If Len(Me.txtFirstName & vbNullString) Then
-
If (Me.chkLike) Then
-
strSQLWhere = "WHERE [FirstName] Like " & Chr$(39) & "*" & Me.txtFirstName & "*" & Chr$(39)
-
Else
-
strSQLWhere = "WHERE [FirstName] = " & Chr$(39) & Me.txtFirstName & Chr$(39)
-
End If
-
-
strSQLWhere = strSQLWhere & strJoin
-
End If
-
-
If Len(strSQLWhere) Then
-
strSQLWhere = Left$(strSQLWhere, Len(strSQLWhere) - (Len(strJoin) - 1))
-
End If
-
-
strSQLOrderBy = "ORDER BY "
-
Select Case Me.fraOrderBy
-
Case 1
-
strSQLOrderBy = strSQLOrderBy & "[surname]"
-
Case 2
-
strSQLOrderBy = strSQLOrderBy & "[firstName]"
-
Case 3
-
strSQLOrderBy = strSQLOrderBy & "[regNumber]"
-
End Select
-
-
strSQL = strSQLHead & strSQLWhere & strSQLOrderBy
11 5560 - You can build a partial SQL Where Clause based on a Multi-Select List Box with the following code:
- Dim strSQL As String
-
Dim varItem As Variant
-
Dim ctlList As Control
-
Dim strSQLPre As String
-
-
Set ctlList = Me![lstCountries]
-
-
strSQLPre = "Where [Country] In ("
-
-
Select Case ctlList.ItemsSelected.Count
-
Case 0
-
Exit Sub
-
Case Else
-
For Each varItem In ctlList.ItemsSelected
-
strSQL = strSQL & "'" & ctlList.ItemData(varItem) & "',"
-
Next varItem
-
End Select
-
-
strSQL = strSQLPre & Left$(strSQL, Len(strSQL) - 1) & ")"
- If you selected 4 Countries, the Partial Clause would read:
- Where [Country] In ('France','England','Italy','Ireland')
- There are much better SQL Experts/Moderators/Members than I, I'm sure a better approach will be forthcoming.
listbox ItemData is a two column listbox, with the county numbers in column 0 (probably hidden) and the names in column 1. Decision 1 is to decide whether search is by county name, or county number. Which is in tblMemberDetails, the name or the number?
In the query design grid, design your query as if it were always the same, and forget the names and region numbers. Just the counties. Use the "Create query in Design View" of the Database window. Select tblMemberDetails from the Show Table dialog, and close. Drag the * to column 1. Drag the County to column 2. Use name or number as appropriate, I am going to assume name for now. Column 2, of Criteria row, enter a counties you know exists, say "Limerick" and below it, enter another, say "Tipperary". Press the View button on the Query Design Toolbar (far left button). Do you get the results expected? If no, then you need to find out why, but it should work if you have these counties in the table. If not, pick 2 you know exist.
It works, so click the downarrow next to the View button, and select SQL. This is the SQL statement Access generates. It won't use the IN keyword as suggested by ADezii, but you can do that later.
Put the code from the query window into your code, assigning this string to strSQLHead replacing the loops in lines 6 to 17. Then include your strSQLHead so you have - strSQL = strSQLHead * strSQLWhere
Forget for now the ORDER BY and the other conditions. This should work, and you should understand it.
When it works, add the ORDER BY and other WHERE conditions. Continue to refer to your query design window, adding your conditions so you can see an example of an SQL statement that does what you want.
Be aware that your listbox, ItemsSelected, is 2 columns, so you will have to tell access which column to use. ADezii's line 15 should be - strSQL = strSQL & "'" & ctlList.ItemData.column(0,varItem) & "',"
I am still assuming you are using the county name, not number.
Your name search, Like "*surname*" will find occurrances of imbedded names within the name. If you're looking for Oliver Stone, and enter "Sto" in your surname textbox, you will also find Pete Johnston. Is that what you want? - If Len(Me.txtSurname & vbNullString) Then
is always true when I run it. Does this statement really do what you want? Maybe - If Len(Me.txtSurname) <> 0 then
-
'Add to your SQL statement
-
Else
-
'Do nothing
-
End If
- strSQL = strSQLHead * strSQLWhere
Should be - strSQL = strSQLHead & strSQLWhere
~~~and~~~ - strSQL = strSQL & "'" & ctlList.ItemData.column(0,varItem) & "',"
Should be - strSQL = strSQL & "'" & ctlList.ItemData.column(1,varItem) & "',"
Major Thanks!!!
ADezii & OldBirdman
Your information really helped me understand what was going on. This problem was driving me nuts. Thank you so much for your time and knowledge.
I managed to create a Search form to meet my specification exactly but i have one error left to sort out.
The search feature works great it updates the subform of search results perfectly for all criteria. However i have added a option to printPreview a report based on the selected criteria which worked at first, my latest problem came to light after I added a Order By clause to my function which allows me to order results in the search results subform by Surname,FirstName and reg number.
The subform results part works fine i can order by Surname etc.
My Problem is if i hit the button to preview the report it get an error message
Run Time error 3075
Syntax error (missing operator) in a query expression '''ORDER BY [regNumber]',
My code is below any advice would be hugely apperiacted.
Many Thanks!
Have a Nice weekend
JAMES
Enter the following Line of code between Lines 9 and 10 above, but be sure to REM it out or remove it later. Then, Copy-N-Paste the results here for us to View: - 'After Line# 9 but Before Line# 10:
-
Debug.Print BuildFilter & strSQLOrderBy :Exit Sub
Hello these are my results.
Kind regards and thanks so much!
Someone mentioned to me that you can't add an order by clause to the wherecondition argument.
Is that true? if so whats a easy way to add grouping options ot my report and keep the ordering in my subform.
JAMES - '' ORDER BY [surname]
-
'' ORDER BY [surname]
-
'' ORDER BY [surname]
-
'' ORDER BY [surname]
-
'' ORDER BY [surname]
-
'' ORDER BY [surname]
-
'' ORDER BY [surname]
-
'' ORDER BY [surname]
-
'' ORDER BY [surname]
-
'' ORDER BY [surname]
-
'' ORDER BY [firstName]
-
'' ORDER BY [firstName]
-
'' ORDER BY [firstName]
-
'' ORDER BY [firstName]
-
'' ORDER BY [firstName]
-
'' ORDER BY [firstName]
-
'' ORDER BY [firstName]
-
'' ORDER BY [firstName]
-
'' ORDER BY [firstName]
-
'' ORDER BY [firstName]
-
'' ORDER BY [firstName]
-
'' ORDER BY [surname]
-
'' ORDER BY [surname]
-
'' ORDER BY [surname]
-
'' ORDER BY [surname]
-
'' ORDER BY [surname]
-
'' ORDER BY [surname]
-
'' ORDER BY [surname]
-
'' ORDER BY [surname]
-
'' OR( [tblMemberDetails_CountyCode] = "c" ) ORDER BY [surname]
-
DER BY [surname]
-
hello again and thanks so much for all your time.
My friend tole me i can't add an order by clause to the wherecondition argument. is that true? if so is there an easy way for me to keep the order by option to my subform and add some degree of user selected ordering to my report.
Kind Regards James
'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [firstName]
'' ORDER BY [firstName]
'' ORDER BY [firstName]
'' ORDER BY [firstName]
'' ORDER BY [firstName]
'' ORDER BY [firstName]
'' ORDER BY [firstName]
'' ORDER BY [firstName]
'' ORDER BY [firstName]
'' ORDER BY [firstName]
'' ORDER BY [firstName]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' ORDER BY [surname]
'' OR( [tblMemberDetails_CountyCode] = "c" ) ORDER BY [surname]
DER BY [surname]
NeoPa 32,556
Expert Mod 16PB
No. It's not possible to add any ORDER BY info to the WhereCondition parameter. WhereCondition Optional Variant. A string expression that's a valid SQL WHERE clause without the word WHERE.
ORDER BY is a separate clause in SQL.
Sorting is managed in a report within the design of the report itself.
NeoPa 32,556
Expert Mod 16PB
I just had a quick dig around in case you're interested, and changing the sorting within a report using code is done using the GroupLevel(?).ControlSource property (array) of the report.
Hi Guys and thanks for all the input.
I decided to lose the order by feature so that makes more straight forward.
However i have encountered a new problem.
I successfully integrated a multi select listbox for users to select and search for counties.
On the same page however I would like to integrate a similar multiselect box for nationality. I would like the user to be able to search for nationality with county or individually.
After inserting my nationality list box and adding the code. i can now only search for either
nationality or county and cant perform a combined search.
I think it may be something to do striping off last "OR".
Any help would be greatly appreciated
Many thanks for the lifeline. JAMES -
Private Sub btnSearch_Click()
-
-
' Update the record source
-
'Me.sbfrmSearchResults1.Form.RecordSource = "SELECT * FROM qryNew " & BuildFilter
-
-
' Update the record source
-
If BuildFilter = "" Then
-
Me.sbfrmSearchResults1.Form.RecordSource = "SELECT * FROM qryNew " & BuildFilter
-
Else
-
Me.sbfrmSearchResults1.Form.RecordSource = "SELECT * FROM qryNew WHERE " & BuildFilter
-
End If
-
-
'Requery the subform
-
Me.sbfrmSearchResults1.Requery
-
End Sub
-
Private Function BuildFilter() As Variant
-
Dim varWhere As Variant
-
Dim CountyCode As Variant
-
Dim varItem As Variant
-
Dim intIndex As Integer
-
Dim NationalityCode As Variant
-
Dim strSQLOrderBy As String
-
-
varWhere = Null ' Main filter
-
CountyCode = Null ' Subfilter used for colors
-
NationalityCode = Null
-
' Check for LIKE First Name
-
-
If Me.txtFirstName > "" Then
-
varWhere = varWhere & "[FirstName] LIKE """ & Me.txtFirstName & "*"" AND "
-
End If
-
-
' Check for LIKE Last Name
-
If Me.txtSurname > "" Then
-
varWhere = varWhere & "[surname] LIKE """ & Me.txtSurname & "*"" AND "
-
End If
-
-
If Me.txtRegNumber > "" Then
-
varWhere = varWhere & "[regnumber] like """ & Me.txtRegNumber & """ And "
-
End If
-
-
' Check for Colors in multiselect list
-
For Each varItem In Me.lstCountyCode.ItemsSelected
-
CountyCode = CountyCode & " [tblMemberDetails_CountyCode] = """ & _
-
Me.lstCountyCode.ItemData(varItem) & """ OR "
-
-
Next
-
-
'Test to see if we have subfilter for colors...
-
If IsNull(CountyCode) Then
-
' do nothing
-
Else
-
' strip off last "OR" in the filter
-
If Right(CountyCode, 4) = " OR " Then
-
CountyCode = Left(CountyCode, Len(CountyCode) - 4)
-
End If
-
-
'Add some parentheses around the subfilter
-
varWhere = varWhere & "( " & CountyCode & " )"
-
End If
-
-
'NationalityCode
-
-
' Check for Nationality in multiselect list
-
For Each varItem In Me.lstNationality.ItemsSelected
-
NationalityCode = NationalityCode & " [tblmemberdetails.NationalityCode] = """ & _
-
Me.lstNationality.ItemData(varItem) & """ OR "
-
-
Next
-
-
'Test to see if we have subfilter for colors...
-
If IsNull(NationalityCode) Then
-
' do nothing
-
Else
-
' strip off last "OR" in the filter
-
If Right(NationalityCode, 4) = " OR " Then
-
NationalityCode = Left(NationalityCode, Len(NationalityCode) - 4)
-
End If
-
-
'Add some parentheses around the subfilter
-
varWhere = varWhere & "( " & NationalityCode & " )"
-
End If
-
-
'Check if there is a filter to return...
-
If IsNull(varWhere) Then
-
varWhere = "''"
-
Else
-
-
' 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
-
End Function
-
Hi guys,
My problem is now resolved thanks to some advice
Changed
varWhere = varWhere & "( " & CountyCode & " ) AND "
to
varWhere = varWhere & "( " & CountyCode & " ) "
Many thanks for all the time.
James
Sign in to post your reply or Sign up for a free account.
Similar topics
by: MSD |
last post by:
I am running a report that uses a query as its record source and opens a
form collecting beginning and ending item numbers to feed to the query. This
works, but now I'm trying to use the result of...
|
by: Zlatko Matić |
last post by:
Hello.
How to reference selected values from a multi-select list box, as a
criteria in a query ?
Is it possible at all?
Regards,
Zlatko
|
by: Andy_Khosravi |
last post by:
I'm trying to build a search utility for users to find 'inquiries' in
my database that involves several tables. This is normally easy to do
with the query builder, but I have a unique situation...
|
by: Mark Roughton |
last post by:
I have a form where the users need to view records for various
criteria, one of which is a date field on which they may wish to view
all related data for the selected date, for all dates upto and...
|
by: jej1216 |
last post by:
I am trying to put together a PHP search page in which the user can select none, one, two, or three fields to search, and then the results php will build the SQL with dynamic where caluses to reflect...
|
by: The.Daryl.Lu |
last post by:
Hi, two parts to my problem if someone can help address either one or
both:
1. I want to SELECT everything in the table if it matches the
criteria when the query button is pressed (this is just...
|
by: aaronkmar |
last post by:
Hello Bytes,
I hope this post finds you well on this wonderful Friday!
I've been kicking this code around for over a week now and cannot seem to find the correct syntax to handle all of the...
|
by: woodey2002 |
last post by:
Hi Everyone and many thanks for your time.. I am trying to begin access and a bit of VBA i am enjoying it but I have a annoying problem I just can’t get any where on.
My databse mostly includes...
|
by: dizzydangler |
last post by:
Hi, I'm new to access (2007, running on XP), but I've gotten some great help from this site on my project so far. However, the more I know, the more complex the problems seem to become....
I'm...
|
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: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
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: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
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: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |