THE BAD
Unfortunately, it is not possible to pass all 34 fields to one VBA
function - the query complains it is too complicated.
THE GOOD
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 said:
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 said:
Please list the 34 fields names, eg:
GIFTS_FL
?
?
?
....
Geoff
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?
:
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
message
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!
