ok, so i have a form with unbound check boxes on it, i want to alter a query criteria based on the check boxes values,so i have this as an example of my query criteria: - IIf([Forms]![frm_Reports Credit]![SS 1]=True,>0,Null)
SS 1 is the check box I'm trying to evaluate.>0 is what i want the query criteria to be if the box is checked, Null is the value i want the criteria to be if the box is unchecked.
The statement i have now runs, but returns no results.
I'm pretty sure that when I have done this that I included the whole criteria in the IIF statement. For example - WHERE IIF(Forms![frm_Reports Credit]![SS 1]=True
-
, MyField > 0
-
, MyField Is Null)
7 10267
I'm pretty sure that when I have done this that I included the whole criteria in the IIF statement. For example - WHERE IIF(Forms![frm_Reports Credit]![SS 1]=True
-
, MyField > 0
-
, MyField Is Null)
NeoPa 32,556
Expert Mod 16PB
Absolutely right Seth :-) IIf() is a function that returns a value. The value can be used within SQL code but it cannot return SQL code itself as such.
However, wouldn't the result of the iif be in quotes, since it is being added to a SQL statement?
NeoPa 32,556
Expert Mod 16PB
No. The result would be a value certainly, but not necessarily a string. MyField > 0 and MyField Is Null are both expressions that return boolean values. As the WHERE clause in SQL actually works with boolean values - even though most of us are more used to seeing expressions that return boolean values - this code works fine.
Aaaaahahhhh, I think I get it now. So, in theory, one could simply say
And all records would be returned or
And no records would be returned?
Once again, I have learned something useful here on this forum. Thanks for your insight!
NeoPa 32,556
Expert Mod 16PB
Absolutely. That's a perfect understanding.
That's also why one of my favourite techniques using the Select Case statement in VBA, which makes it way more flexible, is to say : - Select Case True
-
Case X = 3
-
...
-
Case Y = "Bob"
-
...
-
End Select
Thanks Guys, you were all very helpful.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: neptune |
last post by:
I have a query where each customer has an or . Sometimes
both fields for a customer are populated, but if is null, then
will be populated and vice versa. I have a form,
, where I select a...
|
by: Triple7 |
last post by:
Hi there,
basically what I am trying to do (without using code as I don’t know how to do that with a code) is to retrieve the previous 5 days & the next 5 days (date wise)against a query1 that...
|
by: jslssuze |
last post by:
I have medium experience with Access but need help trying to create a query for the following scenario... any and all help will be greatly appreciated!
A user will select different items on an...
|
by: shreyasi |
last post by:
i create a page..there i use check box..how can i call perticular quary from mysql using the check box.i want if i select a checkbox and submit the form then the corrosponding record will be...
|
by: pipestew |
last post by:
I have a form which allows me to enter a value and then view a report based on that value.
The value is currently entered as P-##### and the query criteria is...
!!
I want the Form to have...
|
by: hbaf208 |
last post by:
I have an option group (TimeFrameOption) with three options: All, Last 6 months, and Last 12 Months. When an option is selected, I want to query dates in a listbox (CallReport) to display all dates...
|
by: Nick Cooper |
last post by:
I wish to write a query criterion using expression builder to either pull out one record if another form is loaded, or all records if it isn't. I can't get this code to work.
...
|
by: Phil Dell |
last post by:
Hi all,
Extremely new to Access and VBA, I have been working my way through several books and sites, and I'm finally breaking down and crying for help.
I am developing a...
|
by: Jerry Maiapu |
last post by:
I have about 3 to 4 queries that a filtered using form fields.
It works perfectly on my laptop but does not work on other laptop.
I am using MS access 97. All other laptops are using access 97...
|
by: postman |
last post by:
I have a report based on several criteria that the user can select in listboxes on a form.
Each of these criteria have an "ALL" option which, if selected, will prevent that criteria from being...
|
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...
|
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: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |