Results 1 to 15 of 15
  1. #1
    Join Date
    Jan 2005
    Posts
    50

    Unanswered: Expression/IIf statement help

    I'm trying to use this expression in a make-table query.

    Code:
    IIf(IsNull([ShopCartDesc].[Status]),Null,IIf([ShopCartDesc].[Status]="pre","pre",IIf([IM2_InventoryItemWhseDetl].[QtyOnHand]-[QtyOnSalesOrder]-[QtyOnBackOrder]>0,"new",IIf([ShopCartDesc].[Date Imported]>DateAdd("m",-2,Date()),"new"))))
    Obviously, it's regarding the "status" field. But if I put it in the criteria (Query Design View), I only get records where the status field contains "new" or "pre". If I put it in the "Field" field, it creates a new field, instead of drawing from the actual status field (I think).

    I want all records (in the db) and the statement above to be applied at the same time. How do I do that?

    And furthermore, I think my expression really just isn't correct.
    I think I incorrectly nested some IIfs... and perhaps the statements are in the incorrect order.

    I have a "status" field, containing values of either "new" "pre" or null.
    This status field is located in a table of items descs. It gets included in a make-table query, that compiles relevant fields and creates a products file for an online store.

    This is the way I'd like it to happen with this field during the make-table query:
    - If it's null, it should just remain null.
    - If it contains "pre" I want it to stay the same, or to udpate to "new" if the "date imported" value is within the last two months of the current date, Or to update to "new" if [QtyOnHand]-[QtyOnSalesOrder]-[QtyOnBackOrder] is greater than 0.

    Can anyone tell what I'm trying to do here? Am I even going in the right directions with this IIf statement?

    Btw, does anyone know of specific good online resources for learning about IIf statements and nested IIf statements?
    Who imported me here?

  2. #2
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Rather than going around doing difficult stuff like this in Nested Iif statements.

    Make a readable and maintainable Function in VBA....

    Regards

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,423
    Provided Answers: 8
    Look like the IIf is Getting to ???!!!?@?@!@?!@@$#?@$??


    So why not Write a Function in VB to Pass the right in fo back to the Query

    NewFeild:MyFunction([feldname])

    a function in a modlue
    Code:
    function MyFunction(ThisValue)
    
    if ThisValue < 10 then
    MyFunction = ThisValue + 10
    else
    MyFunction = ThisValue + 100
    end if
    End Function
    base on what the [feldname] is it will add 10 to it or add 100


    This is the way I'd like it to happen with this field during the make-table query:
    - If it's null, it should just remain null.
    - If it contains "pre" I want it to stay the same, or to udpate to "new" if the "date imported" value is within the last two months of the current date, Or to update to "new" if [QtyOnHand]-[QtyOnSalesOrder]-[QtyOnBackOrder] is greater than 0.
    My thinking would be to Use a IIF and a function



    this : iif(isnull([bla]),MyFunction([bla],[date imported],[QtyOnHand],[QtyOnSalesOrder],[QtyOnBackOrder]),"bla")


    Code:
    Function MyFunction(check1,IMdate,sorder,border)
    
    if Check1="Per" then Exit Function
    
    if Imdate > dateadd("m",-2,date()) then
    
    else
    
    end if
    ever work with javascript

    by doesn't this work

    eval("document.Order.A-" + ID + ".value = '" + tt +"'");

    I stuck so this is my smoko break LOL
    Last edited by myle; 11-30-05 at 05:16.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  4. #4
    Join Date
    Jan 2005
    Posts
    50

    Question

    This is where I have to admit that I'm a veteran newb. I don't know VB and have never used any.

    How about something like this?
    Code:
    Status: IIf(Nz([ShopCartDesc].[Status],"")="pre" And ([IM2_InventoryItemWhseDetl].[QtyOnHand]-[QtyOnSalesOrder]-[QtyOnBackOrder]>0 Or [ShopCartDesc].[Date Imported] > DateAdd("m",-2,Date())),"new",[ShopCartDesc].[Status])
    Although.

    When I ran the query with this update, I DID get all records, so that's good. BUT, I have records where "date imported" is "11/2/2005 2:11:14 PM" but "status" is still null (instead of "new").

    I do have some records where date imported is "11/30/2005 10:39:16 AM" and status was updated to "new". The only difference is that the aforementioned records had a null status whereas these ones were update from "pre".
    Who imported me here?

  5. #5
    Join Date
    Jan 2005
    Posts
    50

    needs revised

    Am I disqualified for being VB illiterate?
    Last edited by trunkstar; 11-30-05 at 20:00.
    Who imported me here?

  6. #6
    Join Date
    Jan 2005
    Posts
    50

    Now with relevant code

    Code:
    Status: IIf(Nz([ShopCartSmallDesc].[Status],"")="pre" And ([IM2_InventoryItemWhseDetl].[QtyOnHand]-[QtyOnSalesOrder]-[QtyOnBackOrder]>0 Or [ShopCartSmallDesc].[Date Imported] > DateAdd("m",-2,Date())),"new",[ShopCartSmallDesc].[Status])
    Hm, I think I need to revise this, but I'm not sure how.

    I've been a little unclear while thinking and typing.

    I actually need it to update the field to "new":
    - from null, anytime [Date Imported] > DateAdd("m",-2,Date()))
    - from "pre", when [QtyOnHand]-[QtyOnSalesOrder]-[QtyOnBackOrder]>0

    The rest of the time it can stay as it is.

    argh. Thanks for any continued assistance, this particular bit must be boring by now.

    Is there some part of this dictating that any null values will remain null no matter what?
    Who imported me here?

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    here is what you currently have:
    Code:
    IIf
    	(
    
    		Nz([ShopCartSmallDesc].[Status],"")="pre"
    		
    			And
     
    				(
    
    						[IM2_InventoryItemWhseDetl].[QtyOnHand]-[QtyOnSalesOrder]-[QtyOnBackOrder]>0 
    
    					Or 
    
    						[ShopCartSmallDesc].[Date Imported] > DateAdd("m",-2,Date())
    
    				)
    	
    	
    		,"new"
    	
    	
    		,[ShopCartSmallDesc].[Status]
    
    
    	)
    code-wise it seems fine and should execute as advertised, but logic-wise... ?

    that OR-thing seems a touch strange - i am struggling to understand an OR-thing between date imported and stock-level (fried-egg OR today's-date ??)

    anyhooo, assuming you don't eat young children and that you are generally sane, you say
    I actually need it to update the field to "new":
    - from null, anytime [Date Imported] > DateAdd("m",-2,Date()))
    - from "pre", when [QtyOnHand]-[QtyOnSalesOrder]-[QtyOnBackOrder]>0


    UPDATE myTable SET myField = 'new'
    WHERE
    (
    (isNull(myField) And [Date Imported] > DateAdd('m',-2,Date()))
    Or
    (nz(myField, '')='pre' And [QtyOnHand]-[QtyOnSalesOrder]-[QtyOnBackOrder]>0[/I])
    );

    izy
    currently using SS 2008R2

  8. #8
    Join Date
    Jan 2005
    Posts
    50
    Apologies if I'm going back and forth on this. It seems I didn't fully cover this consideration in my planning.

    After some investigation it comes to my attention that actually, in most instances items will be in stock (available) at the moment they they are imported, thus, skipping the "pre(order)" stage.

    Izy, you're struggling to figure out the OR thing, while the OR thing seems to be exactly the problem I was trying to identify...

    BTW:
    anyhooo, assuming you don't eat young children and that you are generally sane,
    Something leading you to believe otherwise or something?
    Who imported me here?

  9. #9
    Join Date
    Jan 2005
    Posts
    50
    I'm currently using this:

    Code:
    Status: IIf(([ShopCartSmall - Desc].[Status]="pre" And ([IM2_InventoryItemWhseDetl].[QtyOnHand]-[IM2_InventoryItemWhseDetl].[QtyOnSalesOrder]-[IM2_InventoryItemWhseDetl].[QtyOnBackOrder])>0) Or ([ShopCartSmall - Desc].[Date Imported]<DateAdd("m",-2,Date())),"new",[ShopCartSmall - Desc].[Status])
    It seems to be doing the trick, but maybe too well?
    It's updating status to "new" for items that were imported in January of 2005, for values such as:
    1/24/2005 2:09:26 PM

    Am I using DateAdd wrong or something?
    Who imported me here?

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    It's doing exactly what you are telling it to do!
    Exploding it out again so stupid-me can see what is going on:
    Code:
    Status:
     
    IIf
    	(
    		(
    
    			[ShopCartSmall - Desc].[Status]="pre"
     
    				And
     
    			([IM2_InventoryItemWhseDetl].[QtyOnHand]-[IM2_InventoryItemWhseDetl].[QtyOnSalesOrder]-[IM2_InventoryItemWhseDetl].[QtyOnBackOrder])>0
    
    		)
     
    			Or
     
    		(
    
    			[ShopCartSmall - Desc].[Date Imported]<DateAdd("m",-2,Date())
    
    		)
    
    		,"new"
    
    		,[ShopCartSmall - Desc].[Status]
    	)
    trying in text:

    if ([Status] = "pre" AND [GotStock]) OR (ImportedMoreThan2MonthsAgo) THEN "new" ELSE [Status]


    today is 5th December 2005
    OR (ImportedMoreThan2MonthsAgo) is TRUE for any date before 5th October 2005 (including dates in January 2005).
    ...so you get "new"

    is it something as simple as switching < to > ??


    if there is any risk of a Null [Status], use:
    Nz([ShopCartSmall - Desc].[Status],"")="pre"

    izy
    currently using SS 2008R2

  11. #11
    Join Date
    Jan 2005
    Posts
    50

    Unhappy

    today is 5th December 2005 OR (ImportedMoreThan2MonthsAgo) is TRUE for any date before 5th October 2005 (including dates in January 2005).
    ...so you get "new"
    That does make sense, but first, this is the goal:
    Only items imported within the last two months should say "new."

    Secondly, it doesn't make sense that with <
    All the records where I get "new" are from 1/05 and 10/05

    And with > those same records do not get "new".
    Last edited by trunkstar; 12-06-05 at 15:12.
    Who imported me here?

  12. #12
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    if you want the last 2 months to show "new" you definitely need >

    you don't have to believe me.
    try Ctrl-G

    my results using < on 7th December, 2005:

    ? #1/1/2005# < dateadd("m", -2, Date())
    True

    ? #9/9/2005# < dateadd("m", -2, Date())
    True

    ? #10/10/2005# < dateadd("m", -2, Date())
    False


    and using >

    ? #1/1/2005# > dateadd("m", -2, Date())
    False

    ? #9/9/2005# > dateadd("m", -2, Date())
    False

    ? #10/10/2005# > dateadd("m", -2, Date())
    True

    and since your expression is
    iif((blahblah) OR (someDate > dateadd()), "new", somethingElse)
    that OR is going to mark ALL stuff from the last two months as "new"

    UNLESS
    blahblah is Null
    or
    someDate has a problem
    or
    Date() is not working for you (try Now() as an experiment)


    izy
    Last edited by izyrider; 12-07-05 at 03:49.
    currently using SS 2008R2

  13. #13
    Join Date
    Jan 2005
    Posts
    50
    I'm trying to attach a sample db (.zip) to this post, but I keep getting:

    Invalid Post specified. If you followed a valid link, please notify the webmaster
    Anyone run into this issue before?
    Who imported me here?

  14. #14
    Join Date
    Jan 2005
    Posts
    50
    Ah the problem is that the file is 4.8mb, too large for the 2mb size limit.
    Thoughts anyone?
    Who imported me here?

  15. #15
    Join Date
    Jan 2005
    Posts
    50
    In case anyone was actually interested in the outcome/solution
    ...

    Okay, I changed the data type to date/time. jeez, DUH!

    [Date Imported])>DateAdd("m",-2,Date())),"new - That fixed this.

    After that I was having the same problem with the second Iif overwriting everything with new regardless...

    So, I just broke the thing into two seperate update queries (SQL):

    One for pre:
    UPDATE DISTINCTROW [ShopCartSmall - Desc] INNER JOIN IM2_InventoryItemWhseDetl ON [ShopCartSmall - Desc].SKUID = IM2_InventoryItemWhseDetl.ItemNumber SET [ShopCartSmall - Desc].Status = IIf([ShopCartSmall - Desc].[Status]="pre" And ([IM2_InventoryItemWhseDetl].[QtyOnHand]-[IM2_InventoryItemWhseDetl].[QtyOnSalesOrder]-[IM2_InventoryItemWhseDetl].[QtyOnBackOrder])>0,"new",[ShopCartSmall - Desc].[Status]);

    One for new:
    UPDATE [ShopCartSmall - Desc] SET [ShopCartSmall - Desc].Status = IIf([ShopCartSmall - Desc].[Date Imported]>DateAdd("m",-2,Date()),"new")
    WHERE ((([ShopCartSmall - Desc].Status)<>"pre" Or ([ShopCartSmall - Desc].Status) Is Null));

    With some corrected/added criteria, now it works (as far as I can tell)...
    Thanks so much.
    Who imported me here?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •