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 5589 ADezii 8,834
Recognized Expert Expert - 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 tblMemberDetail s, 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 tblMemberDetail s 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,FirstNa me 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
ADezii 8,834
Recognized Expert Expert
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( [tblMemberDetail s_CountyCode] = "c" ) ORDER BY [surname]
DER BY [surname]
NeoPa 32,567
Recognized Expert Moderator MVP
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,567
Recognized Expert Moderator MVP
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(?).C ontrolSource property (array) of the report.
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 a multi-select list box on
the form to feed multiple item numbers to the query.
In other words - if you pick item #100,105,110 from the list box, the query
should display information for those 3 items.
If I type 'IN (100,105,110)' in the...
|
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 involving a multi
select listbox. Unfortunatly, my SQL skills are somewhat limited, so
I'm not sure if there is an easy way around it. To simplify the
explanation, I'll simplify the table/field setup to get at the meat of
the question.
I have a...
|
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 icluding
the selected date or all records on or after the selected date
The user selects either "=", >=" or "<=" from a combo box and then a
date from another combobox. The combination of thse two choices is
then set in an unbound textbox so...
|
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 the fields chosen.
The first page, where they select the search fields and submit:
<?php
$db = mysql_connect("localhost", "root", "yeahright");
if (!$db)
{
die('Could not connect:'.mysql_error);
| |
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 a
commandbutton on my form). The biggest problem I'm having is that I
can't (don't know how) populate a combobox on static items such as
"True, False, Any." The Any field, would be selected by the user to
find all records where that fields is...
|
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 possible selections of my search form.
Would someone please help me correct my IF statements in this code used for searching.
Here's how it works.
|
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 bits of code for different examples. I have one last thing to finish.
I am trying to create a search form that will allow users to select criteria from multiple sources eg ,multi select list boxes , combo boxes.
I have a subform showing all the...
|
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 working with all of my data in a single table and, right now, I've got a form linked to a query that allows the user to input whatever search criteria they want through a variety of controls. I've also created a series of check boxes on the form...
|
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, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
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 protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
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 presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |