Macro Condition that references result of a query?

[pgillenw]

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

 

[Steve Schapel]

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

 

[pgillenw via AccessMonster.com]

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

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

I am trying to create a macro that only runs if the result of a query
(which

[quoted text clipped - 4 lines]

If it is, I have had zero luck finding any example of people referencing a
query result in a macro condition.

 

[Steve Schapel]

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

 

[pgillenw via AccessMonster.com]

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...

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

Thanks Steve...
I do see where you are going but I can't get the "And date" second

[quoted text clipped - 5 lines]

Thanks,
Paul

 

[Steve Schapel]

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!

 

[Gerwin]

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


BR,
Gerwin

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...

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

Thanks Steve...
I do see where you are going but I can't get the "And date" second

[quoted text clipped - 5 lines]

Thanks,
Paul