Macro Condition that references result of a query?


P

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
 
S

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
 
Ad

Advertisements

P

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

Steve said:
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.
 
S

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
 
P

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

Steve said:
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
 
S

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!
 
Ad

Advertisements

G

Gerwin

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


BR,
Gerwin

pgillenw via AccessMonster.com said:
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...

Steve said:
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
 

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. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top