472,378 Members | 1,234 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

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

IIf in query won't return false

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_RecipesSubform
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 tblRecipeIngredients, 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_RecipesSubform: (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 1497
Hi again..sorry forgot - I'm using access97.
Mar 6 '07 #2
159 Expert 100+
could you post the sql for you're query? it might help us find the problem
Mar 7 '07 #3
12,516 Expert Mod 8TB
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
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge required to effectively administer and manage Oracle...
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was proposed, which integrated multiple engines and...
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific technical details, Gmail likely implements measures...
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the synthesis of my design into a bitstream, not the C++...
by: Carina712 | last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand. Background colors can be used to highlight important...
by: Rahul1995seven | last post by:
Introduction: In the realm of programming languages, Python has emerged as a powerhouse. With its simplicity, versatility, and robustness, Python has gained popularity among beginners and experts...
by: Johno34 | last post by:
I have this click event on my form. It speaks to a Datasheet Subform Private Sub Command260_Click() Dim r As DAO.Recordset Set r = Form_frmABCD.Form.RecordsetClone r.MoveFirst Do If...
by: ezappsrUS | last post by:
Hi, I wonder if someone knows where I am going wrong below. I have a continuous form and two labels where only one would be visible depending on the checkbox being checked or not. Below is the...
by: jack2019x | last post by:
hello, Is there code or static lib for hook swapchain present? I wanna hook dxgi swapchain present for dx11 and dx9.

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.