By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,504 Members | 1,212 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,504 IT Pros & Developers. It's quick & easy.

Function IsSelectedVar and Null value

P: 49
Hello
I need some help with the Function IsSelectedVar and fields that have null value. I have a form that has 4 multi select list boxes; they are used to filter records for reports. The 4 fields are Site location, Primary function, secondary function, Tertiary_function,. The secondary function and Tertiary_function will not always have a value in the field. When I try to run the report or the underlying query, if the field has no value the records will not show up. Example, if I select all of the Gyms, for a group of locations, I will get all items associated with the Gym I.e. storage rooms etc, but the gyms themselves. Can anyone help?
Thanks
Sep 19 '07 #1
Share this Question
Share on Google+
11 Replies


JKing
Expert 100+
P: 1,206
Hi, how are you setting the where criteria for the report?
For example is the where clause of your query set within the SQL of your query or are you building the criteria in vba and passing it into the where statement parameter of the openreport command?
Sep 19 '07 #2

P: 49
Hi, how are you setting the where criteria for the report?
For example is the where clause of your query set within the SQL of your query or are you building the criteria in vba and passing it into the where statement parameter of the openreport command?
Sorry I ment to post this orginal post
Expand|Select|Wrap|Line Numbers
  1. SELECT [Show all sites].*
  2. FROM [Show all sites]
  3. WHERE (((IsSelectedVar("frm_cntrl","site fltr",[site_id]))=-1) AND ((IsSelectedVar("frm_cntrl","funcfltr",[Primary_function]))=-1) AND ((IsSelectedVar("frm_cntrl","sfuncfltr",[Secondary_function]))=-1));
  4.  
Sep 19 '07 #3

JKing
Expert 100+
P: 1,206
I think a solution would be to remove the where clause form your query a create a report based on all returned values. Then when opening the report specify the where statement in the open report command.

This will require some VBA coding. The idea behind it is on say a button click for example that a criteria string will be built that takes in all selected parameters on a form that passes them into the openreport command and it filters based on the criteria string.
Sep 22 '07 #4

NeoPa
Expert Mod 15k+
P: 31,186
I looked for IsSelectedVar() and found nothing on it at all. I'm using A2K. Which version are you using? Or maybe it's one of your own functions?
Sep 22 '07 #5

P: 49
I looked for IsSelectedVar() and found nothing on it at all. I'm using A2K. Which version are you using? Or maybe it's one of your own functions?
2003 i think this function is new top 2003, I have never seen it in previous versions
Sep 24 '07 #6

P: 49
I think a solution would be to remove the where clause form your query a create a report based on all returned values. Then when opening the report specify the where statement in the open report command.

This will require some VBA coding. The idea behind it is on say a button click for example that a criteria string will be built that takes in all selected parameters on a form that passes them into the openreport command and it filters based on the criteria string.
I have tried that, that is actually how I did it the first time. No luck, the problem I have is that some of the secondary and tertiary function fields are blank.
Sep 24 '07 #7

daniel aristidou
100+
P: 491
I have tried that, that is actually how I did it the first time. No luck, the problem i have is that some of the secondary and teriatry function feilds are blank.
Wopuld the promblem not be solved if u used "or" as well as "and" in your sql?
ie make it query for all the feilds or some of the feilds eg
Expand|Select|Wrap|Line Numbers
  1. Where  (((IsSelectedVar("frm_cntrl","site fltr",[site_id]))=-1) AND ((IsSelectedVar("frm_cntrl","funcfltr",[Primary_function]))=-1) AND ((IsSelectedVar("frm_cntrl","sfuncfltr",[Secondary_function]))=-1))
  2. OR
  3. (((IsSelectedVar("frm_cntrl","site fltr",[site_id]))=-1) AND ((IsSelectedVar("frm_cntrl","funcfltr",[Primary_function]))=-1)

Etc

PS im newb in access .......just suggestion.
Sep 24 '07 #8

P: 49
Wopuld the promblem not be solved if u used "or" as well as "and"
in your sql?
ie make it query for all the feilds or some of the feilds eg
Where (((IsSelectedVar("frm_cntrl","site fltr",[site_id]))=-1) AND ((IsSelectedVar("frm_cntrl","funcfltr",[Primary_function]))=-1) AND ((IsSelectedVar("frm_cntrl","sfuncfltr",[Secondary_function]))=-1))
OR
(((IsSelectedVar("frm_cntrl","site fltr",[site_id]))=-1) AND ((IsSelectedVar("frm_cntrl","funcfltr",[Primary_function]))=-1)


Ect

Ps im newb in access .......just suggestion.
[qoute]
thanks for the suggestion. I will try it, i think is has to do with the fact that the secondary function and in the furture the tertiary fuction may be null.
Sep 24 '07 #9

NeoPa
Expert Mod 15k+
P: 31,186
2003 i think this function is new top 2003, I have never seen it in previous versions
Thanks for the answer :)
Sorry I can't be more help at this stage.
Sep 24 '07 #10

P: 49
Thanks for the answer :)
Sorry I can't be more help at this stage.
Neopa
Thanks for looking at it. I have not been able to find anything to help me with this. I dont belive that it will work if a feild has a null value. Do you know if you can automatically select an item in a list box via code ie.. on enter?
Sep 24 '07 #11

NeoPa
Expert Mod 15k+
P: 31,186
To select an item in a ListBox or ComboBox control programatically, you simply set the value of the control to the value of the item you want to select.
If the ControlSource of a ComboBox is :
Expand|Select|Wrap|Line Numbers
  1. SELECT [Colour]
  2. FROM [tblRainbow]
then to select the third element (Yellow) you say :
Expand|Select|Wrap|Line Numbers
  1. Me.ComboBoxName = "Yellow"
Sep 24 '07 #12

Post your reply

Sign in to post your reply or Sign up for a free account.