Is the field numeric? If so, drop the quotes
"WHERE [tbl Event].[Event ID] IN (1, 3);"
--
hth,
SusanV
I am using the IN() instead of all of those OR statements in SQL. I am
getting an error message that says "Data type mismatch in criteria
expression." What does this mean? Here is the "stWhere" string when
the
user selects "1" and "3" in the list box.
"WHERE [tbl Event].[Event ID] IN ("1", "3");"
Thank you for your help! I have never used the IN part of SQL before.
:
Why?
The IN() is a whole lot easier than a string or ORs.
To change it, you will have to recode where strWhere is created.
Even if you choose to do that, all those useless (((((((((((((((((
that
Access puts in can be omitted.
WHERE [tbl Event].[Event ID])=1 OR [tbl Event].[Event ID]=3;
:
I have a new problem. I am using the multi-select list box and it
is
correctly allowing the user to make multiple selections. I am using
the code
you gave me to store the selected records in a string. (I put
msgbox
in my
code to see the string and it is correctly storing the selected
records.)
The problem I am now having is using the IN statement of SQL.
For example, I want my "stWhere" to look like the following if the
user
selects Event ID "1" and Event ID "2". How would I achieve this
using
your
For Each....statement?
WHERE ((([tbl Event].[Event ID])=1)) OR ((([tbl Event].[Event
ID])=3));
:
I guess you could, but I really think that would take more time
and
be more
prone to errors. Also, remember, you want to be able to select a
range or
use <ctl>Click to select multiple noncontiguous items. You would
not
be able
to do that easily.
I really believe the multi select list box is the easiest to
develop
and
would be the most user friendly.
:
Could I use something else, like a text box and have the user to
sepearte the
choices with commas or do a range with a hyphen? Similar to
print
options
when selecting the pages to be printed. If so, do you have the
code to do
this?
:
There is not a better solution that I can think of. I tried
using a combo
with auto expand to position the list box, but anything
previously selected
became unselected.
:
The [Event ID] text is very meaningful to the user and is
unique. (It is the
primary key in the "tbl Event" table.) There are currently
about 100+
records or [Event ID]s that the user could choose from but
could be more very
soon. If columns is not a good alternative, do you know of
another option?
:
If you are talking about putting multiple [Event ID]s in
the
list box item (1
id per column), it is possible, but not easy, and would be
difficult to
select. How would the user know which column to select?
How
would you, as
the developer, know which column to use? Is the [Event
ID]
text that is
meaningful to a user? Is it unique? How many are we
talking
about?
:
Let me give you my scenario:
In the form, there is a multi-select list box with the
control source as
"SELECT [tbl Event].[Event ID] FROM [tbl Event]; " This
correctly displays
all the records for the Event ID field.
In this list box, there are many records that I have to
scroll through in
order to select the ones that I want. I was wondering
if
there is a way to
display more records in the list box rather than scroll
through each of them.
:
You can't use a multi select list box control as a
bound
control. It will
always return a Null value.
I'm not sure I understand the issue.
:
Thank you! I am now able to make multiple
selections
in my list box. Now
the problem lies with the text that is in the list
box.
There is just field
for the control source on the list box but multiple
of
records. The field is
just an AutoNumber in the list box. I don't want to
have to scroll down all
the way through the list to choose the records I
want
to send to SQL
statement. Is there a way to make it wrap the text
by
using columns in the
list box?
:
You will need a List Box control with the Multi
Select property set to
Extended. This will allow you to use the
familiar,
Click to get one, <Ctl>
Click to add another, or <Shift> Click to select a
range.
Now, you need a Row Source for the field. It
should
look something like:
SELECT DISTINCT SomeFieldName FROM SomeTableName
This will return one occurance of every value in
the
field.
Once the user has selected a field, you can use
the
ItemsSelected collection
of the List Box control to put together a where
condition for your SQL
statement:
Dim varItem As Variant
Dim strWhere as String
strWhere = "IN("
For Each varItem In
Me.MyListBoxName.ItemsSelected
strWhere = strWhere & Chr(34) &
Me.MyListBoxName.ItemData(varItem) & _
Chr(34) & ","
Next varItem
'Take off the last comma
strWhere = Left(strWhere, Len(strWhere) -1)
'Add the closing paren
strWhere = strWhere & ")"
Now you have a string you can use in your SQL that
will filter based on the
selections maked in the list box.
:
Hello,
I want to be able to make multiple selections on
my
form from the same field
in the same table but allow specific records to
be
selected, as well as a
range of records and certain records (comma
delimited?). After the fields
are selected, I want to pass this value into a
SQL
statement to perform the
query. How do I do this?
