Macro Condition that references result of a query?

Discussion in 'Access Macros' started by pgillenw, Apr 28, 2010.

  1. pgillenw

    pgillenw Guest

    I am trying to create a macro that only runs if the result of a query (which
    sums the total orders of a certain product for the current month) is greater
    than a certain number.
    Is this even possible?
    If it is, I have had zero luck finding any example of people referencing a
    query result in a macro condition.

    Thanks,

    Paul
     
    pgillenw, Apr 28, 2010
    #1
    1. Advertisements

  2. Paul,

    Yes, it is possible. Your question is very non-specific, but I would guess
    you will be using one of the domain functions. For example, your Condition
    might be something like this:
    DSum("[OrderAmount]","SomeTable","[ProductID]=" & [ProductID] & " And
    [OrderDate]>DateSerial(Year(Date()),Month(Date()),1))">123
    Or, if you literally have a query that only returns the one value that you
    are trying to compare, the Condition may just be something like this:
    DLookup("[TotalAmount]","YourQuery")>123
     
    Steve Schapel, Apr 29, 2010
    #2
    1. Advertisements

  3. Thanks Steve...
    I do see where you are going but I can't get the "And date" second criteria
    to work.
    I know it is something wrong with my syntax...ideas appreciated!
    DSum("orddose","History","[vaccine]='TDAP'" & " And
    [orddate]>DateSerial(Year(Date()),Month(Date()),1))">1000
    Thanks,
    Paul

     
    pgillenw via AccessMonster.com, Apr 30, 2010
    #3
  4. Paul,

    Yep, you have the final " in the wrong position. Try it like this:
    DSum("orddose","History","[vaccine]='TDAP' And
    [orddate]>DateSerial(Year(Date()),Month(Date()),1)")>1000
     
    Steve Schapel, Apr 30, 2010
    #4
  5. You deserve a medal.
    For whatever reason this does run but always returns as null.
    This is the method I have gotten to work alone, but not as a second criteria
    DSum("orddose","History","[vaccine]='TDAP' And (FORMAT([orddate],"yyyymm")=
    (Date(),"yyyymm")" >1000
    Again I am syntax illiterate for this...

     
    pgillenw via AccessMonster.com, Apr 30, 2010
    #5
  6. Paul,

    I expect this would work:
    DSum("orddose","History","[vaccine]='TDAP' And
    Format([orddate],"yyyymm")=Format(Date(),"yyyymm")")>1000

    But getting records returned by the query here assumes that you in fact *do*
    have TDAP records in the History table for the current month, totalling more
    than 1000 for the orddose. And since it's the 1st of the month now, this is
    probably not true!
     
    Steve Schapel, May 1, 2010
    #6
  7. pgillenw

    Gerwin Guest

    You could also try to use the DateDiff function - if I remember correctly you
    can compare months using this function.


    BR,
    Gerwin

     
    Gerwin, May 11, 2010
    #7
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.