Hello Everyone,
I have a query by form with several list boxes. The user selects items from
the list boxes and clicks a button that returns results in a datasheet
subform.
One of the list boxes [lstStatus] contains two values: Call Back and No
Answer. The user can select either one or both and the proper results are
returned. If the user doesn't use this list box, then all Call Backs, No
Answers, and Null values are returned. As another option, I would like the
user to be able to select ONLY those records where the value of the field is
Null.
What code could I add to do that? Here is what I have that works as stated
above:
If Me!lstStatus.It emsSelected.Cou nt > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "AND" & "[Status] IN ("
For i = 0 To lstStatus.ListC ount - 1
If lstStatus.Selec ted(i) Then
strWhere = strWhere & "'" & lstStatus.Colum n(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
Else
strWhere = strWhere & "[Status] IN ("
For i = 0 To lstStatus.ListC ount - 1
If lstStatus.Selec ted(i) Then
strWhere = strWhere & "'" & lstStatus.Colum n(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End If
End If
Thanks for your help!
William
5  4126 
For this scenario, I thin an option group with four radio buttons
would be simpler: Call Back, No Answer, Both, and None. You could
then create your Where clauses easily based on the option selected:
Call Back
Status = "Call Back"
No Answer
Status = "No Answer"
Both
Status IN ("Call Back", "No Answer")
None
Status IS NULL
From a UI perspective, I think that is also simpler than a multiselect
listbox. HTH
Rick Collard www.msc-lims.com
On Thu, 26 Feb 2004 11:33:47 -0500, "William Wisnieski"
<ww********@adm issions.umass.e du> wrote: Hello Everyone,
I have a query by form with several list boxes. The user selects items from
the list boxes and clicks a button that returns results in a datasheet
subform.
One of the list boxes [lstStatus] contains two values: Call Back and No
Answer. The user can select either one or both and the proper results are
returned. If the user doesn't use this list box, then all Call Backs, No
Answers, and Null values are returned. As another option, I would like the
user to be able to select ONLY those records where the value of the field is
Null.
What code could I add to do that? Here is what I have that works as stated
above:
If Me!lstStatus.It emsSelected.Cou nt > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "AND" & "[Status] IN ("
For i = 0 To lstStatus.ListC ount - 1
If lstStatus.Selec ted(i) Then
strWhere = strWhere & "'" & lstStatus.Colum n(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
Else
strWhere = strWhere & "[Status] IN ("
For i = 0 To lstStatus.ListC ount - 1
If lstStatus.Selec ted(i) Then
strWhere = strWhere & "'" & lstStatus.Colum n(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End If
End If
Thanks for your help!
William
I realized that my earlier suggestion for an option group may not be
the best solution if your list of status options will grow over time.
In that case, why not search for null values when nothing is selected
in the list:
If IsNull(lstStatu s) Then strWhere = "Status IS NULL"
That still may not be terribly intuitive, so you might consider adding
a "(none)" entry to the list and using that selection to search for
Status IS NULL. But what happens if someone selects both (none) and a
valid status?. That's also not very friendly. Perhaps a combination
of check box and your list will work. The check box is labelled "No
status" and when checked you disable the listbox. HTH
Rick Collard www.msc-lims.com
On Thu, 26 Feb 2004 11:33:47 -0500, "William Wisnieski"
<ww********@adm issions.umass.e du> wrote: Hello Everyone,
I have a query by form with several list boxes. The user selects items from
the list boxes and clicks a button that returns results in a datasheet
subform.
One of the list boxes [lstStatus] contains two values: Call Back and No
Answer. The user can select either one or both and the proper results are
returned. If the user doesn't use this list box, then all Call Backs, No
Answers, and Null values are returned. As another option, I would like the
user to be able to select ONLY those records where the value of the field is
Null.
What code could I add to do that? Here is what I have that works as stated
above:
If Me!lstStatus.It emsSelected.Cou nt > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "AND" & "[Status] IN ("
For i = 0 To lstStatus.ListC ount - 1
If lstStatus.Selec ted(i) Then
strWhere = strWhere & "'" & lstStatus.Colum n(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
Else
strWhere = strWhere & "[Status] IN ("
For i = 0 To lstStatus.ListC ount - 1
If lstStatus.Selec ted(i) Then
strWhere = strWhere & "'" & lstStatus.Colum n(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End If
End If
Thanks for your help!
William
Thanks Rick. I like your suggestion to add an additional entry to the list
box, which I've done (No Status). But I can't figure out how to insert the
necessary code into my existing code from my first post. I was thinking
something like:
If Me.Status.Selec tion = 'No Status' Then strWhere = [Status] IS NULL
I just don't know how to incorporate this into the existing code.
Thanks again,
William
"Rick Collard" <no****@nospam. nospam> wrote in message
news:40******** ******@news.wes t.earthlink.net ... I realized that my earlier suggestion for an option group may not be
the best solution if your list of status options will grow over time.
In that case, why not search for null values when nothing is selected
in the list:
If IsNull(lstStatu s) Then strWhere = "Status IS NULL"
That still may not be terribly intuitive, so you might consider adding
a "(none)" entry to the list and using that selection to search for
Status IS NULL. But what happens if someone selects both (none) and a
valid status?. That's also not very friendly. Perhaps a combination
of check box and your list will work. The check box is labelled "No
status" and when checked you disable the listbox. HTH
Rick Collard
www.msc-lims.com
On Thu, 26 Feb 2004 11:33:47 -0500, "William Wisnieski"
<ww********@adm issions.umass.e du> wrote:
Hello Everyone,
I have a query by form with several list boxes. The user selects items
fromthe list boxes and clicks a button that returns results in a datasheet
subform.
One of the list boxes [lstStatus] contains two values: Call Back and No
Answer. The user can select either one or both and the proper results
arereturned. If the user doesn't use this list box, then all Call Backs, No
Answers, and Null values are returned. As another option, I would like
theuser to be able to select ONLY those records where the value of the field
isNull.
What code could I add to do that? Here is what I have that works as
statedabove:
If Me!lstStatus.It emsSelected.Cou nt > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "AND" & "[Status] IN ("
For i = 0 To lstStatus.ListC ount - 1
If lstStatus.Selec ted(i) Then
strWhere = strWhere & "'" & lstStatus.Colum n(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
Else
strWhere = strWhere & "[Status] IN ("
For i = 0 To lstStatus.ListC ount - 1
If lstStatus.Selec ted(i) Then
strWhere = strWhere & "'" & lstStatus.Colum n(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End If
End If
Thanks for your help!
William
How about something like this:
If Me!lstStatus.It emsSelected.Cou nt > 0 Then
strStatus = "[Status] IN ("
For i = 0 To lstStatus.ListC ount - 1
If lstStatus.Selec ted(i) Then
If lstStatus.Colum n(0, i) = "No Status" Then
strStatus = "[Status] IS NULL"
Exit For
End If
strStatus = strStatus & "'" & _
lstStatus.Colum n(0, i) & "', "
End If
Next i
If strStatus <> "[Status] IS NULL" Then _
strStatus = Left(strStatus, Len(strStatus) - 2) & ")"
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND " & strStatus
Else
strWhere = strStatus
End If
End If
Give that a try.
Rick Collard www.msc-lims.com
On Mon, 1 Mar 2004 16:55:21 -0500, "William Wisnieski"
<ww********@adm issions.umass.e du> wrote: Thanks Rick. I like your suggestion to add an additional entry to the list
box, which I've done (No Status). But I can't figure out how to insert the
necessary code into my existing code from my first post. I was thinking
something like:
If Me.Status.Selec tion = 'No Status' Then strWhere = [Status] IS NULL
I just don't know how to incorporate this into the existing code.
Thanks again,
William
Rick,
Thank you very much for the code. It works great. But what you said may
happen sure did. If I click on "No Status" and "No Answer", then the
results are only those with Null values as the status. I'm hoping I can
tweak this so that it will return both those with Null values AND the "No
Answer" value.
Thanks again for your help with this.
William
"Rick Collard" <no****@nospam. nospam> wrote in message
news:40******** *******@news.we st.earthlink.ne t... How about something like this:
If Me!lstStatus.It emsSelected.Cou nt > 0 Then
strStatus = "[Status] IN ("
For i = 0 To lstStatus.ListC ount - 1
If lstStatus.Selec ted(i) Then
If lstStatus.Colum n(0, i) = "No Status" Then
strStatus = "[Status] IS NULL"
Exit For
End If
strStatus = strStatus & "'" & _
lstStatus.Colum n(0, i) & "', "
End If
Next i
If strStatus <> "[Status] IS NULL" Then _
strStatus = Left(strStatus, Len(strStatus) - 2) & ")"
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND " & strStatus
Else
strWhere = strStatus
End If
End If
Give that a try.
Rick Collard
www.msc-lims.com
On Mon, 1 Mar 2004 16:55:21 -0500, "William Wisnieski"
<ww********@adm issions.umass.e du> wrote:
Thanks Rick. I like your suggestion to add an additional entry to the
listbox, which I've done (No Status). But I can't figure out how to insert
thenecessary code into my existing code from my first post. I was thinking
something like:
If Me.Status.Selec tion = 'No Status' Then strWhere = [Status] IS NULL
I just don't know how to incorporate this into the existing code.
Thanks again,
William
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Dave Karmens |
last post by:
If I have say 10 fixed variables, how can I set their values = to that
of a form that is built dynamically?
column1 column2
email = formvalue(0)
fname = formvalue(1)
lname = formvalue(2)
etc..
|
by: James Baker |
last post by:
I have a form that has a dropdown list that will cause a post to the same
page when it's changed. The problem I'm running into is that all of the
controls reset to their default values (obviously expected behavior).
What's the recommended/best way to persist these values through the post
process? I know I could set them to the request.form values of themselves,
so to speak...but I didn't know if there was a simpler/more efficient way.
...
|
by: raj |
last post by:
hi,
my list box in the html page contains list box with values. these
values inturn have subvalues and I have to allign the child values to
right so that i can know the parent of the values. how can I do that.
list values should look something like this...
(suppose u have a list box with the state names in usa. the cities of
the states would be the child elements and the state names would be the
parent values)
ALABAMA
|
by: Regena |
last post by:
hi,
my list box in the html page contains list box with values. these
values inturn have subvalues and I have to allign the child values to
right so that i can know the parent of the values. how can I do that.
list values should look something like this...
(suppose u have a list box with the state names in usa. the cities of
the states would be the child elements and the state names would be the
parent values)
ALABAMA
|
by: assgar |
last post by:
Hi
Developemnt on win2003 server. Final server will be linux
Apache,Mysql and PHP is being used.
I use 2 scripts(form and process).
The form displays multiple dynamic rows with chechboxs, input box for units of service, description of the service and each row has its own dropdown list of unit fees that apply.
Each dynamically created row will return 3 values fee1_choice, fee1_unit and fee1_money.
Note The above informaton is...
| | |
by: assgar |
last post by:
Hi
I need help solving a porblem.
I have a form that displays a checkbox, service code, description
and dropdown with fees on each row.
The fee_money and unit array only returns a selected value from the
first item (checkbox).
|
by: mvlt |
last post by:
I am using Access 2003 and working on a query by form. I have a form with two unbound combo boxes. The form searches our contact list for people in a certain renewal cycle. It then gives the option of looking for a certain constituency within that group.
The first combo box, cboRenewalCycle, lets the user select from a list of Cycles (FEB, JUN, SEP). The second, cboConstituent, lets the user select from a list of Constituents (Business,...
|
by: cyberdawg999 |
last post by:
Greetings all in ASP land
I have overcome one obstacle that took me 2 weeks to overcome and I
did it!!!!!
I am so elated!! thank you to all who invested their time and energy towards helping me with my problems.
Now for my new little problem,I had a problem posting the values from checkbox fields to a database and thats the obstacle I overcame.
Now the second part is my new problem is that I want that the next time that page loads for...
|
by: ADezii |
last post by:
Null as it relates to database development is one of life's little mysteries and a topic of total confusion for novices who venture out into the database world. A Null Value is not zero (0), a zero (0) length string, an empty Field, or no value at all - so exactly what is Null? The purpose of this Topic is hopefully to explain what a Null Value is, discuss some peculiarities about Nulls, show how we can detect them, and finally, how to convert...
|
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: 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 tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| | |
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 Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
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.
| |
