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 ?

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

0
peljo
2/5/2008 4:25:58 PM
access.queries 6343 articles. 1 followers. Follow

4 Replies
1218 Views

Similar Articles

[PageSpeed] 34

hi,

peljo via AccessMonster.com wrote:
> 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
Create a table with these two fields [Size] and [Offset]. [Size] is the 
primary key.

Add this table to your query as a left join.

> 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)
Then it will be:

   DDU: [exworks]*2+0,41+Nz([Offset];0)

Otherwise:

   DDU: [exworks]*2+0,41+
        Iif([size] = 205; 0,001; 0)+
        Iif([size] = 60; 0,001; 0)+
        ...+
        Iif([size] = 0.5; 0,001; 0)+


mfG
--> stefan <--
0
Stefan
2/5/2008 4:36:54 PM
Thank you so much !

peljo wrote:
>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 ?

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

0
peljo
2/5/2008 5:27:41 PM
The Switch function works well in this situation.  Doing it in memory is much 
faster than fetching records from a table:

DDU:  [exworks]*2+0,4+0,01 + 
Nz(switch([exworks]=205,.001,[exworks]=60,.001,[exworks]=20,.009,[exworks]=1,1.32,x=4,.32, [exworks]=.5,1.67),0)

As written, if the value of [exworks] is not in the list, the Switch will 
return Null, but using the Nz function changes it to 0.
-- 
Dave Hargis, Microsoft Access MVP


"peljo via AccessMonster.com" wrote:

> 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 ?
> 
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200802/1
> 
> 
0
Utf
2/5/2008 5:28:01 PM
hi Klatuu,

Klatuu wrote:
> The Switch function works well in this situation.  Doing it in memory is much 
> faster than fetching records from a table:
I had maintainability in mind...)


mfG
--> stefan <--
0
Stefan
2/5/2008 5:35:47 PM
Reply:

Similar Artilces:

using the IIF
i am having trouble with getting the statement to work like i need: IIf([REFUND_LETTERS_ALL_COUNT].Counter=1,"First Request","Second Request ") AS Expr1 What I need to do is nest this statement so that the result will show Counter = 0, blank Counter= 1, “First Request” Counter = 2, “Second Request” Etc…. As of now, it returns "Second Request for all records. On Fri, 21 Mar 2008 10:09:01 -0700, ACSer wrote: > i am having trouble with getting the statement to work like i need: > > IIf([REFUND_LETTERS_ALL_COUNT].Counter=1,"First Request",&qu...

Nested IIF Statement in a query
I'm trying to set up a nested IIF statement in a query and I'm striking out. Basically I'm trying to do the following: If "Field A" and "Field B" are both blank, "Enter This Text" OR If "Field A" is NOT blank, "Enter That Text". Any ideas?? Thanks in adavnce. Frank hi Frank, On 22.01.2010 15:34, FrankTimJr wrote: > I'm trying to set up a nested IIF statement in a query and I'm striking out. > > Basically I'm trying to do the following: > > If "Field A" and "Field B&quo...

IIF statement in query criteria, help!
I have a query in which one date need to be filtered based on another field. If the field [last] =1 , the data needs to be filtered showing only data where [ShiftDate]>=[Start1]. If the field [last]=2, it is [shiftdate]>=[Start2]. So in the criteria for field [ShiftDate], I entered "iif([last]=1,[shiftdate]>=[start1],[shiftdate]>=[start2])". I don't get an error, but I also don't get any results. If I enter ">=[start1]" or [shiftdate]>=[start1] instead of the iif statement, I get the results expected. I'm pretty sure that I...

using iif and like in control Source of report
Simple question! I have a table that has a single field called [name]. I want my report to display true if a name matches "bill". For exam if the field value is "bill Jan" or "Jan Bill" it would be true, "Jan Mark" would be false. Something like this: =IIf([Name] Like "&bill&","true","false") Use * as the wildcard, so: =IIf([YourFieldNameHere] Like "*bill*", "Yep", "Nah") There is a serious problem with the field named Name. Most things in Access (including reports) ...

Text box with iif and is null statements
I need to have a text box that combines a couple of fields and text. My problem is that if the field is blank I need it not to include some of the text. The text box is Dear Alias or FirstName and SpouseName, What I have is ="Dear " & (IIf(IsNull([Alias]),StrConv([FirstName],3),StrConv([Alias],3))) & (IIf(IsNull([SpouseName]),StrConv([SpouseName],3))) & "," Problem is that if there is a spousename it isn't showing. If I add in the "and" I still don't get the spouse name but I get the and. I have tried so many combinatio...

How do I exclude data using the IIF statement in report builder
I have data in my dataset that I want to exclude in the average calculation for the final report (those with a key.value of 999). Here is how my statement is written but it is not returning any value. What am I doing wrong? =iif((Fields!MeasureID.Value = 1 or Fields!MeasureID.Value = 16) and NOT Fields!Key.Value = 999, round(Avg(Fields!CY_Rate.Value),0), iif((Fields!MeasureID.Value = 19 or Fields!MeasureID.Value = 20 or Fields!MeasureID.Value = 21 or Fields!MeasureID.Value = 22 or Fields!MeasureID.Value = 23 or Fields!MeasureID.Value = 24) and NOT Fields!Key.Value = 999, roun...

IIf function, & Date(), & AND, & > & <
I'm trying to get the query to check if an anniversary date is within the next 30 days, so I tried using this formula, but it did not work. Your help is greatly appreciated. Expr1: IIf([Anniv]>Date() AND [Anniv]<Date()+30, "Yes", "No") On Thu, 7 Jan 2010 09:10:26 -0800, Hurrikane4 wrote: > I'm trying to get the query to check if an anniversary date is within the > next 30 days, so I tried using this formula, but it did not work. > Your help is greatly appreciated. > Expr1: IIf([Anniv]>Date() AND [Anniv]<Date()+30, "Yes&q...

IIF and formatting
I hope someone can help! I am running MS Access 2003. I have two Expressions built in a query that look like this: Expr5: IIf([ConstructionCost]>0,[ConstructionCost],"TBD") Expr2: Format([ConstructionCost],"$#,##0;-$#,##0") I want to combine the two so that the output is either: $500,000 (not 500000) or TBD. I can't figure out how to wrap the Format around the IIF. First thing to consider is what kind of data you want in this field. If you just want text (no need to perform any mathematical operations such as summing), use: IIf([ConstructionCost] >...

image field where clause
I have sql table (20million records)with a image field A. I need to set up a where clause like ---- select * from tablea where A(image type) is not null. that query takes forever. How do I optimize that query? Thanks, Mecn (mecn@yahoo.com) writes: > I have sql table (20million records)with a image field A. I need to set > up a where clause like ---- select * from tablea where A(image type) is > not null. > that query takes forever. > How do I optimize that query? About how many rows in the table do you expect to have a non-NULL value in A? If it 16 mil...

Re: iif clause
"Frank" <fhsmith@cox.net> wrote in message news:... > Hi John: > Was able to get expression to work using the Nz expression. Thanks again > for all of your assistance. > > > "Frank" <fhsmith@cox.net> wrote in message > news:iZFcn.48387$zN4.39188@newsfe05.iad... >>I have a query that reflects a field named phonenumber on the main tale. >>I want to replace all null values with "-----". my iif clause is as >>follows: >> >> iif([phonenumber] is null, "----", [phonenumber]). Wh...

vary the select statement in a from clause
hey all, i have a sql like so: update mytable set mycol=val from ( if (cond) begin select myval from t1 end else begin select * from t2 end ) tbl is there a way to make the if(cond) work? probably doesn't look like a good idea anyway? thanks, rodchar It will be best to simply use IF statement and run two separate queries based on the conditions: IF (cond) UPDATE mytable ... FROM t1 ELSE UPDATE mytable ... FROM t2 -- Plamen Ratchev http://www.SQLStudio.com UPDATE Fooobar SET x = CASE WHEN <search condition> THEN ...

IIF Statement Query??
I have four fields in a table that contain an identifying number. I want to write a query with an expression that looks for the first field that contains a value and return that value. If the first field is null, I want the next field to be looked at, if it has a value, I want that value returned, if it is null, I want the next field to be looked at and so on. ma_case_num varchar2(9byte) ap_case_num varchar2(9byte) fs_case_num varchar2(9byte) ss_case_num varchar2(9byte) That data layout sounds a lot like ... a spreadsheet! Whenever I see 'repeating fields' in Access...

DLookup within Iif statement
I'm having trouble getting an Iif Statement that includes a Dlookup to work. Here is what I have but the records where the Iif is true show nothing for this field. IIf([tblOrders].[FreightOnly]=False,[tblOrders].[Price]-DLookUp("Price","tblProductCost","EffectiveDate <=#" & [qryNetRevenue].![Loadeddate] & "#"),[tblOrders].[Price]) Thanks! Walter Walter, there is a typo here: [qryNetRevenue].![Loadeddate] you have both a . and a ! Maybe you need to remove the ! from [qryNetRevenue].![Loadeddate] If the above doesn't w...

iif(isnull( in Query
I have a [Company] and or a [Contact] The Client: is the [Company] if the [Company] isnull then [Contact] How do i put this in a query statement Client: IIf(IsNull([Company]),[Contact],[Company]) HTH, Rob > The Client: is the [Company] if the [Company] isnull then [Contact] > > How do i put this in a query statement > On Sep 13, 6:23 pm, "Rob Parker" <NOSPAMrobppar...@optusnet.com.au.REMOVETHIS> wrote: > Client: IIf(IsNull([Company]),[Contact],[Company]) > > HTH, > > Rob > > > > > The Client: is the [Company] if the [Company] ...

IIF statement, proper formula
Not entirely sure if the IIF statement is the correct way to go about this, I have a field on a query that has total pounds of either printed or plain product. I want to add a column to my query that only adds up the printed product. So, if "product type" is like printed, then add up a total, is basically what I am trying to say. Can you help me with the proper formula? -- Message posted via http://www.accessmonster.com Assuming you have: - a Text field named product type - a Number field named pounds put a text box in the Report Footer section, and set its Contro...

Date Brackets as an IIf statement
Looking for help writing two IIF queries that will put some dates into brackets. I have some sku's that have a expiration date and the idea is to put the product into 1 of 5 brackets. The brackets are: Brackets: Age Date Bracket Code 1. Age Date Bracket Desc "Greater than 6 months left", = > 181 days Age Date Bracket Code 2. Age Date Bracket Desc "6 months to 3 months" = 91 up to 180 days Age Date Bracket Code 3. Age Date Bracket Desc "3 months to 1 month" = 31 up to 90 days Age Date Bracket Code 4. Age Date Bracket Desc "1 month le...

Not good with IIF statements
I think I found out where I explained myself wrong. Can someone help. I need an IIF statement for the following... If the field [Current CommTech Level] equals CommTech 1 then in the field [CommTech 1 Promotion Deadline] add 90 days to the field [Date of Last Promotion] and display the result. If not then the field [CommTech 1 Promotion Deadline] is blank. Sorry for any confusion on an earlier post. Thank you -- Scot Rawlings Technical Trainer Comcast Auburn, WA Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200803/1 Then just modify ...

Iif, And Expressions in a Query (Working with Dates)
I am trying to create an expression that will look at the 1st Pmt Date Long and if it's <= #1/1/2007# and #1/1/2007# < Last Pmt Date Long, to bring a field called Total Payment Forward. So, if the 1st Pmt Date Long is 3/1/2004 and the Last Pmt Date Long is 10/1/2009, it should be bringing the Total Payment Forward. At least, that is my goal. Below is my expression that I'm using in the field called "Jan 2007" : Jan 2007: IIf([1st Pmt Date Long]<=#1/1/2007# And #1/1/2007# < [Last Pmt Date Long],[Total Payment Forward],0) This only returns all the 1st Pmt...

Re: Stored Proc Question: Use ad hoc Where Clause
Ive got a question on how to implement the following: I Have an sp_ that accepts two parameters. Here's essentially what the sp looks like: /****************************************************************************************************** Create Procedure sp_SearchResults @Status_ID Int, @Location_ID Int As Declare @SqlString varchar(100); /** Check values passed in to sp and based off these construct @sqlString to use in Where clause **/ If @Status_ID <> 0 Begin Select @SqlString = 'Where Mytable....

IIf statement in query criteria 12-08-09
I am having a problem with the iif statement when I use it in the criteria for a query for a combo box (Combo2) that is based on the selection of another combo box (Combo1) Essentially, what I am trying to do is show all choices in combo2 if there has not been a selection in Combo1; And show a filtered selection in Combo2 if there is a selection in Combo1. I am trying to do this using the iif statement when setting the criteria in the Combo2 query. iif (isnull(combo1), No Criteria is Set, Criteria is set) I am having trouble with the "No Criteria is Set" part of...

IIf in a report based on select query -- problem w/parameters
I have a report based on a select query. I've added a column in the report to add information into the report based on whether or not two values within the query are equal. The following code is in the unbound text box of my report (created using expression builder). =IIf(MG8!Cust_First_Order_Date_Time=MG8!OrdFlow_Actual_End,"1","0") When trying to run the report I get the "Enter Parameter Value" window requesting a parameter of MG8. Entering any or no parameter returns my report with "1" in all rows (which is not correct based o...

"with <string> in sender" clause works on substring (matches on too many senders)
Outlook 2002 SP-3 I don't send myself e-mails but spammers would like to pretend that I do. They often must put something in the From header to be RFC compliant, so they throw in the same e-mail address as they shoved in the To header. Say I have an account called smith@domain.tld. I define the rule: Apply after receiving a message with smith@domain.tld in sender permanently delete stop processing more rules Looks good until you realize the match is on a substring rather than an exact match on the sender's full e-mail address. The messages do get deleted if I sen...

VBA Quotes using IIF
In CODE 1 below, I have a working SELECT statement that is the RowSource for a combo box named cbotrackID. I'm trying to write it out in VBA, but I'm having a lot of trouble with the quotes. I didn't put my failed code in CODE 2 because I didn't want to confuse anyone. Can someone help me convert the SQL in CODE 1 into a VBA statement that I can use in VBA to set the RowSource with a code version of the sql? CODE 1 ********** SELECT t.trackID, t.trackNum, [trackNum] & ") " & [trackSong] & IIf(IsNull([trackTime]),""," (" & [...

MS Excel VBA DAO SQL where clause cell
Hi I'm trying to get a table of data from MS Access to MS Excel via VBA DAO programming. In my worksheet call 'Data' in cell D14 there is my string value call "toys & Chairs" I can get the DATA into MS Excel without the Where clause in my SQL VBA script. The problem is when I use the WHERE clause which sources its value from cell D14... In my VBA code. Firstly, cell D14 is defined in VBA: Dim r1 As String r1 = Range("D14") Secondly, MY SQL is: SQL = "LVL_1, LVL_2, LVL_3" SQL = SQL & " FROM table_ABC" SQL = SQL &...

IIf
I have an employee database which tracks training. I have a table with basic employee information and a seperate table to hold all trainings with dates completed, hours and if it was mandatory. They are linked by an employee id. I want to write a sum for each employee that shows the total hours earned if the completed date falls between the user entered start and end date of the report. Some of the trainings show regardless of the date completed if they are identified as a mandatory training. This is my thoughts on the IIf statement but I am missing something. =IIf([Compl...