IIF Function Not Returning The False Value
Jan 16, 2006
IIF function not returning the false value. I have the following results based on the query shown below.
SELECT Trade.Ref_No, Trade.CERS_Price, Market.[USD/EUR_Rate], IIf("CERS_Price_currency = US Dollar.USD",[Trade].[CERS_Price]*[Market].[USD/EUR_Rate] , 0) AS [Price In Euro], Market.[USD/CHF_Rate], Trade.CERS_Price_Currency
FROM Status INNER JOIN (Market INNER JOIN Trade ON Market.MarketDate = Trade.Trade_Date) ON Status.StatusID = Trade.StatusID;
Price In Euro USD/CHF_Rate CERS_Price_Currency CERS_Price
35 7 US Dollar. USD 5
91 7 Euro. EUR 13
1,715.00 7 Brazil Reais. BRL 245
759.00 33 Indian Rupees. INR 23.00
As you can see returns all calculations whether is USD or not.
Can you help please?
Ultimately I want to do this for all other currencies by nesting the IIF function.
Thanks
dfuas
View Replies
ADVERTISEMENT
Mar 9, 2007
I'm really not sure how to go about this. I'm creating a course booking system and when creating a booking I need to check for current bookings with the same employee and course id's (i.e. the employee is already booked on the course). The query takes the employee and course id's from a form, and is initialised when the 'book' button is pressed. It correctly selects if the person is already booked on the course but I want the query to return a value to the form i.e. if it returns null/false then the booking can be created but if it returns a record/true then the booking already exists and a message box can be displayed.
I'm not sure if I'm going about this the right way, can anyone suggest how this can be done as described above or suggest a better way of doing the task?
View 5 Replies
View Related
Jan 10, 2012
I've built an IFF expression that is determined by a number of variables.
If any of the Data fields are filled I want the statement to return true.
If NONE of the four are filled in I want it to return false.
As it is, it returns true no matter what.
Here is the expression I'm using:
Title: IIF (IsNull([Data1] and [Data2] and [Data3] and [Data4]), True , False)
View 7 Replies
View Related
Apr 9, 2013
I have an access query where i want to write a syntax to return US if my table field is blank, but return the original field characters if not blank
this is what i have so far, not sure how to specify return original characters if not blank?
=IIF (JOBSITE COUNTRY=BLANK,"US", )
View 1 Replies
View Related
Dec 12, 2006
Hi, I've used the following code in the Criteria line in a query (Access 2000)and the false condition doesn't seem to work. If I replace the false condition >0 with any other single value (say 1) it works fine.
IIf([Forms]![frmReports]![cboServArea]>0,1,>0)
I'd be grateful for any help,
Thanks
View 5 Replies
View Related
Dec 29, 2006
Greetings,
I've been banging my head on this for days, and what really makes this difficult is the number of websites which say it should work just fine!
I'm working with Access 2003 and trying to make use of the IIf function. I have given my fields generic names in the examples below.
I have a number of items I want to search upon in my search form. Each item has a check box and a combo box. I want the query to look at the check box. If the box is checked, I want the query to use the value in the combo box. If the check box is UN checked, I want the query to ignore this field and return all values. I thought this was simple, until I tried to do it!
Here is my code, placed in the Field-name criteria box of the query:
IIf([Forms]![form-name]![checkbox-name]=True,[Forms]![form-name]![combobox-name])
Now I know that the function works, because I can manually add a value:
IIf([Forms]![form-name]![checkbox-name]=True,[Forms]![form-name]![combobox-name],2)
This will return all records where Field-name = 2.
So... my original code should work! Because no "false" condition is stated, the query should be blank, and should return all records. But it doesn't! Truly maddening.
Here are some attempts which did not work:
IIf([Forms]![form-name]![checkbox-name]=True,[Forms]![form-name]![combobox-name],>0)
---> The ">0" caused an error. In fact, I can only specify an integer - all else, including things with operators, do not work.
Working with nulls - it does not seem to recognize an unchecked box as a null.
That's the short version of the story - any and all ideas welcome!
Regards,
-- Mark
View 14 Replies
View Related
Oct 7, 2013
I have a calculated field (Bhours) with the following IIf function:
=IIf(Fields!BDate.Value<=Parameters!Pdate.Value and Fields!EDate.Value>Parameters!Pdate.Value,Fields!H ours.Value,0)
It works perfectly, but I don't like the fact that it has a zero for the false part as I like the field to the blanc instead of filled with a zero.I can't use a space as I need to calculate with this field later on. I tried using "", but then the field displays #error".
View 7 Replies
View Related
May 15, 2007
Item: Access app front end for an SQL database (production tracking/job logging utility)
Problem: The app has a main search page that allows job entries to be located and opened by varies criteria (job#, job name, etc). I can currently run multiple searches on different criteria successfully but if I open a job window(which has been returned as result of my search) and then close that job window to do a new search my search function will no longer return any results(other then “Calculating….”) unless I close out the access app and reload.
Thanks in advance for any ideas that can point me in the right direction to hunt down my bug.
View 1 Replies
View Related
Jan 13, 2015
I have just added a function to a database to strip out any commas or quotation marks from a passed string.
However, it is returning a black string even though when I step through the function it creates the out string correctly and the variable "OutString" is populated when the Exit Function command is executed.
Calling code.
InString = Btxt
OutString = ""
Call Strip_String(InString, OutString)
SBtxt = OutString
[Code] .....
View 4 Replies
View Related
Jan 13, 2014
I'm trying to do a string compare between two variables. One string variable is part of an array (which I'm looping through), the other is passed to the function as an argument.the function should return the position of a field in the OrderBy string of a subform.Here's the VBA :
Code:
Private Function SortPosition(strOrderBy As String, strField As String) As StringDim arrSortedFields() As String
Dim i As Long
If Len(strOrderBy) > 0 And InStr(strOrderBy, strField) > 0 Then
arrSortedFields = Split(strOrderBy, ",")
[code]....
The weird thing is, the line in blue returns True when, by watching the variables, it appears that it should not, and returns False when it appears that it should?
arrSortedFields(i) = "[RandomField]"
strField = "RandomField"
arrSortedFields(i) Like "[" & strField & "]*" = False
arrSortedFields(i) = "[SomeFieldName]"
strField = "ADifferentField"
arrSortedFields(i) Like "[" & strField & "]*" = True
I would have thought the expression "[RandomField]" Like "[RandomField]*" should return True?And similarly the expression "[SomeFieldName]" Like "[ADifferentField]*" should return False?Have been using 'Like' for donkey's years and never seen it throw results like this before?
(P.S. The reason I need to use 'Like' rather than a straight = is to account for the possibility that a field may be sorted descending, and therefore to nullify the DESC keyword which may follow any given field...)
View 9 Replies
View Related
Dec 8, 2014
I have a table EmployeeInfo containing three fields
EmployeeId
Name
FatherName
I have created form for this table.
I do not want to use autonumber for employeeid for some reasons. I want to place a button along employeeid test box on form.
User will click on the button it will get max employee id from employeeinfo table and add 1 and copy it into employee id text box.
What will be the code for button click event.
View 2 Replies
View Related
Sep 13, 2013
I made a custom function to look certain value from table based on couple of criteria that it gets from query where I want to use it. Function's code is below:
Code:
Public Function PotteryWeights(strLocusID As Long, nrPotSubID As Long) As Variant
Dim priSubID As Long
Dim priLocusID As Long
Dim priResult As Variant
priSubID = nrPotSubID
[Code] ...
However, when I use it in query it only returns Case else - option and everything else is empty.
View 7 Replies
View Related
Aug 6, 2007
i have a little problem, at least, i think it is little.
Within my access db form i have made a combobox for searching a number. When selected the applicable information will be shown in the detail part of the form.
I made a button in the detail section, to allow people to change the information.
However, after i placed the button with the allowedits = false and true part to autorise if people may or may not change the information, the combobox for searching a number only works when i first press the button [change] (so that the property is set on allowedits = true), then search the number by the combobox. Directly after that the combobox is not allowed to be used(!?)
What do i want?
- combobox is allowed to be used always
- information in detail section only is allowed to be changed when pressing the [change] button.
I am not sure how to do this, please help me if you want? :confused:
View 2 Replies
View Related
Jun 20, 2005
I've got a form which shows all the records in a list, with a check box next to each which is for choosing which ones to print. i have created a "de-select all" button, using a macro with simple SQL behind it -
"UPDATE companys SET print = false WHERE print = true"
when i test this button, it sets all the boxs to false except for the last one that was set to true. it also doesnt update the form straight away, sometimes the checks say until you scroll so the record is no longer on screen.
does anyone know how to rectify these problems?
Thanks
View 1 Replies
View Related
Aug 7, 2006
Hi guys, here is my problem. thx 4 hlping me.
i created a form to work as a menu. At this point the menu has only one option (File) which is a text box. Once the user click on the text box FILE, a list box appears below, leting the user select between a few options. When the user clicked one of the options , a new forms comes (popup and modal in yes).
The problem is that when the second form is closed, the menu still has the list box visible. I would like to make it no visible but i dont know how.
any clue?
thx, max.
View 2 Replies
View Related
Oct 28, 2006
How do!
First post, spent a long time trying to find an answer but to no avail
How would you add a simple way to turn the value of a specific checkbox in each record in the DB to false?
Very nice place you have here, I hope I will be able to return some information at a later date :D
Cheers
Wmffra
View 3 Replies
View Related
Nov 1, 2004
Hi
I have a subform with several records entered. When a particular entry is made in the subform
eg. chocolatebiscuit
I want the subform to not allow any more entries - records in that particular subform
Is there a easy way to do this?
I have tried to do this by saying
Me.Form.allowadditions = false
Unfortunately this works on the whole form
and stops the next subform - belonging to the next main record
from adding a new record.
How do I restrict the new records in A particular subform?
I tried
Me.Form.allowadditions = true
on entering the subform
but it doesnt work every time.
Also i put on enter
if not chocolatebiscuit then
allow entries
but for new records it takes the last entry in the subform
which could be two records back in the main form
says yes there is a chocolatebiscuit
and will not allow a new record.
View 3 Replies
View Related
Jul 17, 2005
Hi,
Is it possible to add a new record thorough VBA (for a command button) with the AllowAdditions property set to false?
I only want new records created if this command button is pressed.
TIA!
View 8 Replies
View Related
May 12, 2006
I have a query that shows banned users (I work in a school). When a student's ban has ended, a tick box is checked in a form linked to tblBannedUsers to show they are no longer banned.
The query itself simply queries all records in tblBannedUsers with a username field (UserID) taking search criteria from a box on a form.
However, I want to filter out the students that are no longer banned (those with a tick in the checkbox). To do this, I thought it would be a simple case of editing the query, and in the Ban Lifted field criteria, use =False to say I only want those records with a tick.
This doesn't work and I still get all records given the combo box filter (which are just filtering for a username...if I leave it blank it gives all records via Is Null).
What should I be putting in the criteria to filter out those records with a ticked checkbox?
Thanks,
Steve Swallow
EDIT: I've just done a test query and <B>No</B> is the criteria to use, but when I use this criteria in my query which also take data from a form's combo box it ignore the <B>No</No> criteria.
View 4 Replies
View Related
Feb 17, 2005
This seems really straight forward to me. I have 3 forms - FormA, FormB and FormC. When I open FormA the user inputs some information, when they tab to the last field, FormB opens (which I want to be invisible) and Form C opens.
I've tried putting this coding on the Open event of FormB:
Me.Visible = False
I've also tried putting this code on the Open event of FormC
Forms!FormB.Visible= False
Neither work. My form (FormB) still displays when eithr event occurs.
Any suggestions?
Cindy
View 6 Replies
View Related
Jun 15, 2005
I have a form with many command buttons. This form is a master for a database that keeps time for my department. One command button is a setup button that I want the user to run once and only once. What code, and where do I put it will allow my to set the command button's visible or enabled to be false. Even when the user closed the database and reopend it, if the user has run the setup once, I do not want to allow them to run it again.
Thanks.
View 5 Replies
View Related
Jun 2, 2006
Is it possible to filter a forms records by using a boolean True/False field.
I want to show all current records for option 1, Expired records for option 2, and all records for option 3. The form is based on 'tblMembers' which has a field [Expired] which is a boolean yes/no field formatted to true/false.
View 1 Replies
View Related
Nov 7, 2006
Hi
I was just wondering if it was possible to set the Allowedits setting to false whenever someone moves to a new record.
I currently have Allowedits = No by default on form open, and an "Edit" button which sets allowedits to Yes. However, I want the form to go to Allowedits=No again when the user scrolls to a new record.
Also, is there a way to set the form so that if someone makes changes to a record, the change doesn't automatically save unless you press a save button.
Thanks
Natasha
View 1 Replies
View Related
Jul 9, 2015
SELECT tbl_Visits.vst_VisitDate, tbl_Visits.vst_Complaint, tbl_Visits.vst_Diagnosis
FROM tbl_Visits
WHERE (((tbl_Visits.vst_Complaint) Like "*asthma*")) OR (((tbl_Visits.vst_Diagnosis) Like "*asthma*"));
returns records with no occurrences of asthma. about 4 of 638 hits.
View 14 Replies
View Related
Nov 1, 2014
I have a combobox, and the 'OnNotInList' event has code to show a message box, then set focus to another control. That all works fine, however I am still getting the system generated 'Not in List Warning', despite the code for the not in list event starting with DoCmd.Setwarnings (False)
How to disable the warning?
View 2 Replies
View Related
Sep 19, 2014
There are around 100,000 records to update. Would a SQL Statement be more efficient?
It is a local table being used to sum up the results of a handful of rules.
The columns can only hold True or False (datatype)
If and only if all columns are True - then true
MyRow T T T T T T T T T T - Sum in next column is T
MyRow T T T T T T F T T T - Sum in next column is F
Speed is very important. The Recordset for a single row is still open on the Currrent Record since the Update just finished.
Code:
RS_RE_1SegStatusProfiled.Fields("Total") = (RS_RE_1SegStatusProfiled.Fields("RE_1") AND CStr(RS_RE_1SegStatusProfiled.Fields("RE_2") AND (RS_RE_1SegStatusProfiled.Fields("RE_3") ' and so on
My guess is that since the recordset is open to the current record on a local table, it will be efficient to just re-read all of the values and And them together.
Since I have code writing to each record, I could also go through all the extra assignment of a local variable.
View 4 Replies
View Related
