Multi-select no selection made - display all records

  • Thread starter Thread starter Rana
  • Start date Start date
R

Rana

Hi all,

I've got a what I'm sure is an easy question for the pros. Please se below
for details as it is a follow-up from a previous problem.

-----------------------------------------------------------------------------------------------
Michael, thanx for the tip it works like a charm. I now however want the
query to run even if i dont select any of the criteria from the multi-select
box. I know I should have " Is Null" somewhere but I dont know where to/how
to include it in the VBA code.

Any ideas?
Thanx.
Rana
 
I do it something like:

Dim varItem as variant
Dim varCriteria as variant

varCriteria = Null
For each varItem in lstSkillsInterests.ItemsSelected
varCriteria = (varCriteria + ", ") & "'" & lstSkillsInterests.column(0,
varItem) & "'"
Next

If len(NZ(varCriteria, "") - len(replace(NZ(varCriteria, ""), ",", "") < 1
then
strWhere = "WHERE Skills_Interests.Skills_Interests_Preference1 = " _
+ varCriteria
Else
strWhere = "WHERE Skills_Interests.Skills_Interests_Preference1 IN(" _
& varCriteria & ")"
endif

strSQL = strSQL & strWhere

1. Using ItemsSelected instead of listcount lets you loop through only
those items that are selected, speeding up the process.

2. After the loop, I test to see whether there is a comma in the
varCriteria variable. If not, I concatenate that variable to the "WHERE "
clause using a plus sight. This way, if varCriteria = NULL then strWhere =
NULL. If varCriteria = "a" then strWhere = "WHERE ...... = 'a'". If there
is a comma in varCriteria, I use concatenate the WHERE clause using IN ( ).
So, your resulting SQL string has one of 3 conditions.

a. no where clause at all (varCriteria = Null)
b. a where clause with an = (varSriteria is a single selection)
c. a where clause with an IN ( ) clause (varCriteria includes multiple
selections)

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.
 
Bonnie thanx for ur reply. I could do that however doesnt that run the chance
of making my database alot bigger and slower?

Rana

bhicks11 via AccessMonster.com said:
How about simply:

If not is null(thelist) then
use this query
else
use this query
End If

Bonnie
http://www.dataplus-svc.com
Hi all,

I've got a what I'm sure is an easy question for the pros. Please se below
for details as it is a follow-up from a previous problem.

-----------------------------------------------------------------------------------------------
Michael, thanx for the tip it works like a charm. I now however want the
query to run even if i dont select any of the criteria from the multi-select
box. I know I should have " Is Null" somewhere but I dont know where to/how
to include it in the VBA code.

Any ideas?
Thanx.
Rana

Michel Walsh said:
Indeed, you need:
Click to expand...
[quoted text clipped - 90 lines]
Regards,
Rana.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top