Writing a query using VBA - Variable where clause

[BlueWolverine]

Hello,
MS ACCESS 2003 on XP Pro.

I want to write a query using vba based on the selection(s) from a list box.

If the list box has N entries, I want the user to be able to highlight n
entries, click a button, and generate a query that looks something like this.

select *
where
id = n_1 or id = n_2 or ... or id = n

Because n and N are so variable and uncontrollable, the code would have to
run for each i in n.

Please let me know if this is unclear...

My first step is I don't know how to read out multiple values highlighted in
a list box. Then I need help writing that SQL in VBA.

Thanks.

 

[Michel Walsh]

You have at least three solutions:

1- Make a temporary table, one (indexed) field, one record per item
selected. Use a query which will make an inner join with that temporary
table.

2- Make an ad hoc query from a string. The string will be a complete SQL
statement that you will use appropriately (depends on the context). See
http://www.mvps.org/access/forms/frm0007.htm

3- Make a query which will accept a string ( list of all the selected items,
a little bit like solution 2) but send that string as a parameter to a query
using LIKE:

... WHERE "," & stringAsParam & "," LIKE "*[, ]" & fieldName &
"[, ]*"


where the stringAsParam holds a value like: "1, 2, 4, 66" and the
statement is equivalent to WHERE fieldName IN(1, 2, 3, 66)



Solution 3 is more secure, in theory, than solution 2 (which implies a
complete SQL statement rather than a parameter), but solution 1 is probably
the fastest one, at execution time.


Vanderghast, Access MVP

 

[BlueWolverine]

How do I read the highlighted values into the temp table?
--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!

You have at least three solutions:

1- Make a temporary table, one (indexed) field, one record per item
selected. Use a query which will make an inner join with that temporary
table.

2- Make an ad hoc query from a string. The string will be a complete SQL
statement that you will use appropriately (depends on the context). See
http://www.mvps.org/access/forms/frm0007.htm

3- Make a query which will accept a string ( list of all the selected items,
a little bit like solution 2) but send that string as a parameter to a query
using LIKE:

... WHERE "," & stringAsParam & "," LIKE "*[, ]" & fieldName &
"[, ]*"


where the stringAsParam holds a value like: "1, 2, 4, 66" and the
statement is equivalent to WHERE fieldName IN(1, 2, 3, 66)



Solution 3 is more secure, in theory, than solution 2 (which implies a
complete SQL statement rather than a parameter), but solution 1 is probably
the fastest one, at execution time.


Vanderghast, Access MVP

 

[Michel Walsh]

Same as for building the IN string, or the parameter string (see the example
supplied in reference), but the foreach loop execute an INSERT INTO
statement:

CurrentDb.Execute "INSERT INTO tempTable( fieldName) VALUES(" &
ctl.ItemData(varItem) & ")"

as example, if your value is NUMERICAL. If the value is a string, instead of
an integer, try:

CurrentDb.Execute "INSERT INTO tempTable( fieldName) VALUES(""" &
ctl.ItemData(varItem) & """)"


Also remember to clear the temp table of its record, as first step (before
the foreach loop):

CurrentDb.Execute "DELETE * FROM tempTable"




Vanderghast, Access MVP

How do I read the highlighted values into the temp table?
--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!

You have at least three solutions:

1- Make a temporary table, one (indexed) field, one record per item
selected. Use a query which will make an inner join with that temporary
table.

2- Make an ad hoc query from a string. The string will be a complete SQL
statement that you will use appropriately (depends on the context). See
http://www.mvps.org/access/forms/frm0007.htm

3- Make a query which will accept a string ( list of all the selected
items,
a little bit like solution 2) but send that string as a parameter to a
query
using LIKE:

... WHERE "," & stringAsParam & "," LIKE "*[, ]" & fieldName &
"[, ]*"


where the stringAsParam holds a value like: "1, 2, 4, 66" and the
statement is equivalent to WHERE fieldName IN(1, 2, 3, 66)



Solution 3 is more secure, in theory, than solution 2 (which implies a
complete SQL statement rather than a parameter), but solution 1 is
probably
the fastest one, at execution time.


Vanderghast, Access MVP


message

Hello,
MS ACCESS 2003 on XP Pro.

I want to write a query using vba based on the selection(s) from a list
box.

If the list box has N entries, I want the user to be able to highlight
n
entries, click a button, and generate a query that looks something like
this.

select *
where
id = n_1 or id = n_2 or ... or id = n

Because n and N are so variable and uncontrollable, the code would have
to
run for each i in n.

Please let me know if this is unclear...

My first step is I don't know how to read out multiple values
highlighted
in
a list box. Then I need help writing that SQL in VBA.

Thanks.

 

[BlueWolverine]

Thank you so much. I figured out that you had already dropped an answer to
that part afterwards. SOrry for making you type that which you had already
answered.

I have one last question.

I want to make my form remember the last settings and default to them the
next time the form is opened. How can I make a list box open with entries
selected based on the contents of the temp table?

obviously I want the user to be able to change those settings, thus
overriding the temp table.

Thanks.
--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!

Same as for building the IN string, or the parameter string (see the example
supplied in reference), but the foreach loop execute an INSERT INTO
statement:

CurrentDb.Execute "INSERT INTO tempTable( fieldName) VALUES(" &
ctl.ItemData(varItem) & ")"

as example, if your value is NUMERICAL. If the value is a string, instead of
an integer, try:

CurrentDb.Execute "INSERT INTO tempTable( fieldName) VALUES(""" &
ctl.ItemData(varItem) & """)"


Also remember to clear the temp table of its record, as first step (before
the foreach loop):

CurrentDb.Execute "DELETE * FROM tempTable"




Vanderghast, Access MVP

How do I read the highlighted values into the temp table?
--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!

You have at least three solutions:

1- Make a temporary table, one (indexed) field, one record per item
selected. Use a query which will make an inner join with that temporary
table.

2- Make an ad hoc query from a string. The string will be a complete SQL
statement that you will use appropriately (depends on the context). See
http://www.mvps.org/access/forms/frm0007.htm

3- Make a query which will accept a string ( list of all the selected
items,
a little bit like solution 2) but send that string as a parameter to a
query
using LIKE:

... WHERE "," & stringAsParam & "," LIKE "*[, ]" & fieldName &
"[, ]*"


where the stringAsParam holds a value like: "1, 2, 4, 66" and the
statement is equivalent to WHERE fieldName IN(1, 2, 3, 66)



Solution 3 is more secure, in theory, than solution 2 (which implies a
complete SQL statement rather than a parameter), but solution 1 is
probably
the fastest one, at execution time.


Vanderghast, Access MVP


message
Hello,
MS ACCESS 2003 on XP Pro.

I want to write a query using vba based on the selection(s) from a list
box.

If the list box has N entries, I want the user to be able to highlight
n
entries, click a button, and generate a query that looks something like
this.

select *
where
id = n_1 or id = n_2 or ... or id = n

Because n and N are so variable and uncontrollable, the code would have
to
run for each i in n.

Please let me know if this is unclear...

My first step is I don't know how to read out multiple values
highlighted
in
a list box. Then I need help writing that SQL in VBA.

Thanks.

 

[Michel Walsh]

You can reset everyone in the collection ItemsSelected to false, then, walk
that collection and set its member to true for member items IN the "temp"
table. (Temporary is then an bad name for such a table which should survive
the session, though )



Vanderghast, Access MVP

Thank you so much. I figured out that you had already dropped an answer
to
that part afterwards. SOrry for making you type that which you had already
answered.

I have one last question.

I want to make my form remember the last settings and default to them the
next time the form is opened. How can I make a list box open with entries
selected based on the contents of the temp table?

obviously I want the user to be able to change those settings, thus
overriding the temp table.

Thanks.
--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!

Same as for building the IN string, or the parameter string (see the
example
supplied in reference), but the foreach loop execute an INSERT INTO
statement:

CurrentDb.Execute "INSERT INTO tempTable( fieldName) VALUES(" &
ctl.ItemData(varItem) & ")"

as example, if your value is NUMERICAL. If the value is a string, instead
of
an integer, try:

CurrentDb.Execute "INSERT INTO tempTable( fieldName) VALUES(""" &
ctl.ItemData(varItem) & """)"


Also remember to clear the temp table of its record, as first step
(before
the foreach loop):

CurrentDb.Execute "DELETE * FROM tempTable"




Vanderghast, Access MVP



message

How do I read the highlighted values into the temp table?
--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!


:

You have at least three solutions:

1- Make a temporary table, one (indexed) field, one record per item
selected. Use a query which will make an inner join with that
temporary
table.

2- Make an ad hoc query from a string. The string will be a complete
SQL
statement that you will use appropriately (depends on the context).
See
http://www.mvps.org/access/forms/frm0007.htm

3- Make a query which will accept a string ( list of all the selected
items,
a little bit like solution 2) but send that string as a parameter to a
query
using LIKE:

... WHERE "," & stringAsParam & "," LIKE "*[, ]" & fieldName &
"[, ]*"


where the stringAsParam holds a value like: "1, 2, 4, 66" and the
statement is equivalent to WHERE fieldName IN(1, 2, 3, 66)



Solution 3 is more secure, in theory, than solution 2 (which implies a
complete SQL statement rather than a parameter), but solution 1 is
probably
the fastest one, at execution time.


Vanderghast, Access MVP


message
Hello,
MS ACCESS 2003 on XP Pro.

I want to write a query using vba based on the selection(s) from a
list
box.

If the list box has N entries, I want the user to be able to
highlight
n
entries, click a button, and generate a query that looks something
like
this.

select *
where
id = n_1 or id = n_2 or ... or id = n

Because n and N are so variable and uncontrollable, the code would
have
to
run for each i in n.

Please let me know if this is unclear...

My first step is I don't know how to read out multiple values
highlighted
in
a list box. Then I need help writing that SQL in VBA.

Thanks.

 

[BlueWolverine]

Hey thank you a ton, but I'm still alittle lost on execution. I understand
what you're saying but I can't figure out the code to do it. I don't need
the loop logic or anything but how do you manipulate these states for the
items in a list box.

ctl.ItemsSelected = false does not work.
and how do you access the unselected items? there's no ctl.ItemsinList that
I can see.

Thanks.
--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!

You can reset everyone in the collection ItemsSelected to false, then, walk
that collection and set its member to true for member items IN the "temp"
table. (Temporary is then an bad name for such a table which should survive
the session, though )



Vanderghast, Access MVP

Thank you so much. I figured out that you had already dropped an answer
to
that part afterwards. SOrry for making you type that which you had already
answered.

I have one last question.

I want to make my form remember the last settings and default to them the
next time the form is opened. How can I make a list box open with entries
selected based on the contents of the temp table?

obviously I want the user to be able to change those settings, thus
overriding the temp table.

Thanks.
--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!

Same as for building the IN string, or the parameter string (see the
example
supplied in reference), but the foreach loop execute an INSERT INTO
statement:

CurrentDb.Execute "INSERT INTO tempTable( fieldName) VALUES(" &
ctl.ItemData(varItem) & ")"

as example, if your value is NUMERICAL. If the value is a string, instead
of
an integer, try:

CurrentDb.Execute "INSERT INTO tempTable( fieldName) VALUES(""" &
ctl.ItemData(varItem) & """)"


Also remember to clear the temp table of its record, as first step
(before
the foreach loop):

CurrentDb.Execute "DELETE * FROM tempTable"




Vanderghast, Access MVP



message
How do I read the highlighted values into the temp table?
--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!


:

You have at least three solutions:

1- Make a temporary table, one (indexed) field, one record per item
selected. Use a query which will make an inner join with that
temporary
table.

2- Make an ad hoc query from a string. The string will be a complete
SQL
statement that you will use appropriately (depends on the context).
See
http://www.mvps.org/access/forms/frm0007.htm

3- Make a query which will accept a string ( list of all the selected
items,
a little bit like solution 2) but send that string as a parameter to a
query
using LIKE:

... WHERE "," & stringAsParam & "," LIKE "*[, ]" & fieldName &
"[, ]*"


where the stringAsParam holds a value like: "1, 2, 4, 66" and the
statement is equivalent to WHERE fieldName IN(1, 2, 3, 66)



Solution 3 is more secure, in theory, than solution 2 (which implies a
complete SQL statement rather than a parameter), but solution 1 is
probably
the fastest one, at execution time.


Vanderghast, Access MVP


message
Hello,
MS ACCESS 2003 on XP Pro.

I want to write a query using vba based on the selection(s) from a
list
box.

If the list box has N entries, I want the user to be able to
highlight
n
entries, click a button, and generate a query that looks something
like
this.

select *
where
id = n_1 or id = n_2 or ... or id = n

Because n and N are so variable and uncontrollable, the code would
have
to
run for each i in n.

Please let me know if this is unclear...

My first step is I don't know how to read out multiple values
highlighted
in
a list box. Then I need help writing that SQL in VBA.

Thanks.

 

[Michel Walsh]

There is not, but you can use

DCOUNT("*", "tempTable", "fieldName=" & ctl.ItemData(varItem) )

and if it returns 0, the item is not in the temp table. Note that you need
the right delimiters if the item is a string, or a date (here is use a
syntax for the item being an integer).


To select the lines, or not, you can use just one pass, after all, like:



Dim i As Long
For i = 0 To Me.List0.ListCount - 1
' do something with Me.List0.ItemData(i), or simply random:
Me.List0.Selected(i) = (10 * Rnd()) Mod 2
Next




which, here, randomly select about 50% of the lines. In your case, it
won't be based on Rnd but based on DCount, or otherwise.




Vanderghast, Access MVP

Hey thank you a ton, but I'm still alittle lost on execution. I
understand
what you're saying but I can't figure out the code to do it. I don't need
the loop logic or anything but how do you manipulate these states for the
items in a list box.

ctl.ItemsSelected = false does not work.
and how do you access the unselected items? there's no ctl.ItemsinList
that
I can see.

Thanks.
--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!

You can reset everyone in the collection ItemsSelected to false, then,
walk
that collection and set its member to true for member items IN the
"temp"
table. (Temporary is then an bad name for such a table which should
survive
the session, though )



Vanderghast, Access MVP


message

Thank you so much. I figured out that you had already dropped an
answer
to
that part afterwards. SOrry for making you type that which you had
already
answered.

I have one last question.

I want to make my form remember the last settings and default to them
the
next time the form is opened. How can I make a list box open with
entries
selected based on the contents of the temp table?

obviously I want the user to be able to change those settings, thus
overriding the temp table.

Thanks.
--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!


:

Same as for building the IN string, or the parameter string (see the
example
supplied in reference), but the foreach loop execute an INSERT INTO
statement:

CurrentDb.Execute "INSERT INTO tempTable( fieldName) VALUES(" &
ctl.ItemData(varItem) & ")"

as example, if your value is NUMERICAL. If the value is a string,
instead
of
an integer, try:

CurrentDb.Execute "INSERT INTO tempTable( fieldName) VALUES(""" &
ctl.ItemData(varItem) & """)"


Also remember to clear the temp table of its record, as first step
(before
the foreach loop):

CurrentDb.Execute "DELETE * FROM tempTable"




Vanderghast, Access MVP



message
How do I read the highlighted values into the temp table?
--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!


:

You have at least three solutions:

1- Make a temporary table, one (indexed) field, one record per item
selected. Use a query which will make an inner join with that
temporary
table.

2- Make an ad hoc query from a string. The string will be a
complete
SQL
statement that you will use appropriately (depends on the context).
See
http://www.mvps.org/access/forms/frm0007.htm

3- Make a query which will accept a string ( list of all the
selected
items,
a little bit like solution 2) but send that string as a parameter
to a
query
using LIKE:

... WHERE "," & stringAsParam & "," LIKE "*[, ]" &
fieldName &
"[, ]*"


where the stringAsParam holds a value like: "1, 2, 4, 66" and
the
statement is equivalent to WHERE fieldName IN(1, 2, 3, 66)



Solution 3 is more secure, in theory, than solution 2 (which
implies a
complete SQL statement rather than a parameter), but solution 1 is
probably
the fastest one, at execution time.


Vanderghast, Access MVP


message
Hello,
MS ACCESS 2003 on XP Pro.

I want to write a query using vba based on the selection(s) from
a
list
box.

If the list box has N entries, I want the user to be able to
highlight
n
entries, click a button, and generate a query that looks
something
like
this.

select *
where
id = n_1 or id = n_2 or ... or id = n

Because n and N are so variable and uncontrollable, the code
would
have
to
run for each i in n.

Please let me know if this is unclear...

My first step is I don't know how to read out multiple values
highlighted
in
a list box. Then I need help writing that SQL in VBA.

Thanks.