I am working with a table (that unfortutely I cannot change) that uses
"yes/no" boxes for 34 different categories. I want to create a field in a
report that shows the text of any categories with "Y" in it. I created a
query to get the text:

Example: Gifts: IIf([GIFTS_FL]="Y","Gifts","")

It is possible for multiple categories to be selected. In a report, I want
one field that lists the different categories, separated by a comma. I can
do something like this in the query but then I have these "commas" between
items that are "N".

Is there any easier way to do this?

Thanks!

The best way to do this would be to write a VBA function and call the
function from the query.

When you call the function from the query, you'd pass all 34 fields to the
function. The function could return the string in any format you need, eg
by omitting fields where the checkbox is false.

It all depends on whether you want to write a function in a code module. If
you feel up to it and need help, post again.

Geoff


"IM4Jayhawks" <(E-Mail Removed)> wrote in message
news7182EC0-1CBE-4EFF-9BBE-(E-Mail Removed)...
>I am working with a table (that unfortutely I cannot change) that uses
> "yes/no" boxes for 34 different categories. I want to create a field in a
> report that shows the text of any categories with "Y" in it. I created a
> query to get the text:
>
> Example: Gifts: IIf([GIFTS_FL]="Y","Gifts","")
>
> It is possible for multiple categories to be selected. In a report, I
> want
> one field that lists the different categories, separated by a comma. I
> can
> do something like this in the query but then I have these "commas" between
> items that are "N".
>
> Is there any easier way to do this?
>
> Thanks!

Thanks, Geoff! This may be over my head, but I'll give it a try. Where do I
write the VBA function? Within my query or report?



"Geoff" wrote:

> The best way to do this would be to write a VBA function and call the
> function from the query.
>
> When you call the function from the query, you'd pass all 34 fields to the
> function. The function could return the string in any format you need, eg
> by omitting fields where the checkbox is false.
>
> It all depends on whether you want to write a function in a code module. If
> you feel up to it and need help, post again.
>
> Geoff
>
>
> "IM4Jayhawks" <(E-Mail Removed)> wrote in message
> news7182EC0-1CBE-4EFF-9BBE-(E-Mail Removed)...
> >I am working with a table (that unfortutely I cannot change) that uses
> > "yes/no" boxes for 34 different categories. I want to create a field in a
> > report that shows the text of any categories with "Y" in it. I created a
> > query to get the text:
> >
> > Example: Gifts: IIf([GIFTS_FL]="Y","Gifts","")
> >
> > It is possible for multiple categories to be selected. In a report, I
> > want
> > one field that lists the different categories, separated by a comma. I
> > can
> > do something like this in the query but then I have these "commas" between
> > items that are "N".
> >
> > Is there any easier way to do this?
> >
> > Thanks!
>
>
>

Please list the 34 fields names, eg:

GIFTS_FL
?
?
?
....

Geoff


"IM4Jayhawks" <(E-Mail Removed)> wrote in message
news:698F76E7-8C02-4CAC-8BE3-(E-Mail Removed)...
> Thanks, Geoff! This may be over my head, but I'll give it a try. Where
> do I
> write the VBA function? Within my query or report?
>
>
>
> "Geoff" wrote:
>
>> The best way to do this would be to write a VBA function and call the
>> function from the query.
>>
>> When you call the function from the query, you'd pass all 34 fields to
>> the
>> function. The function could return the string in any format you need,
>> eg
>> by omitting fields where the checkbox is false.
>>
>> It all depends on whether you want to write a function in a code module.
>> If
>> you feel up to it and need help, post again.
>>
>> Geoff
>>
>>
>> "IM4Jayhawks" <(E-Mail Removed)> wrote in message
>> news7182EC0-1CBE-4EFF-9BBE-(E-Mail Removed)...
>> >I am working with a table (that unfortutely I cannot change) that uses
>> > "yes/no" boxes for 34 different categories. I want to create a field
>> > in a
>> > report that shows the text of any categories with "Y" in it. I created
>> > a
>> > query to get the text:
>> >
>> > Example: Gifts: IIf([GIFTS_FL]="Y","Gifts","")
>> >
>> > It is possible for multiple categories to be selected. In a report, I
>> > want
>> > one field that lists the different categories, separated by a comma. I
>> > can
>> > do something like this in the query but then I have these "commas"
>> > between
>> > items that are "N".
>> >
>> > Is there any easier way to do this?
>> >
>> > Thanks!
>>
>>
>>

INAPPROPRIATE_FL
ALTERED_DOC_FL
COMUNCTN_STDS_FL
FAXED_ITEMS_FL
FUNDING_ACCT_FL
LETTERHEAD_FL
INS_POLICIES_FL
ANOTHER_ORG_FL
BUS_SOLICTN_FL
THIRD_PRTY_REQ_FL
EMAIL_USE_FL
OTSD_ACCOUNTS_FL
CLNT_CMPLNT_FL
CMPLNC_MTG_FL
FIDU_CAPCTY_FL
GIFTS_FL
CLIENT_LOANS_FL
PHONE_LSTG_FL
SEP_OF_BUS_FL
SUB_OF_BUS_FL
TITLES_FL
UNAPRVD_MATERL_FL
ADVISOR_ASSTNT_FL
FUNDS_CO_MINGLG_FL
DISCRNY_AUTH_FL
FORGERIES_FL
INV_CLUB_FL
MISREPRESENT_FL
PRVT_SEC_TRANS_FL
SIGNED_FORMS_FL
SUBMT_CORSP_FL
UNSUIT_RECOMDT_FL
OTSD_ACTY_FL
OTR_COMPL_CNCRN_FL


"Geoff" wrote:

> Please list the 34 fields names, eg:
>
> GIFTS_FL
> ?
> ?
> ?
> ....
>
> Geoff
>
>
> "IM4Jayhawks" <(E-Mail Removed)> wrote in message
> news:698F76E7-8C02-4CAC-8BE3-(E-Mail Removed)...
> > Thanks, Geoff! This may be over my head, but I'll give it a try. Where
> > do I
> > write the VBA function? Within my query or report?
> >
> >
> >
> > "Geoff" wrote:
> >
> >> The best way to do this would be to write a VBA function and call the
> >> function from the query.
> >>
> >> When you call the function from the query, you'd pass all 34 fields to
> >> the
> >> function. The function could return the string in any format you need,
> >> eg
> >> by omitting fields where the checkbox is false.
> >>
> >> It all depends on whether you want to write a function in a code module.
> >> If
> >> you feel up to it and need help, post again.
> >>
> >> Geoff
> >>
> >>
> >> "IM4Jayhawks" <(E-Mail Removed)> wrote in message
> >> news7182EC0-1CBE-4EFF-9BBE-(E-Mail Removed)...
> >> >I am working with a table (that unfortutely I cannot change) that uses
> >> > "yes/no" boxes for 34 different categories. I want to create a field
> >> > in a
> >> > report that shows the text of any categories with "Y" in it. I created
> >> > a
> >> > query to get the text:
> >> >
> >> > Example: Gifts: IIf([GIFTS_FL]="Y","Gifts","")
> >> >
> >> > It is possible for multiple categories to be selected. In a report, I
> >> > want
> >> > one field that lists the different categories, separated by a comma. I
> >> > can
> >> > do something like this in the query but then I have these "commas"
> >> > between
> >> > items that are "N".
> >> >
> >> > Is there any easier way to do this?
> >> >
> >> > Thanks!
> >>
> >>
> >>
>
>
>

THE BAD NEWS:

Unfortunately, it is not possible to pass all 34 fields to one VBA
function - the query complains it is too complicated.

THE GOOD NEWS:

However, it is perfectly possible to split the 34 fields into two groups of
17 fields. We can pass the first group of 17 fields to one VBA function and
the second group of 17 fields to a second VBA function. When these two
functions have done their stuff, we can get a third VBA function to combine
the results.

HOW TO DO IT:

1. Insert a new Module into your database. To do this, in the database
window, under Objects, click "Modules" and then click the "New" toolbar
button.

2. Copy and paste the following three functions into the module. Don't
worry if they look complicated - they're not really. Copy all the way down
to the heading "EDIT YOUR QUERY" (but don't copy that heading).


Public Function GetText1( _
INAPPROPRIATE_FL As Boolean, _
ALTERED_DOC_FL As Boolean, _
COMUNCTN_STDS_FL As Boolean, _
FAXED_ITEMS_FL As Boolean, _
FUNDING_ACCT_FL As Boolean, _
LETTERHEAD_FL As Boolean, _
INS_POLICIES_FL As Boolean, _
ANOTHER_ORG_FL As Boolean, _
BUS_SOLICTN_FL As Boolean, _
THIRD_PRTY_REQ_FL As Boolean, _
EMAIL_USE_FL As Boolean, _
OTSD_ACCOUNTS_FL As Boolean, _
CLNT_CMPLNT_FL As Boolean, _
CMPLNC_MTG_FL As Boolean, _
FIDU_CAPCTY_FL As Boolean, _
GIFTS_FL As Boolean, _
CLIENT_LOANS_FL As Boolean) As String

' Declare the variable "strText1".
' We shall use this variable to build
' the text we want in the query:
Dim strText1 As String

' Initialise "strText1" to no text:
strText1 = ""

' See if each field is TRUE; if so, add some text
' to build string. Feel free to change the text
' between the quotation marks ("...") to something
' that's appropriate. But remember to end the text
' with a comma and a space. You can change the order
' of the lines below using cut-and-paste, but be
' careful you do it correctly:

If INAPPROPRIATE_FL Then strText1 = strText1 & "Inappropriate, "
If ALTERED_DOC_FL Then strText1 = strText1 & "Altered, "
If COMUNCTN_STDS_FL Then strText1 = strText1 & "Comunctn, "
If FAXED_ITEMS_FL Then strText1 = strText1 & "Faxed, "
If FUNDING_ACCT_FL Then strText1 = strText1 & "Funding, "
If LETTERHEAD_FL Then strText1 = strText1 & "Letterhead, "
If INS_POLICIES_FL Then strText1 = strText1 & "Ins Policies, "
If ANOTHER_ORG_FL Then strText1 = strText1 & "Another Org, "
If BUS_SOLICTN_FL Then strText1 = strText1 & "Bus Solictn, "
If THIRD_PRTY_REQ_FL Then strText1 = strText1 & "Third Party, "
If EMAIL_USE_FL Then strText1 = strText1 & "Email Use, "
If OTSD_ACCOUNTS_FL Then strText1 = strText1 & "OTSD Accounts, "
If CLNT_CMPLNT_FL Then strText1 = strText1 & "Clnt Cmplnt, "
If CMPLNC_MTG_FL Then strText1 = strText1 & "Cmplnc Mtg, "
If FIDU_CAPCTY_FL Then strText1 = strText1 & "FIDU Capcty, "
If GIFTS_FL Then strText1 = strText1 & "Gifts, "
If CLIENT_LOANS_FL Then strText1 = strText1 & "Client Loans, "

' If strText1 contains more than two characters,
' then remove the final comma and space:
If Len(strText1) > 2 Then
strText1 = Left(strText1, Len(strText1) - 2)
End If

' Return the text to the query:
GetText1 = strText1

End Function

Public Function GetText2( _
PHONE_LSTG_FL As Boolean, _
SEP_OF_BUS_FL As Boolean, _
SUB_OF_BUS_FL As Boolean, _
TITLES_FL As Boolean, _
UNAPRVD_MATERL_FL As Boolean, _
ADVISOR_ASSTNT_FL As Boolean, _
FUNDS_CO_MINGLG_FL As Boolean, _
DISCRNY_AUTH_FL As Boolean, _
FORGERIES_FL As Boolean, _
INV_CLUB_FL As Boolean, _
MISREPRESENT_FL As Boolean, _
PRVT_SEC_TRANS_FL As Boolean, _
SIGNED_FORMS_FL As Boolean, _
SUBMT_CORSP_FL As Boolean, _
UNSUIT_RECOMDT_FL As Boolean, _
OTSD_ACTY_FL As Boolean, _
OTR_COMPL_CNCRN_FL As Boolean) As String


' Declare the variable "strText2".
' We shall use this variable to build
' the text we want in the query:
Dim strText2 As String

' Initialise "strText2" to no text:
strText2 = ""

' See if each field is TRUE; if so, add some text
' to build string. Feel free to change the text
' between the quotation marks ("...") to something
' that's appropriate. But remember to end the text
' with a comma and a space. You can change the order
' of the lines below using cut-and-paste, but be
' careful you do it correctly:

If PHONE_LSTG_FL Then strText2 = strText2 & "Phone Listing, "
If SEP_OF_BUS_FL Then strText2 = strText2 & "Sep of Bus, "
If SUB_OF_BUS_FL Then strText2 = strText2 & "Sub of Bus, "
If TITLES_FL Then strText2 = strText2 & "Titles, "
If UNAPRVD_MATERL_FL Then strText2 = strText2 _
& "Unapproved Material, "
If ADVISOR_ASSTNT_FL Then strText2 = strText2 & "Advisor Asst, "
If FUNDS_CO_MINGLG_FL Then strText2 = strText2 _
& "Funds Co Mingling, "
If DISCRNY_AUTH_FL Then strText2 = strText2 & "Discrny Auth, "
If FORGERIES_FL Then strText2 = strText2 & "Forgeries, "
If INV_CLUB_FL Then strText2 = strText2 & "Inv Club, "
If MISREPRESENT_FL Then strText2 = strText2 & "Misrepresent, "
If PRVT_SEC_TRANS_FL Then strText2 = strText2 & "Prvt Sec Trans, "
If SIGNED_FORMS_FL Then strText2 = strText2 & "Signed Forms, "
If SUBMT_CORSP_FL Then strText2 = strText2 & "Submt Corsp, "
If UNSUIT_RECOMDT_FL Then strText2 = strText2 & "Unsuit Recomdt, "
If OTSD_ACTY_FL Then strText2 = strText2 & "OTSD Acty, "
If OTR_COMPL_CNCRN_FL Then strText2 = strText2 _
& "OTR Compl Cncrn, "

' If strText2 contains more than two characters,
' then remove the final comma and space:
If Len(strText2) > 2 Then
strText2 = Left(strText2, Len(strText2) - 2)
End If

' Return the text to the query:
GetText2 = strText2

End Function

Public Function GetText3( _
MyText1 As String, _
MyText2 As String) As String

' Add a comma if necessary:
If Len(MyText1) > 0 And Len(MyText2) > 0 Then
MyText1 = MyText1 & ", "
End If

' Concatenate the two incoming strings:
GetText3 = MyText1 & MyText2

End Function


EDIT YOUR QUERY:

Now you must call the above functions from the query on which the report is
based. To do this:

3. Open the query in design view.

4. In the design grid, copy and paste the following blocks of text for
each of the three new fields: MyText1, MyText2 and MyText3. Bear in mind
that, in the Newsreader, you will see the text wrapping over a number of
lines. You need to copy and paste all the text for each new field,
including "MyText1: " and "MyText2: ", which appear on a line on their own.
(They appear on a line on their own because there is a space after the colon
: and before the "G" of GetText.). Here's the text you need to copy and
paste into three new columns of the design grid:


MyText1:
GetText1([INAPPROPRIATE_FL],[ALTERED_DOC_FL],[COMUNCTN_STDS_FL],[FAXED_ITEMS_FL],[FUNDING_ACCT_FL],[LETTERHEAD_FL],[INS_POLICIES_FL],[ANOTHER_ORG_FL],[BUS_SOLICTN_FL],[THIRD_PRTY_REQ_FL],[EMAIL_USE_FL],[OTSD_ACCOUNTS_FL],[CLNT_CMPLNT_FL],[CMPLNC_MTG_FL],[FIDU_CAPCTY_FL],[GIFTS_FL],[CLIENT_LOANS_FL])


MyText2:
GetText2([PHONE_LSTG_FL],[SEP_OF_BUS_FL],[SUB_OF_BUS_FL],[TITLES_FL],[UNAPRVD_MATERL_FL],[ADVISOR_ASSTNT_FL],[FUNDS_CO_MINGLG_FL],[DISCRNY_AUTH_FL],[FORGERIES_FL],[INV_CLUB_FL],[MISREPRESENT_FL],[PRVT_SEC_TRANS_FL],[SIGNED_FORMS_FL],[SUBMT_CORSP_FL],[UNSUIT_RECOMDT_FL],[OTSD_ACTY_FL],[OTR_COMPL_CNCRN_FL])


MyText3: GetText3([MyText1],[MyText2])


5. In the "Show" line of the design grid, deselect (uncheck) MyText1 and
MyText2, as you do not want these fields in your report.

6. Save the query.

7. Run the query. You should now see the field "MyText3", showing text
for the TRUE fields. (If it doesn't work on your system, double-check that
you have completed each step.)


EDIT YOUR REPORT:

8. To finish off, you need to ensure that your report is based on the
query you have just edited.

9. Then in your report, you need to add a textbox for the field MyText3.
You will have to make the textbox big enough to show all the text that might
appear if all the fields are TRUE.


CHANGING THE FUNCTIONS SO
1. THEY RETURN DIFFERENT TEXT
2. FIELDS ARE IN A DIFFERENT ORDER

10. The functions GetText1 and GetText2 (paragraph 2 above) are now in
the new module, which you can edit.

11. In GetText1 and GetText2, you have 17 "IF" statements. Each "IF"
statement ends in some text in quotation marks "...". You can change the
text between the quotation marks to something that is appropriate. I had to
guess what you might need. Be careful when you edit the functions. They
must be perfect!

11. Notice the text between the quotation marks ends in a comma and a
space. That's important.

12. You can change the order of the "If" statements if you prefer the
text to appear in a different order. If a line ends in an underscore
character _ then it continues on to the next line and you must cut-and-paste
the two lines together.

13. Notice (see paragraph 4 above) that the new fields in the query, ie
MyText1 and MyText2, pass the fields to the functions GetText1 and GetText2
in the order you listed them in your previous post. If you want to change
which fields are passed to which function, then it is essential that you
change BOTH the order of the fields in the query (paragraph 4 above) AND
change the order in which the fields are received by the functions. The
order in which the functions receive the fields is stated at the top of each
function (in the module) - for example, in paragraph 4 above,
[INAPPROPRIATE_FL] is the first field passed by the query to the GetText1
function and the following block shows that the GetText1 function (in the
module) expects to receive the [INAPPROPRIATE_FL] field first:

Public Function GetText1( _
INAPPROPRIATE_FL As Boolean, _
ALTERED_DOC_FL As Boolean, _
COMUNCTN_STDS_FL As Boolean, _
FAXED_ITEMS_FL As Boolean, _
FUNDING_ACCT_FL As Boolean, _
LETTERHEAD_FL As Boolean, _
INS_POLICIES_FL As Boolean, _
ANOTHER_ORG_FL As Boolean, _
BUS_SOLICTN_FL As Boolean, _
THIRD_PRTY_REQ_FL As Boolean, _
EMAIL_USE_FL As Boolean, _
OTSD_ACCOUNTS_FL As Boolean, _
CLNT_CMPLNT_FL As Boolean, _
CMPLNC_MTG_FL As Boolean, _
FIDU_CAPCTY_FL As Boolean, _
GIFTS_FL As Boolean, _
CLIENT_LOANS_FL As Boolean) As String


I hope you're not thoroughly confused!!!
Post again if anything doesn't make sense.
Good luck with your database. I hope it works the way you expected.

Geoff




"IM4Jayhawks" <(E-Mail Removed)> wrote in message
news:37233B9B-57A6-4E74-882D-(E-Mail Removed)...
> INAPPROPRIATE_FL
> ALTERED_DOC_FL
> COMUNCTN_STDS_FL
> FAXED_ITEMS_FL
> FUNDING_ACCT_FL
> LETTERHEAD_FL
> INS_POLICIES_FL
> ANOTHER_ORG_FL
> BUS_SOLICTN_FL
> THIRD_PRTY_REQ_FL
> EMAIL_USE_FL
> OTSD_ACCOUNTS_FL
> CLNT_CMPLNT_FL
> CMPLNC_MTG_FL
> FIDU_CAPCTY_FL
> GIFTS_FL
> CLIENT_LOANS_FL
> PHONE_LSTG_FL
> SEP_OF_BUS_FL
> SUB_OF_BUS_FL
> TITLES_FL
> UNAPRVD_MATERL_FL
> ADVISOR_ASSTNT_FL
> FUNDS_CO_MINGLG_FL
> DISCRNY_AUTH_FL
> FORGERIES_FL
> INV_CLUB_FL
> MISREPRESENT_FL
> PRVT_SEC_TRANS_FL
> SIGNED_FORMS_FL
> SUBMT_CORSP_FL
> UNSUIT_RECOMDT_FL
> OTSD_ACTY_FL
> OTR_COMPL_CNCRN_FL
>
>
> "Geoff" wrote:
>
>> Please list the 34 fields names, eg:
>>
>> GIFTS_FL
>> ?
>> ?
>> ?
>> ....
>>
>> Geoff
>>
>>
>> "IM4Jayhawks" <(E-Mail Removed)> wrote in message
>> news:698F76E7-8C02-4CAC-8BE3-(E-Mail Removed)...
>> > Thanks, Geoff! This may be over my head, but I'll give it a try.
>> > Where
>> > do I
>> > write the VBA function? Within my query or report?
>> >
>> >
>> >
>> > "Geoff" wrote:
>> >
>> >> The best way to do this would be to write a VBA function and call the
>> >> function from the query.
>> >>
>> >> When you call the function from the query, you'd pass all 34 fields to
>> >> the
>> >> function. The function could return the string in any format you
>> >> need,
>> >> eg
>> >> by omitting fields where the checkbox is false.
>> >>
>> >> It all depends on whether you want to write a function in a code
>> >> module.
>> >> If
>> >> you feel up to it and need help, post again.
>> >>
>> >> Geoff
>> >>
>> >>
>> >> "IM4Jayhawks" <(E-Mail Removed)> wrote in message
>> >> news7182EC0-1CBE-4EFF-9BBE-(E-Mail Removed)...
>> >> >I am working with a table (that unfortutely I cannot change) that
>> >> >uses
>> >> > "yes/no" boxes for 34 different categories. I want to create a
>> >> > field
>> >> > in a
>> >> > report that shows the text of any categories with "Y" in it. I
>> >> > created
>> >> > a
>> >> > query to get the text:
>> >> >
>> >> > Example: Gifts: IIf([GIFTS_FL]="Y","Gifts","")
>> >> >
>> >> > It is possible for multiple categories to be selected. In a report,
>> >> > I
>> >> > want
>> >> > one field that lists the different categories, separated by a comma.
>> >> > I
>> >> > can
>> >> > do something like this in the query but then I have these "commas"
>> >> > between
>> >> > items that are "N".
>> >> >
>> >> > Is there any easier way to do this?
>> >> >
>> >> > Thanks!
>> >>
>> >>
>> >>
>>
>>
>>

Postscript:

1. To be clear, in paragraph 4 of my previous post, you paste the text
for the three new fields, MyText1, MyText2 and MyText3, into the FIELD row
of the design grid, using a new column for each field.

2. In paragraph 9 of my previous post, I said you need to make the new
Textbox in the report (for the field MyText3) large enough to contain all
the text if all the fields are TRUE. In fact, you can make the Textbox just
one line long if you set the "Can Grow" property of the Textbox. To set
this property:

3. Open the report in design view.

4. Right-click the Textbox and click "Properties" on the right-click
menu.

5. In the Properties dialog, go to the "Format" page and click in the
"Can Grow" property. Click the down-arrow to the right of the property and
select "Yes".

6. You might also want to set the "Can Shrink" property to "Yes".

7. Now, the Textbox will get larger or smaller (vertically) to
accommodate whatever text is in the MyText3 field.

Geoff




"Geoff" <(E-Mail Removed)> wrote in message
news:OOkK$(E-Mail Removed)...
> THE BAD NEWS:
>
> Unfortunately, it is not possible to pass all 34 fields to one VBA
> function - the query complains it is too complicated.
>
> THE GOOD NEWS:
>
> However, it is perfectly possible to split the 34 fields into two groups
> of
> 17 fields. We can pass the first group of 17 fields to one VBA function
> and
> the second group of 17 fields to a second VBA function. When these two
> functions have done their stuff, we can get a third VBA function to
> combine
> the results.
>
> HOW TO DO IT:
>
> 1. Insert a new Module into your database. To do this, in the database
> window, under Objects, click "Modules" and then click the "New" toolbar
> button.
>
> 2. Copy and paste the following three functions into the module. Don't
> worry if they look complicated - they're not really. Copy all the way
> down
> to the heading "EDIT YOUR QUERY" (but don't copy that heading).
>
>
> Public Function GetText1( _
> INAPPROPRIATE_FL As Boolean, _
> ALTERED_DOC_FL As Boolean, _
> COMUNCTN_STDS_FL As Boolean, _
> FAXED_ITEMS_FL As Boolean, _
> FUNDING_ACCT_FL As Boolean, _
> LETTERHEAD_FL As Boolean, _
> INS_POLICIES_FL As Boolean, _
> ANOTHER_ORG_FL As Boolean, _
> BUS_SOLICTN_FL As Boolean, _
> THIRD_PRTY_REQ_FL As Boolean, _
> EMAIL_USE_FL As Boolean, _
> OTSD_ACCOUNTS_FL As Boolean, _
> CLNT_CMPLNT_FL As Boolean, _
> CMPLNC_MTG_FL As Boolean, _
> FIDU_CAPCTY_FL As Boolean, _
> GIFTS_FL As Boolean, _
> CLIENT_LOANS_FL As Boolean) As String
>
> ' Declare the variable "strText1".
> ' We shall use this variable to build
> ' the text we want in the query:
> Dim strText1 As String
>
> ' Initialise "strText1" to no text:
> strText1 = ""
>
> ' See if each field is TRUE; if so, add some text
> ' to build string. Feel free to change the text
> ' between the quotation marks ("...") to something
> ' that's appropriate. But remember to end the text
> ' with a comma and a space. You can change the order
> ' of the lines below using cut-and-paste, but be
> ' careful you do it correctly:
>
> If INAPPROPRIATE_FL Then strText1 = strText1 & "Inappropriate, "
> If ALTERED_DOC_FL Then strText1 = strText1 & "Altered, "
> If COMUNCTN_STDS_FL Then strText1 = strText1 & "Comunctn, "
> If FAXED_ITEMS_FL Then strText1 = strText1 & "Faxed, "
> If FUNDING_ACCT_FL Then strText1 = strText1 & "Funding, "
> If LETTERHEAD_FL Then strText1 = strText1 & "Letterhead, "
> If INS_POLICIES_FL Then strText1 = strText1 & "Ins Policies, "
> If ANOTHER_ORG_FL Then strText1 = strText1 & "Another Org, "
> If BUS_SOLICTN_FL Then strText1 = strText1 & "Bus Solictn, "
> If THIRD_PRTY_REQ_FL Then strText1 = strText1 & "Third Party, "
> If EMAIL_USE_FL Then strText1 = strText1 & "Email Use, "
> If OTSD_ACCOUNTS_FL Then strText1 = strText1 & "OTSD Accounts, "
> If CLNT_CMPLNT_FL Then strText1 = strText1 & "Clnt Cmplnt, "
> If CMPLNC_MTG_FL Then strText1 = strText1 & "Cmplnc Mtg, "
> If FIDU_CAPCTY_FL Then strText1 = strText1 & "FIDU Capcty, "
> If GIFTS_FL Then strText1 = strText1 & "Gifts, "
> If CLIENT_LOANS_FL Then strText1 = strText1 & "Client Loans, "
>
> ' If strText1 contains more than two characters,
> ' then remove the final comma and space:
> If Len(strText1) > 2 Then
> strText1 = Left(strText1, Len(strText1) - 2)
> End If
>
> ' Return the text to the query:
> GetText1 = strText1
>
> End Function
>
> Public Function GetText2( _
> PHONE_LSTG_FL As Boolean, _
> SEP_OF_BUS_FL As Boolean, _
> SUB_OF_BUS_FL As Boolean, _
> TITLES_FL As Boolean, _
> UNAPRVD_MATERL_FL As Boolean, _
> ADVISOR_ASSTNT_FL As Boolean, _
> FUNDS_CO_MINGLG_FL As Boolean, _
> DISCRNY_AUTH_FL As Boolean, _
> FORGERIES_FL As Boolean, _
> INV_CLUB_FL As Boolean, _
> MISREPRESENT_FL As Boolean, _
> PRVT_SEC_TRANS_FL As Boolean, _
> SIGNED_FORMS_FL As Boolean, _
> SUBMT_CORSP_FL As Boolean, _
> UNSUIT_RECOMDT_FL As Boolean, _
> OTSD_ACTY_FL As Boolean, _
> OTR_COMPL_CNCRN_FL As Boolean) As String
>
>
> ' Declare the variable "strText2".
> ' We shall use this variable to build
> ' the text we want in the query:
> Dim strText2 As String
>
> ' Initialise "strText2" to no text:
> strText2 = ""
>
> ' See if each field is TRUE; if so, add some text
> ' to build string. Feel free to change the text
> ' between the quotation marks ("...") to something
> ' that's appropriate. But remember to end the text
> ' with a comma and a space. You can change the order
> ' of the lines below using cut-and-paste, but be
> ' careful you do it correctly:
>
> If PHONE_LSTG_FL Then strText2 = strText2 & "Phone Listing, "
> If SEP_OF_BUS_FL Then strText2 = strText2 & "Sep of Bus, "
> If SUB_OF_BUS_FL Then strText2 = strText2 & "Sub of Bus, "
> If TITLES_FL Then strText2 = strText2 & "Titles, "
> If UNAPRVD_MATERL_FL Then strText2 = strText2 _
> & "Unapproved Material, "
> If ADVISOR_ASSTNT_FL Then strText2 = strText2 & "Advisor Asst, "
> If FUNDS_CO_MINGLG_FL Then strText2 = strText2 _
> & "Funds Co Mingling, "
> If DISCRNY_AUTH_FL Then strText2 = strText2 & "Discrny Auth, "
> If FORGERIES_FL Then strText2 = strText2 & "Forgeries, "
> If INV_CLUB_FL Then strText2 = strText2 & "Inv Club, "
> If MISREPRESENT_FL Then strText2 = strText2 & "Misrepresent, "
> If PRVT_SEC_TRANS_FL Then strText2 = strText2 & "Prvt Sec Trans, "
> If SIGNED_FORMS_FL Then strText2 = strText2 & "Signed Forms, "
> If SUBMT_CORSP_FL Then strText2 = strText2 & "Submt Corsp, "
> If UNSUIT_RECOMDT_FL Then strText2 = strText2 & "Unsuit Recomdt, "
> If OTSD_ACTY_FL Then strText2 = strText2 & "OTSD Acty, "
> If OTR_COMPL_CNCRN_FL Then strText2 = strText2 _
> & "OTR Compl Cncrn, "
>
> ' If strText2 contains more than two characters,
> ' then remove the final comma and space:
> If Len(strText2) > 2 Then
> strText2 = Left(strText2, Len(strText2) - 2)
> End If
>
> ' Return the text to the query:
> GetText2 = strText2
>
> End Function
>
> Public Function GetText3( _
> MyText1 As String, _
> MyText2 As String) As String
>
> ' Add a comma if necessary:
> If Len(MyText1) > 0 And Len(MyText2) > 0 Then
> MyText1 = MyText1 & ", "
> End If
>
> ' Concatenate the two incoming strings:
> GetText3 = MyText1 & MyText2
>
> End Function
>
>
> EDIT YOUR QUERY:
>
> Now you must call the above functions from the query on which the report
> is based. To do this:
>
> 3. Open the query in design view.
>
> 4. In the design grid, copy and paste the following blocks of text for
> each of the three new fields: MyText1, MyText2 and MyText3. Bear in mind
> that, in the Newsreader, you will see the text wrapping over a number of
> lines. You need to copy and paste all the text for each new field,
> including "MyText1: " and "MyText2: ", which appear on a line on their
> own. (They appear on a line on their own because there is a space after
> the colon : and before the "G" of GetText.). Here's the text you need to
> copy and paste into three new columns of the design grid:
>
>
> MyText1:
> GetText1([INAPPROPRIATE_FL],[ALTERED_DOC_FL],[COMUNCTN_STDS_FL],[FAXED_ITEMS_FL],[FUNDING_ACCT_FL],[LETTERHEAD_FL],[INS_POLICIES_FL],[ANOTHER_ORG_FL],[BUS_SOLICTN_FL],[THIRD_PRTY_REQ_FL],[EMAIL_USE_FL],[OTSD_ACCOUNTS_FL],[CLNT_CMPLNT_FL],[CMPLNC_MTG_FL],[FIDU_CAPCTY_FL],[GIFTS_FL],[CLIENT_LOANS_FL])
>
>
> MyText2:
> GetText2([PHONE_LSTG_FL],[SEP_OF_BUS_FL],[SUB_OF_BUS_FL],[TITLES_FL],[UNAPRVD_MATERL_FL],[ADVISOR_ASSTNT_FL],[FUNDS_CO_MINGLG_FL],[DISCRNY_AUTH_FL],[FORGERIES_FL],[INV_CLUB_FL],[MISREPRESENT_FL],[PRVT_SEC_TRANS_FL],[SIGNED_FORMS_FL],[SUBMT_CORSP_FL],[UNSUIT_RECOMDT_FL],[OTSD_ACTY_FL],[OTR_COMPL_CNCRN_FL])
>
>
> MyText3: GetText3([MyText1],[MyText2])
>
>
> 5. In the "Show" line of the design grid, deselect (uncheck) MyText1
> and
> MyText2, as you do not want these fields in your report.
>
> 6. Save the query.
>
> 7. Run the query. You should now see the field "MyText3", showing text
> for the TRUE fields. (If it doesn't work on your system, double-check
> that you have completed each step.)
>
>
> EDIT YOUR REPORT:
>
> 8. To finish off, you need to ensure that your report is based on the
> query you have just edited.
>
> 9. Then in your report, you need to add a textbox for the field
> MyText3.
> You will have to make the textbox big enough to show all the text that
> might
> appear if all the fields are TRUE.
>
>
> CHANGING THE FUNCTIONS SO
> 1. THEY RETURN DIFFERENT TEXT
> 2. FIELDS ARE IN A DIFFERENT ORDER
>
> 10. The functions GetText1 and GetText2 (paragraph 2 above) are now in
> the new module, which you can edit.
>
> 11. In GetText1 and GetText2, you have 17 "IF" statements. Each "IF"
> statement ends in some text in quotation marks "...". You can change the
> text between the quotation marks to something that is appropriate. I had
> to guess what you might need. Be careful when you edit the functions.
> They must be perfect!
>
> 11. Notice the text between the quotation marks ends in a comma and a
> space. That's important.
>
> 12. You can change the order of the "If" statements if you prefer the
> text to appear in a different order. If a line ends in an underscore
> character _ then it continues on to the next line and you must
> cut-and-paste the two lines together.
>
> 13. Notice (see paragraph 4 above) that the new fields in the query, ie
> MyText1 and MyText2, pass the fields to the functions GetText1 and
> GetText2 in the order you listed them in your previous post. If you want
> to change which fields are passed to which function, then it is essential
> that you change BOTH the order of the fields in the query (paragraph 4
> above) AND change the order in which the fields are received by the
> functions. The order in which the functions receive the fields is stated
> at the top of each function (in the module) - for example, in paragraph 4
> above, [INAPPROPRIATE_FL] is the first field passed by the query to the
> GetText1 function and the following block shows that the GetText1 function
> (in the module) expects to receive the [INAPPROPRIATE_FL] field first:
>
> Public Function GetText1( _
> INAPPROPRIATE_FL As Boolean, _
> ALTERED_DOC_FL As Boolean, _
> COMUNCTN_STDS_FL As Boolean, _
> FAXED_ITEMS_FL As Boolean, _
> FUNDING_ACCT_FL As Boolean, _
> LETTERHEAD_FL As Boolean, _
> INS_POLICIES_FL As Boolean, _
> ANOTHER_ORG_FL As Boolean, _
> BUS_SOLICTN_FL As Boolean, _
> THIRD_PRTY_REQ_FL As Boolean, _
> EMAIL_USE_FL As Boolean, _
> OTSD_ACCOUNTS_FL As Boolean, _
> CLNT_CMPLNT_FL As Boolean, _
> CMPLNC_MTG_FL As Boolean, _
> FIDU_CAPCTY_FL As Boolean, _
> GIFTS_FL As Boolean, _
> CLIENT_LOANS_FL As Boolean) As String
>
>
> I hope you're not thoroughly confused!!!
> Post again if anything doesn't make sense.
> Good luck with your database. I hope it works the way you expected.
>
> Geoff
>
>
>
>
> "IM4Jayhawks" <(E-Mail Removed)> wrote in message
> news:37233B9B-57A6-4E74-882D-(E-Mail Removed)...
>> INAPPROPRIATE_FL
>> ALTERED_DOC_FL
>> COMUNCTN_STDS_FL
>> FAXED_ITEMS_FL
>> FUNDING_ACCT_FL
>> LETTERHEAD_FL
>> INS_POLICIES_FL
>> ANOTHER_ORG_FL
>> BUS_SOLICTN_FL
>> THIRD_PRTY_REQ_FL
>> EMAIL_USE_FL
>> OTSD_ACCOUNTS_FL
>> CLNT_CMPLNT_FL
>> CMPLNC_MTG_FL
>> FIDU_CAPCTY_FL
>> GIFTS_FL
>> CLIENT_LOANS_FL
>> PHONE_LSTG_FL
>> SEP_OF_BUS_FL
>> SUB_OF_BUS_FL
>> TITLES_FL
>> UNAPRVD_MATERL_FL
>> ADVISOR_ASSTNT_FL
>> FUNDS_CO_MINGLG_FL
>> DISCRNY_AUTH_FL
>> FORGERIES_FL
>> INV_CLUB_FL
>> MISREPRESENT_FL
>> PRVT_SEC_TRANS_FL
>> SIGNED_FORMS_FL
>> SUBMT_CORSP_FL
>> UNSUIT_RECOMDT_FL
>> OTSD_ACTY_FL
>> OTR_COMPL_CNCRN_FL
>>
>>
>> "Geoff" wrote:
>>
>>> Please list the 34 fields names, eg:
>>>
>>> GIFTS_FL
>>> ?
>>> ?
>>> ?
>>> ....
>>>
>>> Geoff
>>>
>>>
>>> "IM4Jayhawks" <(E-Mail Removed)> wrote in message
>>> news:698F76E7-8C02-4CAC-8BE3-(E-Mail Removed)...
>>> > Thanks, Geoff! This may be over my head, but I'll give it a try.
>>> > Where
>>> > do I
>>> > write the VBA function? Within my query or report?
>>> >
>>> >
>>> >
>>> > "Geoff" wrote:
>>> >
>>> >> The best way to do this would be to write a VBA function and call the
>>> >> function from the query.
>>> >>
>>> >> When you call the function from the query, you'd pass all 34 fields
>>> >> to
>>> >> the
>>> >> function. The function could return the string in any format you
>>> >> need,
>>> >> eg
>>> >> by omitting fields where the checkbox is false.
>>> >>
>>> >> It all depends on whether you want to write a function in a code
>>> >> module.
>>> >> If
>>> >> you feel up to it and need help, post again.
>>> >>
>>> >> Geoff
>>> >>
>>> >>
>>> >> "IM4Jayhawks" <(E-Mail Removed)> wrote in
>>> >> message
>>> >> news7182EC0-1CBE-4EFF-9BBE-(E-Mail Removed)...
>>> >> >I am working with a table (that unfortutely I cannot change) that
>>> >> >uses
>>> >> > "yes/no" boxes for 34 different categories. I want to create a
>>> >> > field
>>> >> > in a
>>> >> > report that shows the text of any categories with "Y" in it. I
>>> >> > created
>>> >> > a
>>> >> > query to get the text:
>>> >> >
>>> >> > Example: Gifts: IIf([GIFTS_FL]="Y","Gifts","")
>>> >> >
>>> >> > It is possible for multiple categories to be selected. In a
>>> >> > report,
>>> >> > I
>>> >> > want
>>> >> > one field that lists the different categories, separated by a
>>> >> > comma.
>>> >> > I
>>> >> > can
>>> >> > do something like this in the query but then I have these "commas"
>>> >> > between
>>> >> > items that are "N".
>>> >> >
>>> >> > Is there any easier way to do this?
>>> >> >
>>> >> > Thanks!
>>> >>
>>> >>
>>> >>
>>>
>>>
>>>
>
>
>
>
>
>
>
>
>
>
>
>