Conditional Macros help

  • Thread starter Thread starter JerryD
  • Start date Start date
J

JerryD

I need help creating a report whch includes a Conditional
Macro (no VBA).

I have a query, qryA whose records contain the
fields "StreetName" amd "AssocMemb". "AssocMemb" is a
Yes/No field.

And I have a report, rptA.
rptA uses qryA for input.
rptA sorts and groups on "StreetName"
rptA does not have a group header, only a group footer.

The Group Footer has three fields: StreetName, txtNumOf
Residences, and txtNumOfEnrollments. The Control Source
for txtNumOfResidences is set to: "Count([SteetNames]).

Upon running the report, the "StreetNaae"
and "txtNumOfResidences" fields are coming through OK.
But after reading how to create Macros and Condintional
Macros, I can't set up one to work. All I want the
conditional macro to do is "count" the number of records
with "AssocMemb" = "Yes". Can someone help me with this
macro?
 
You need to know how to set up a condition that counts the number of records
matching your criteria? Try this as your condition statement:

DCount("*", "qryA", "AssocMemb=Yes")=0

Above condition will be true if there are no records in the query matching
your criterion.

However, if what you want to is to display such a count in a textbox on your
report, and assuming that AssocMemb is a True/False (or Yes/No) field, you
can use an expression similar to this as the control source of the textbox
in the report's footer section:
=Sum(-[AssocMemb])

The value of True (or Yes) for a boolean field is -1, and 0 for False (or
No).
 
Your expression
=Sum(-[AssocMemb])
as the control source for the footer section text box
worked fine. And your explanation was great. My project
was stuck on top dead center.

Can you recommend any good educational literature on
Macros and VBA? I have a Microsoft XP system with Office
Professional, Thanks. Jerry Deckard
-----Original Message-----
You need to know how to set up a condition that counts the number of records
matching your criteria? Try this as your condition statement:

DCount("*", "qryA", "AssocMemb=Yes")=0

Above condition will be true if there are no records in the query matching
your criterion.

However, if what you want to is to display such a count in a textbox on your
report, and assuming that AssocMemb is a True/False (or Yes/No) field, you
can use an expression similar to this as the control source of the textbox
in the report's footer section:
=Sum(-[AssocMemb])

The value of True (or Yes) for a boolean field is -1, and 0 for False (or
No).
--
Ken Snell
<MS ACCESS MVP>

I need help creating a report whch includes a Conditional
Macro (no VBA).

I have a query, qryA whose records contain the
fields "StreetName" amd "AssocMemb". "AssocMemb" is a
Yes/No field.

And I have a report, rptA.
rptA uses qryA for input.
rptA sorts and groups on "StreetName"
rptA does not have a group header, only a group footer.

The Group Footer has three fields: StreetName, txtNumOf
Residences, and txtNumOfEnrollments. The Control Source
for txtNumOfResidences is set to: "Count([SteetNames]).

Upon running the report, the "StreetNaae"
and "txtNumOfResidences" fields are coming through OK.
But after reading how to create Macros and Condintional
Macros, I can't set up one to work. All I want the
conditional macro to do is "count" the number of records
with "AssocMemb" = "Yes". Can someone help me with this
macro?
Click to expand...


.
Click to expand...
 
Glad it worked for you.

With regard to books, the "best" choices are the ones that best relate to
you. John Viescas, Microsoft ACCESS MVP, has a list of suggested books at
his website (www.viescas.com).

For my personal use, I have found that most books do not discuss macros in
great detail. However, the ACCESS 2002 Bible (Prague & Irwin, published by
Hungry Minds) does spend a fair amount of pages on macros and is where I
learned about them.

I have used many books that cover VBA for ACCESS. One that is a good
starting point is Beginning ACCESS 2002 VBA (Smith & Sussman, published by
Wrox).

--
Ken Snell
<MS ACCESS MVP>

JerryD said:
Your expression
=Sum(-[AssocMemb])
as the control source for the footer section text box
worked fine. And your explanation was great. My project
was stuck on top dead center.

Can you recommend any good educational literature on
Macros and VBA? I have a Microsoft XP system with Office
Professional, Thanks. Jerry Deckard
-----Original Message-----
You need to know how to set up a condition that counts the number of records
matching your criteria? Try this as your condition statement:

DCount("*", "qryA", "AssocMemb=Yes")=0

Above condition will be true if there are no records in the query matching
your criterion.

However, if what you want to is to display such a count in a textbox on your
report, and assuming that AssocMemb is a True/False (or Yes/No) field, you
can use an expression similar to this as the control source of the textbox
in the report's footer section:
=Sum(-[AssocMemb])

The value of True (or Yes) for a boolean field is -1, and 0 for False (or
No).
--
Ken Snell
<MS ACCESS MVP>

I need help creating a report whch includes a Conditional
Macro (no VBA).

I have a query, qryA whose records contain the
fields "StreetName" amd "AssocMemb". "AssocMemb" is a
Yes/No field.

And I have a report, rptA.
rptA uses qryA for input.
rptA sorts and groups on "StreetName"
rptA does not have a group header, only a group footer.

The Group Footer has three fields: StreetName, txtNumOf
Residences, and txtNumOfEnrollments. The Control Source
for txtNumOfResidences is set to: "Count([SteetNames]).

Upon running the report, the "StreetNaae"
and "txtNumOfResidences" fields are coming through OK.
But after reading how to create Macros and Condintional
Macros, I can't set up one to work. All I want the
conditional macro to do is "count" the number of records
with "AssocMemb" = "Yes". Can someone help me with this
macro?
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top