IIF statement pulling from wrong field

I have a query have has the 5 variations of the following IIF statement: Bag
Total: IIf(" " & [Unit of Measure] & " " Like "* BAG *",[Qty],Null) this
statement says to add up if the unit of measure if it is a bag.
I am attempting to add another IIF statement that says =Sum(IIf([product type]
Like "*printed*", [pounds], 0)) - I am trying to add up if the product type
has the word printed in it total it at each customer and at the end of the
report.
When I use this IIF statement on my report, it is totalling the amount from
the printed IIf(" " & [Unit of Measure] & " " Like "* BAG *",[Qty],Null)
amount, not the pounds column. 

The product type is a text field, the pounds field is a number field.

I need help writing the proper IIF statement, changing the other IIF
statements, or defining how the fields are defined.  Thank you.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201002/1

0
jligue
2/19/2010 4:52:37 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
779 Views

Similar Articles

[PageSpeed] 35

see in-line comments
-- 
Daryl S


"jligue via AccessMonster.com" wrote:

> I have a query have has the 5 variations of the following IIF statement: Bag
> Total: IIf(" " & [Unit of Measure] & " " Like "* BAG *",[Qty],Null) this
> statement says to add up if the unit of measure if it is a bag.
> I am attempting to add another IIF statement that says =Sum(IIf([product type]
> Like "*printed*", [pounds], 0)) - I am trying to add up if the product type
> has the word printed in it total it at each customer and at the end of the
> report.
> When I use this IIF statement on my report, it is totalling the amount from
> the printed IIf(" " & [Unit of Measure] & " " Like "* BAG *",[Qty],Null)
> amount, not the pounds column. 

If you want the pounds column totalled for this, use this:
IIf(" " & [Unit of Measure] & " " Like "* BAG *",[pounds],Null)

> 
> The product type is a text field, the pounds field is a number field.
> 
> I need help writing the proper IIF statement, changing the other IIF
> statements, or defining how the fields are defined.  Thank you.
> 
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201002/1
> 
> .
> 
0
Utf
2/19/2010 6:46:01 PM
It is working, MANY thanks!

Daryl S wrote:
>see in-line comments
>> I have a query have has the 5 variations of the following IIF statement: Bag
>> Total: IIf(" " & [Unit of Measure] & " " Like "* BAG *",[Qty],Null) this
>[quoted text clipped - 6 lines]
>> the printed IIf(" " & [Unit of Measure] & " " Like "* BAG *",[Qty],Null)
>> amount, not the pounds column. 
>
>If you want the pounds column totalled for this, use this:
>IIf(" " & [Unit of Measure] & " " Like "* BAG *",[pounds],Null)
>
>> The product type is a text field, the pounds field is a number field.
>> 
>> I need help writing the proper IIF statement, changing the other IIF
>> statements, or defining how the fields are defined.  Thank you.

-- 
Message posted via http://www.accessmonster.com

0
jligue
2/19/2010 7:31:34 PM
Reply:

Similar Artilces:

if, and statement
I need to do an if and statement something like this- if A1=1 and A2x(A1)1>10 then no more than 10, but if A2=2 and A2xA1>25 then no more than 25 so if A2= 12 and A1=1 then the result will be 10 but if A2= 12 and A1=2 then the result will be 24 but if A2= 15 and A1=2 then the reult will be 25 I know it is a lot, but you can guys figure it out. Try this: =MIN((A1=1)*A2,10)+MIN(2*(A1=2)*A2,25) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------...

Memo Field in Form
I created a notes section in a form I built. The notes section is connected to a memo field in the table itself because I need the ability to type several thousand records in that field. The memo field doesn't allow me to seperate my notes into paragraphs by pressing the enter button. I need to format its contents and seperate each section/paragraph by a space. Currently, Access only allows me to type one continuous string of text. Is this possible? I'm not on a computer with Access at the moment, but isn't there a property for the memo field on your form that is called some...

Group same type of field from 3 different queries
Can anyone help me with this? I have 3 different queries each summing up a value for a given month. Past sales, present sales, future sales, these only have two fields Month and value. What I want to do is create something like Month Past Present Future ------------------------------------- Jun 5278 3424 3454 Jul 5625 6254 5462 Jun 2562 5264 5262 Where it will merge the two values for Jun, this will account for more than one product showing a summary of all sales. Ive tried using crosstabs queries, nested queries, even writing ...

Mutual fund transaction download as wrong Mutual fund
When I download my transaction from my Fidelity IRA Money downloads Fidelity Diversified International (FDIVX) as American Century Equity Income Inv (TWEIX). What would cause this and how can I fix it? I think this originally came in as an unidentified transaction and was accidentally matched with TWEIX. How can this be fixed? ...

e-mail address in To Field
Hi, I changed out a users laptop and he had over 100 e-mail address in the To field that were not saved in his contacts. Is there any way that I can export them to his new laptop? They are contained in the 'username'.nk2 file "MR" <MR@discussions.microsoft.com> wrote in message news:77118046-5742-4EB1-949D-9E04487A7D2A@microsoft.com... > Hi, > > I changed out a users laptop and he had over 100 e-mail address in the To > field that were not saved in his contacts. Is there any way that I can > export them to his new laptop? ...

If I have lots of RAM why can't I pull in large data sets?
I have over 2Gb of RAM and over 13Gb of free hard drive space. So why can't I pull in more data? I get an error stating that I don't have enough available resources so close some applications (which I've done) or choose less data (get real!). I open Task Manager and it shows that I have megabytes of RAM and page file space still available! What gives? Microsoft Query gives the same message, like when I'm getting external data for a pivot table. Most of the time this isn't a problem but sometimes I have to pull a lot of data but Excel just doesn't want to cooperate...

Downloading statement shows items to review but I can't find them (2007)!
The last few days, when I download my checking account statementm it succeeds and shows items to review (4 yesterday, 5 today) but the downloaded items are not showing up in the account. The last item in the register is from 11/21 (5 days ago). I have the current vesion (MSM Deluxe 2007, Version 16...1024). I've been using MSM for many years. Anyone have any ideas on this? Thanks, Gary In microsoft.public.money, GarDavis wrote: >The last few days, when I download my checking account statementm it >succeeds and shows items to review (4 yesterday, 5 today) but the >downloaded ...

Re Label Accounts Field
Anybody know how to change the Accounts field label to something else e.g. Organization, or Business? You can rename the 'account' entity to be 'Business'. Organization is a reserved word in CRM (there is already an orgainzation object) so it's off-limits. Dave "Owais" <Owais @discussions.microsoft.com> wrote in message news:25767F85-513C-40F7-A82F-F0E5A627D9FF@microsoft.com... > Anybody know how to change the Accounts field label to something else e.g. > Organization, or Business? ...

Macro and If Statement
Here's the situation: I have a worksheet that will have an "X" in either cell F3 or G3 or H3. What I would like to do (via a macro), is to examine F3, G3 and H3, and if there is an "X" in anyone of those cells, to run another specific macro. For example: Run a macro (for example called "totalsummary")to determine... If there is an "X" in F3, then run a macro called "summaryEL"; If there is an "X" in G3, then run a macro called "summaryHT"; If there is an "X" in H3, then run a macro called "summaryFA&quo...

IIF Statement syntax?
I have a timesheet template that allows a user to enter their Time In, Time out for Lunch, Time back In, Time Out for the day, then calculates the time differences to give total number of hours worked. I am trying to make it so that if the total time for the day is Negative, it assigns zero as the default. Here is the statement that I tried IIF(ROUND((((E7-E6)+(E4-E3))*24),2)<0, ROUND((((E7-E6)+(E4-E3))*24),2),0 Can anyone give me some help on what I've done wrong? I just get #NAME in the box Thanks for the help in advance Mac Could it be that IFF should be IF? Bernard "m...

how2: show contact name and phone fields in task list
It would be useful if I could display contact name and phone number fields in my task list. In Simple View I've used Field Chooser to insert these columns into the table .. but alas! the fields are blank. Can I do what I want to do and .. how? thanks Any folder view shows only the data in that folder, even though Outlook lets you think otherwise by allowing you to add fields from different types of items in the folder. You may be able to accomplish what you want, however, with a little custom VBA code. For an example, see http://www.outlookcode.com/codedetail.aspx?id=566 -- Su...

line chart displays wrong values
Yesterday my chart wizard went crazy A simple chart, I've done them thousands of times Now when I select 2 series and try to plot a 2 line chart the first series plots fine and the second plots as the sum of the two series. I tried shutting down, restarting, typing the data manually into a new sheet, sending the data to a colleague -- the result is the same HELP! -- koche005 It sounds like you created a stacked line chart. To change it: Select the chart Choose Chart>Chart Type Select the Line chart type Select the Line chart sub-type (at the top left) Click OK koche005 wrote: &...

Is it possible to sequentially number the comments field
Anyone out there clever enough to tell me how to sequentially populate the comments field in cells... the problem I can see is that the cells are in a grid of A5-10 through to i5 to 10, I want them populating a5,a6,a7,a8,a9,a10,b5,b6 etc and when I get to i10 I want the sequence to carry over to the next sheet... Don't want much do I ;) TIA Nick It looks like rather than "sequentially" populating the Comment fields, you are simply putting the cell's address into the Comment field. If that observation is correct, then I do not know what you mean when you said "when ...

IIF(AND) statement
Hi I have a number of things I need to test in the query. I need to use IIF statement with AND (for example in Excel you can do it),. Example I need this IIf (and(cat="HS",cat2="JTK",cat3="NT"),"1","2") how to do it in access? -- Greatly appreciated Eva On 7 apr, 21:09, Eva <E...@discussions.microsoft.com> wrote: > Hi > I have a number of things I need to test in the query. I need to use IIF > statement with AND (for example in Excel you can do it),. > Example > I need this > IIf (and(cat="H...

input text at front of field that contains data
Hi there, I have imported data into excel and I wish to input text ZZ in front of the orginal data...for example her is how the data looks like(to the left). Column A (how do I put a ZZ in front of the text without going through it one by one ________ so in a matter of seconds it should look like this(the column to the right) ABADES ZZABADES ABADEF ZZABADEF ABADET ZZABADET ABADER ZZABADER ABADEW ....and so on ....and so on I have 62...

IF statement question
Is it possible to test for more than 2 different conditions? ...example total number of hours paid hours 5 hrs 5 hrs 10 hrs 9.5 hrs 12 hrs 10 mins 12 hrs IF "total number of hours" <6 this will be "paid hours" ..... IF "total number of hours" >6 but <12 "paid hours" = "total number of hours" -.5 IF "total number of hours" >12 "paid hours" = "total number of hours" -.1 -----= Poste...

Multiple null fields
Here what im trying to do. If any of the three Text fields are null then display msgbox. It works fine with just one text box, but I can’t seem to understand how to look at three text fields. What’s the proper way to use multiple fields? If IsNull([Text65] Or [Text67] Or [Text69]) Then intSave = MsgBox("Are you sure you want to go to a new customer without inputing a Action?", vbYesNo + vbQuestion + vbDefaultButton2, "Save Confirmation") If intSave = vbYes Then DoCmd.Close DoCmd.OpenForm "FrmCustSearch" End If Else DoCmd.Close ...

Entered wrong closing amount
Is there a way to re-enter the closing amount if you accidently put too many zero's in it and didn't notice until you ran the Z Report? I have a client who has several closing amount errors they'd like to fix. Thanks! Isn't the lack of this ability a terrible shortcoming. Any add-ins or otherwise easy solution for this? A list of SQL statements to correct it? This happens far too often and I have to relearn the procedure and run SQL statements to correct. "Teri K" wrote: > Is there a way to re-enter the closing amount if you accidently put too many > ...

iIF clause
Can you help me build the right IIf field in the query with the following conditions : The field DDU consissts of : DDU :[exworks]*2+0,4+0,01 To the above expression i must also add 0,001 if size = 205, etc according to the following table : 205 0.001 60 0.001 20 0.009 1 1.32 4 0,32 0,5 1,67 However Acces does not accept my query, obvioulsy i have errors : DDU : [exworks]*2+0,4+0,01 + IIf([size = 205],0,001,[size = 60],001) Will you help me ? ...

wrong currency on fund
I entered a few mutual funds into MS Money+ and mistakenly put them into USD instead of EUR. I was able to switch the transaction back to Euro, but in the Account Summary all the prices for those funds still show in USD (although correctly converted from euros). I cant find any way to change the visuals back to EUR as well. Any ideas? It's like the fund itself is stuck on USD, even though the transaction is now in euros. Cor In microsoft.public.money, Cor Bosman wrote: >I entered a few mutual funds into MS Money+ and mistakenly put them into >USD instead of EUR. I was able to swi...

What Does +IIF Do as Opposed to IIF?
What Does +IIF Do as Opposed to IIF?James Igoehttp://code.comparative-advantage.com/ AFAIK, there's no such function as +IIf in VBA. If I had to guess, I'd say that the + is simply arithmetic: add the results of the IIf function to whatever preceeded it.How are you seeing it used?-- Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele(no e-mails, please!)<james.igoe@gmail.com> wrote in message news:1173285991.823665.303480@p10g2000cwp.googlegroups.com...>> What Does +IIF Do as Opposed to IIF?>> James Igoe> http://code.comparative-advantage.com/> On 7 Mar 200...

Zeros in empty merge fields
Hi! When I mail merge address info from a spreadsheet, the last merge field--empty zip code--does not close up. I have two zip code fields: one for physical address and one for P. O. Boxes. The last field shows up as a zero when empty. I tried setting the options by unchecking zero, but that doesn't fix the problem. I've also tried formatting the zip column to text and number to see if that would fix it, but don't remember that it fixed the problem. The zero doesn't really keep the mail from being delivered, but it would be nice if the blank field would just disap...

How do I populate a subform field with a value from the main form?
I have a form called ContractsIn which has a subform called ContractsInAndHours. The main part of the form consists of: Employees.ID Employees.Name The subform has the fields: ContractsAndHours.ID ContractsAndHours.Name ContractsAndHours.DailyHoursWorked ContractsAndHours.ContractOut This form is used by the supervisor to enter the hours worked and the contracts in for each day. When entering hours and contracts for each employee I would like the Name field in the subform automatically filled in with the name in the main form when entering through the field. Can this ...

How to pull data from website into my table ?
Dear all, I want to pull stock price from web site into my local table ? Is there any idea how to do it ? Thanks u very much. Chlaris ...

how to pull a record to a form and save it to another table
hi, I want to pull data from more then one field ( as it seems with a combo box with multiple fields listed in a row ) and have it save these fields to a diffrent table. What I'm trying to do is take a Whole record and save that record to a new table. Thanks L Why? It's seldom a good idea to store redundant copies of data. You can use an INSERT INTO query: Dim strSQL As String strSQL = "INSERT INTO TheOtherTable(Field1, Field2, Field3) " & _ "VALUES(" & Me.txtNumericField & ", """ & _ Me.txtTextField...