473,732 Members | 1,903 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

IIf in query won't return false

41 New Member
Hi..I’ve been teaching myself access through trial and error and google, but now I am completely confused and STUCK!
These are the relevant forms in my database:
Main form for entering/viewing recipes. Recordsource is tblRecipes. Has a subform – frm_RecipesSubf orm
frm_RecipesSubf orm:
Continuous forms subform on frm_Recipes - shows the ingredients for the recipe displayed on main form.
Used for entering and editing ingredients and their details (package cost, package size, grams per cup). Recordsource is tblIngredients.

Originally I had my subform’s recordsource as tblRecipeIngred ients, and used vba to do all the calculations, but I was storing a lot of calculated values, and I want them to update whenever any relevant value changes. After a LOT of research I found out that this was wrong so have been trying to base the subform on a query, Query3 instead. Everything seems to work well, except for the IIf statement in my query.

frm_RecipesSubf orm: (relevant fields only)
User entered quantity eg 1/2, 1, 1 1/2
Type of measure eg cup, g, tsp
On NotInList, ingredient is entered into tblIngredients, and frmIngredients is opened to allow details used in costing calculation to be entered (PackageSize, PackageCost, GramsPerCup – CostPerUnit is calculated after update of either of the first two). After this form is closed I have a requery on Activation of the subform, to update the calculated fields.
After update of Quantity or comboMeasure, values are changed to decimal (0.5, 1, 1.5 etc), and this is divided by the relevant amounts depending on whether it is cups , tsp or whatever, through VBA and stored in CalcQ. I understand about not storing calculated values, however the quantity and measure fields will only ever be entered or changed via the subform.
In form footer - =Sum([IngredCost]) This will be passed to the main form and stored (I know, stored values, *sigh* ;) ) so that I can search by recipe cost in another form.
I need to use one of two different formulas to calculate IngredCost depending on the value of [Measure]:
IIf([Measure]="cup" Or "cups" Or "tsp" Or "tbsp",[CalcQ]*[GramsPerCup]*[CostPerUnit],[CalcQ]*[CostPerUnit])
I just can’t get this to work, it only evaluates the expression as true, never as false. I’m guessing that it is looking at all records in the field [Measure], instead of each individual row..not the required effect at all. What is wrong here? Is there a way to achieve this?
First I had CalcQ as just the decimal version of [Quantity], with a bunch of nested IIf’s in my query to do the relevant division (for tsp, tbsp) but changed it because I thought the problem was with nesting the iif’s.
Any help would be very much appreciated, as I said I am absolutely stuck. Sorry this is long but I wanted to (try to) be as clear as possible :)
Mar 6 '07 #1
4 1614
41 New Member
Hi again..sorry forgot - I'm using access97.
Mar 6 '07 #2
159 Recognized Expert New Member
could you post the sql for you're query? it might help us find the problem
Mar 7 '07 #3
12,516 Recognized Expert Moderator MVP
I spotted one error from a quick glance. You actually can't use:
Expand|Select|Wrap|Line Numbers
  1. [Measure] = "cup" Or "cups" Or "tsp" ...
What you have to do is:
Expand|Select|Wrap|Line Numbers
  1. [Measure] = "cup" Or [Measure] = "cups" Or [Measure] = "tsp" ...
Mar 7 '07 #4
41 New Member
Thanks both of you so much for replying so fast.
I have applied the changes suggested by Rabbit, and the query now works! And such a simple solution too! Thank you very much. These forums really are a great way to learn.
Mar 7 '07 #5

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

Similar topics

by: Phil Powell | last post by:
I have a very simple mySqlQuery object that takes two parameters: 1) the string query 2) the db connection resource I tested and was certain everything is passing correctly (the string query and the db connection resource). The first mySqlQuery object produces just fine; but when I run it, the second mySqlQuery object (a totally different SQL query, same db connection resource) comes back null every time, even when there is data...
by: johnnyboy10017 | last post by:
I am having a hell of a time with what I think is a very simple query: It won't actually insert a new record into the specified table, but returns no error, in fact it returns "1" (or true) that the query was successful. Things to know: 1. id field is auto-incrementing primary key 2. Other queries SELECT, UPDATE, and DELETE all work no problem 3. INSERT works via phpMyAdmin 4. Query generated by phpMyAdmin and pasted into my PHP...
by: shumaker | last post by:
I'm wondering how/why this query works. Trying to get my head wrapped around SQL. Basically the Query deletes from the Import table all records that are already in FooStrings so that when I do an insert from the FooStringsImport table into the FooStrings table, then I won't get primary key violations. DELETE FROM FooStringsImport WHERE EXISTS (SELECT * FROM FooStrings WHERE FooStringsImport.FooKey = FooStrings.FooKey)
by: Enterprise | last post by:
I'm stuck. I use Access 2000. I have a date field in a query. What I need done is if a Limit_Date function returns a true, I want the date field to be Between 1/1/02 and 1/1/03, otherwise I want it left alone(all records shown). I have an if statement in the criteria: iif(Limit_Date()=true,Between 1/1/02 and 1/1/03,"*") This does not work since the if statement returns strings. So if Limit_Date is true, then the criteria is "Between...
by: Steve | last post by:
Access 97. I have a form where there is an option group with two buttons, and a combo box. The combo box Row Source is a query. The option group has two options a) include a subset of the query where all records with the field OPEN set to Yes, or return all records in the query (OPEN = Yes or No). If I "manually" set the criteria to 1 (yes) or 1 Or 2, I get the correct records returned. However, I want to use the option group
by: Seth Delaney | last post by:
I have a form with multiple unbound text boxes which serves as a "search form". I can enter my search parameters in the various boxes as needed and click okay. My records are then filtered to produce the results I want in a separate form (filter by form). No problem, except I want to save the sql to a query, not a form. When I debug.print the various sql, I get something like this example: (( LIKE "cam*")) which obviously is not enough...
by: Larry R Harrison Jr | last post by:
I have a database I'm designing in Access 97. I have a custom field in a query which looks in {Table of Documents} and shows them all. It then needs a "latest revision number," stored in another table named {Table of Revisions}. It naturally matches them up by linking the autoid in {Doc} with the related field in {Rev}. It then looks for a field in {Rev} called "revision number" and looks for the last one for the given Doc (linked by the...
by: frizzle | last post by:
Hi there, I have a mySQL system with a news publishing part in it: Admins can create new items with text in it, and they have an option to create 'fulltexts', so you'd get "read more ..." on the front page, click it and read the fulltext. Is there a possibility for mySQL (query) to check if 'fulltext' is empty or not, and only return true or false, so i don't have to put the whole fulltext into the mysql_fetch_array() to decide wether...
by: John Ortt | last post by:
Hi everyone. I have a database which I have developed in Access 2000 which is working nicely. The problem is that my customer only has Access 97. I tried to convert the database but the main menu won't work. I has two parts, a mainmenu and a submenu which displays the option buttons.
by: MU | last post by:
Hello I have some code that sets a dropdownlist control with a parameter from the querystring. However, when the querystring is empty, I get an error. Here is my code: Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load If Not Page.IsPostBack Then
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.