473,320 Members | 2,088 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Function IsSelectedVar and Null value

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
11 1787
JKing
1,206 Expert 1GB
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
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
1,206 Expert 1GB
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
32,556 Expert Mod 16PB
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
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
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
491 256MB
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
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
32,556 Expert Mod 16PB
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
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
32,556 Expert Mod 16PB
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

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

Similar topics

2
by: laredotornado | last post by:
Hello, I am looking for a cross-browser way (Firefox 1+, IE 5.5+) to have my Javascript function execute from the BODY's "onload" method, but if there is already an onload method defined, I would...
24
by: Edmund Dengler | last post by:
Hi all! I am doing some trigger functions that need to find a tuple in another table. The problem is that this second table is doing some summarization work, and I need nulls to equal each...
4
by: Gerry Abbott | last post by:
Hi All, Im trying to use thie combination but have not had success. Below is the function It tried the following myriskLevel(2,2) myrisklevel(0,0,2) and the ismissing(Three) alwasy...
1
by: john | last post by:
Relatively new to C coding, so any help would greatly be appreciated. I'm having problems try to return my string array from my parsing function. When I do a printf I am getting the correct value...
2
by: Frank Millman | last post by:
Hi all Please read the following function. The idea is to pass it a value, and the key to another table. If the value is not null, return the value. If the value is null, look up a default...
2
by: ChasW | last post by:
I just read these 2 pages. These are most helpful, but leave me with a question. http://www.mvps.org/access/queries/qry0005.htm http://www.mvps.org/access/forms/frm0007.htm I have a Multi...
28
by: Larax | last post by:
Best explanation of my question will be an example, look below at this simple function: function SetEventHandler(element) { // some operations on element element.onclick = function(event) {
27
by: Terry | last post by:
I am getting the following warning for the below function. I understand what it means but how do I handle a null reference? Then how do I pass the resulting value? Regards Warning 1...
5
by: Travis | last post by:
I am using a function that returns a const char * that is usually a word, etc. How can I check to see if what it returns is empty? I tried if (function() == "") and (function() == NULL) and...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.