Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2005
    Location
    Brooklyn, ny
    Posts
    69

    Unanswered: Update field with IIF

    Hello,
    I'm tying to execute a query where I have an IIF that is
    based on a date range.

    Total:
    IIf(([dDate] Between #1/1/2006# And #1/31/2006#) ,
    ((Sum([Quantity]))*[nPrice]))

    And it is not working right.

    Need your help.

    Thank you.

    LP

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    LP,

    Where is your False part of the IIF? Ex:

    IIF (A>B,TRUE,FALSE)

    Also why is it not working? Aggregate problem (Sum)?
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Sep 2005
    Location
    Brooklyn, ny
    Posts
    69
    Yes, an error for the Aggregate problem
    I do have a group by in the query.

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by LP2005
    Yes, an error for the Aggregate problem
    I do have a group by in the query.
    Well if you change your query:

    SELECT SUM(Quantity * Price) AS SomeNameHere FROM SomeTable WHERE(dDate Between #1/1/2006# And #1/31/2006#)

    That might help ...

    P.S. - You only need to qualify column names (the []'s) when the name is a reserved word or has spaces or special characters in it ... FYI
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Sep 2005
    Location
    Brooklyn, ny
    Posts
    69
    But I need to populate each column based on the month info,

    iif between 1/1/06 and 1/31/06 then sum for jan
    iif between 2/1/06 and 2/28/06 then sum feb
    and so on.


    Thank you

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by LP2005
    But I need to populate each column based on the month info,

    iif between 1/1/06 and 1/31/06 then sum for jan
    iif between 2/1/06 and 2/28/06 then sum feb
    and so on.


    Thank you
    So ...

    SELECT SUM(Quantity * Price) AS SomeNameHere FROM SomeTable WHERE(dDate Between #1/1/2006# And #1/31/2006#) GROUP BY Format(dDate,'mmm yyyy')

    And the problem is???
    Back to Access ... ADO is not the way to go for speed ...

  7. #7
    Join Date
    Sep 2005
    Location
    Brooklyn, ny
    Posts
    69
    It is not updating the fields

    I have a table:

    StyleID,
    Total U: (Sum([nQuantity])) ,
    Total $: (Sum([nQuantity])*[nPrice])),
    Jan U,
    Jan D,
    Feb U,
    Feb D,
    Mar U,
    Mar $ and so on to Dec
    and I need to update each U and $ field based on a date function.
    iif [dDate] between 1/1/06 and 1/31/06 then Update U and $ for jan
    iif [dDate] between 2/1/06 and 2/28/06 then Update U and $ for feb

    and this where I have a problem

    Please help

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    So you're storing multiple distinct values, storing aggregate values and using spaces in field names all in the same table....

    NEAT!!
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    GODS!!!! Another one ... Sarcasm ill becomes you Ted ... Shall I???


    YOU DO NOT STORE CALCULATED VALUES ... You query for them ... You write reports that display them ... You make forms that displays them in a pleasant UI ... AND you sure as hell don't run update queries to continually update the calculated values ...
    Back to Access ... ADO is not the way to go for speed ...

  10. #10
    Join Date
    Sep 2005
    Location
    Brooklyn, ny
    Posts
    69
    I know it is all sound crazzzy to you, but it is not me, but a tables in our acces db that has no relation whats so ever, How can I query them if I have to create them first????
    Just data stored and that is all. How else I can make a table to have all related infor the year in it?
    I'm not a programmer, nor a reporter. Probably have to become one, to improve that mess.

  11. #11
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by LP2005
    I know it is all sound crazzzy to you, but it is not me, but a tables in our acces db that has no relation whats so ever, How can I query them if I have to create them first????
    Just data stored and that is all. How else I can make a table to have all related infor the year in it?
    I'm not a programmer, nor a reporter. Probably have to become one, to improve that mess.

    Tables do not need to have relationships established to query them ... You do need a way to relate one table to another in order to join them to get common data from them tho ...

    Also, the larger issue (and bone to be picked with you) is that your initial question was straight forward ... Then you WHAMMIED me with what you're trying to do which is patently not a good idea in general and completely untenable specifically. You are better off to explain the WHOLE situatuion up front, then you'll get good (or better) advice on your course(s) of action eliminating the wasting of your time and more importantly the wasting of MY TIME. So let's start over:

    What are you wanting to do?
    What are the tables that you are working with?
    What do you want to use to display your results?
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

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