Change value of field to 0 if specific condition exists on oth

Similar Threads:

1.Change value of field to 0 if specific condition exists on other f

I have a query that has a field with a calculation that is a sum of two other 
fields.  I also have another field that detirmines the type of item within a 
record, specifically stocking(displays as -1) and non-stocking (displays as 
0). 

Have tried variations of   IIF([immaster].[stocking] = 0, 0, 
[Field1]+[Field2])
in criteria of fileds and as new fields, but my record count changes from 
20,783 to 382 or I get no results.

Is there an expression I can use in the criteria of the field that is the 
sum of two fields that will default the sum result to zero if the item type 
field is non-stocking?  Here is the SQL statement if this helps explain what 
I am trying to do:

SELECT [PI Freeze].item, [PI Freeze].descrip, [PI Freeze].prodclas, 
immaster.stocking, [PI Freeze].[Freeze Qty], [MAST CNT].Count, Nz([MAST 
CNT].[Count],0) AS [Count Qty], ([Count Qty]-[PI Freeze].[Freeze Qty]) AS 
[Variance Qty]
FROM ([PI Freeze] LEFT JOIN [MAST CNT] ON [PI Freeze].item = [MAST 
CNT].item) INNER JOIN immaster ON [PI Freeze].item = immaster.item
GROUP BY [PI Freeze].item, [PI Freeze].descrip, [PI Freeze].prodclas, 
immaster.stocking, [PI Freeze].[Freeze Qty], [MAST CNT].Count, Nz([MAST 
CNT].[Count],0), ([Count Qty]-[PI Freeze].[Freeze Qty]);

Not quite sure how to create this in a new column.

thanks,
Kristi

2.Changing value to 0 if condition exists in another field of re

Fast fingers ---
  IIF([immaster].[stocking] = 0, 0, [Field1]+[Field2])

-- 
KARL DEWEY
Build a little - Test a little


"KARL DEWEY" wrote:

> You lost me but here is a guess --
>   IIF([immaster].[stocking] = 0, 0, [Field1]+[Field1])
> -- 
> KARL DEWEY
> Build a little - Test a little
> 
> 
> "Kristibaer" wrote:
> 
> > I have a query that has a field with a calculation that is a sum of two other 
> > fields.  I also have another field that detirmines the type of item within a 
> > record, specifically stocking(displays as -1) and non-stocking (displays as 
> > 0). 
> > 
> > Is there an expression I can use in the criteria of the field that is the 
> > sum of two fields that will default the sum result to zero if the item type 
> > field is non-stocking?  Here is the SQL statement if this helps explain what 
> > I am trying to do:
> > 
> > SELECT [PI Freeze].item, [PI Freeze].descrip, [PI Freeze].prodclas, [PI 
> > Freeze].[Freeze Qty], Nz([MAST CNT].[Count],0) AS [Count Qty], ([Count 
> > Qty]-[PI Freeze].[Freeze Qty]) AS [Variance Qty], immaster.stocking
> > FROM ([PI Freeze] LEFT JOIN [MAST CNT] ON [PI Freeze].item = [MAST 
> > CNT].item) INNER JOIN immaster ON [PI Freeze].item = immaster.item
> > GROUP BY [PI Freeze].item, [PI Freeze].descrip, [PI Freeze].prodclas, [PI 
> > Freeze].[Freeze Qty], [MAST CNT].Count, Nz([MAST CNT].[Count],0), ([Count 
> > Qty]-[PI Freeze].[Freeze Qty]), immaster.stocking;
> > 
> > thanks,
> > Kristi
> > 

3.Changing value to 0 if condition exists in another field of record

I have a query that has a field with a calculation that is a sum of two other 
fields.  I also have another field that detirmines the type of item within a 
record, specifically stocking(displays as -1) and non-stocking (displays as 
0). 

Is there an expression I can use in the criteria of the field that is the 
sum of two fields that will default the sum result to zero if the item type 
field is non-stocking?  Here is the SQL statement if this helps explain what 
I am trying to do:

SELECT [PI Freeze].item, [PI Freeze].descrip, [PI Freeze].prodclas, [PI 
Freeze].[Freeze Qty], Nz([MAST CNT].[Count],0) AS [Count Qty], ([Count 
Qty]-[PI Freeze].[Freeze Qty]) AS [Variance Qty], immaster.stocking
FROM ([PI Freeze] LEFT JOIN [MAST CNT] ON [PI Freeze].item = [MAST 
CNT].item) INNER JOIN immaster ON [PI Freeze].item = immaster.item
GROUP BY [PI Freeze].item, [PI Freeze].descrip, [PI Freeze].prodclas, [PI 
Freeze].[Freeze Qty], [MAST CNT].Count, Nz([MAST CNT].[Count],0), ([Count 
Qty]-[PI Freeze].[Freeze Qty]), immaster.stocking;

thanks,
Kristi

4.using query conditions to return a specific value or all value

Thank you very much Ken
Your help is much appreciated!
AC

"Ken Snell [MVP]" wrote:

> Use this as the criterion expression:
> 
> [Forms]![FormName]![ComboBoxName] Or [Forms]![FormName]![ComboBoxName] Is 
> Null
> -- 
> 
>         Ken Snell
> <MS ACCESS MVP>
> 
> 
> "ac512" < XXXX@XXXXX.COM > wrote in message 
> news: XXXX@XXXXX.COM ...
> > using Access 2002...
> > I am trying to construct a query, using criteria which is selected from a
> > combo box on a form.  If a value is selected in the combo box, then the 
> > query
> > will return only the value selected, but if no value is selected in the 
> > combo
> > box (therefore the combo box has a null value) then the query will return 
> > all
> > values.  Using an IIF statement in the query criteria field, I can get the
> > query to return the appropriate results if a value is selected from the 
> > combo
> > box, but I am not able to return all values if nothing is selected in the
> > combo box 
> 
> 
> 

5.using query conditions to return a specific value or all values

using Access 2002...
I am trying to construct a query, using criteria which is selected from a 
combo box on a form.  If a value is selected in the combo box, then the query 
will return only the value selected, but if no value is selected in the combo 
box (therefore the combo box has a null value) then the query will return all 
values.  Using an IIF statement in the query criteria field, I can get the 
query to return the appropriate results if a value is selected from the combo 
box, but I am not able to return all values if nothing is selected in the 
combo box

6. Apply field value from subform to field value from another subform with condition

7. Changing an existing field to VarChar type also changes the field width

8. Changing an existing field to VarChar type also changes the field width