By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,666 Members | 1,544 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,666 IT Pros & Developers. It's quick & easy.

IIf in query won't return false

P: 41
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:
frm_Recipes:
Main form for entering/viewing recipes. Recordsource is tblRecipes. Has a subform Ė frm_RecipesSubform
frm_RecipesSubform:
Continuous forms subform on frm_Recipes - shows the ingredients for the recipe displayed on main form.
FrmIngredients:
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)
[Quantity]:
User entered quantity eg 1/2, 1, 1 1/2
[comboMeasure]:
Type of measure eg cup, g, tsp
[comboIngredient]:
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.
[CalcQ]:
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.
[subRecipeCost]:
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.
[IngredCost]:
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
Share this Question
Share on Google+
4 Replies


P: 41
Hi again..sorry forgot - I'm using access97.
Mar 6 '07 #2

MSeda
Expert 100+
P: 159
could you post the sql for you're query? it might help us find the problem
Mar 7 '07 #3

Rabbit
Expert Mod 10K+
P: 12,430
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

P: 41
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.