469,347 Members | 19,529 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,347 developers. It's quick & easy.

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 1315
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

Post your reply

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

Similar topics

reply views Thread by Phil Powell | last post: by
10 posts views Thread by johnnyboy10017 | last post: by
4 posts views Thread by shumaker | last post: by
2 posts views Thread by Enterprise | last post: by
2 posts views Thread by Seth Delaney | last post: by
6 posts views Thread by Larry R Harrison Jr | last post: by
25 posts views Thread by frizzle | last post: by
11 posts views Thread by John Ortt | last post: by
24 posts views Thread by MU | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.