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

    Unanswered: DatePart("m",Now()) problem

    Hello,

    My (DatePart("m",Now()) ) not working if I have no current month entries.
    Is there a way in the query to perform that check?

    My IIF is not working
    IIF ((DatePart("m",Now())) <> false, (DatePart("m",Now())), (DatePart("m",Now()) -1 )

    Thank you very much.

  2. #2
    Join Date
    Mar 2006
    Posts
    163
    How are you using that expression?

    It should always return the current month since you are using Now().

  3. #3
    Join Date
    Sep 2005
    Location
    Brooklyn, ny
    Posts
    69
    The thing is in the table that I pull info from there are no "date" values yet for this month, since today is the first day of the month there are no current data, but it will be in the table tomorrow.

    My report return data for week, month, and year. and if I have no data for current month yet, even it is a firs day of the month, report has to show that data anyway since there are data for current week (which include the current month info) and a year.

    I'm using the Now()-1, to get May info , since there are no June info,
    but the query return no result.

    Should I replace false, with is not null?

  4. #4
    Join Date
    Mar 2006
    Posts
    163
    Can you explain how/where you are using the expression?

    Is it criteria for a field?

  5. #5
    Join Date
    Sep 2005
    Location
    Brooklyn, ny
    Posts
    69
    Below please find a query I'm using:

    And the WHERE clause part with
    AND ((DatePart("m",[dbo_OrderHeaderArchive.dOrderDate]))=(DatePart("m",Now()))))
    is not working for the current month since there are no current month info in the table,
    BUT if there are no data in the table, query have to retur prev month info.

    How to do that ???? Now()-1 is working, but i have to manually go to the query and fix it, is there a way to do it with IIF ?


    SELECT
    dbo_OrderDetailArchive.sProductSKU AS Style,
    dbo_OrderDetailArchive.sSKUDimension2 AS Color,
    Sum(dbo_OrderDetailArchive.nQuantity) AS SumOfnQuantity,
    dbo_OrderDetailArchive.nPrice AS Price,
    Sum(dbo_OrderDetailArchive.nExtension) AS SumOfnExtension,
    dbo_OrderHeaderArchive.nDiscountPercent,
    IIf([nDiscountPercent]>0,([SumOfnQuantity]*[nPrice]*((100-[nDiscountPercent])/100)),([SumOfnQuantity]*[nPrice])) AS [Extended Retail],
    DatePart("m",[dbo_OrderHeaderArchive.dOrderDate]) AS [Month],
    DatePart("yyyy",[dbo_OrderHeaderArchive.dOrderDate]) AS [Year],
    dbo_OrderHeaderArchive.dOrderDate
    FROM
    dbo_OrderDetailArchive INNER JOIN dbo_OrderHeaderArchive ON dbo_OrderDetailArchive.nOrderID = dbo_OrderHeaderArchive.nOrderID
    WHERE
    (((DatePart("yyyy",[dbo_OrderHeaderArchive.dOrderDate]))=(DatePart("yyyy",Now())))
    AND ((DatePart("m",[dbo_OrderHeaderArchive.dOrderDate]))=(DatePart("m",Now()))))
    GROUP BY
    dbo_OrderDetailArchive.sProductSKU,
    dbo_OrderDetailArchive.sSKUDimension2,
    dbo_OrderDetailArchive.nPrice,
    dbo_OrderHeaderArchive.nDiscountPercent,
    DatePart("m",[dbo_OrderHeaderArchive.dOrderDate]),
    DatePart("yyyy",[dbo_OrderHeaderArchive.dOrderDate]),
    dbo_OrderHeaderArchive.dOrderDate
    ORDER BY
    dbo_OrderDetailArchive.sProductSKU,
    dbo_OrderDetailArchive.sSKUDimension2




    Thank you

  6. #6
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    There is something wrong with your comparison. When DatePart is evaluated it will be a number between 1 and 12. Then you compare that with False. That will always be false since any nonzero result is true. So essentially you are saying IIf(True<>False,... I think you need something else.

    As a side note, I would recommend using the Month function instead. Month(Now()) will give you the same thing and is easier to understand when reading it.

    Give a little more explanation of what you are trying to accomplish and I am sure someone will be able to help you with your problem.

  7. #7
    Join Date
    Sep 2005
    Location
    Brooklyn, ny
    Posts
    69
    I chang my DatePart to Month
    and
    Below please find a query I'm using:

    in the WHERE clause part with

    ((Month([dbo_OrderHeaderArchive.dOrderDate]))=(Month(Now()))))

    is not working for the current month since there are no current month info in the table,

    BUT if there are no data in the table, query have to return prev. month info.

    How to do that ???? Now()-1 is working, but i have to manually go to the query and fix it, is there a way to do it with IIF

    Thank you

  8. #8
    Join Date
    Mar 2006
    Posts
    163
    Why not just always return the last 2 months of data?

    That would be easier to implement.

    To only return last months data if there's none for this month would be more complicated, you would probably need a DCount, or a subquery.

  9. #9
    Join Date
    Sep 2005
    Location
    Brooklyn, ny
    Posts
    69
    The reason to see one month info was made by my mngers, not by me
    They do want to see current week, current month and ytd sales info.

    Would you help me out to see the month info if there are no curr month info yet?

    Thank you

  10. #10
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    OK, picking up on what norie said, how about the following criteria:

    Code:
    Month([dbo_OrderHeaderArchive.dOrderDate])=Month(DMax("dOrderDate","dbo_OrderHeaderArchive")) And Year([dbo_OrderHeaderArchive.dOrderDate])=Year(DMax("dOrderDate","dbo_OrderHeaderArchive"))
    This will select records for the month/year corresponding to the most recent date stored.

    Chris

  11. #11
    Join Date
    Sep 2005
    Location
    Brooklyn, ny
    Posts
    69
    Thank you, it is working.

    But now he does want to see the last 2 months of data, since it is not much for the current month.

    Current month, and Current Month - 1
    Is there a way to do it in a query?

    Thank you

Posting Permissions

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