Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

How do I take yes/no fields & combine into one text field?

Discussion in 'Information Technology' started by IM4Jayhawks, Jul 28, 2006.

  1. IM4Jayhawks

    IM4Jayhawks
    Expand Collapse
    Guest

    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!
     
  2. Loading...

    Similar Threads Forum Date
    US Defense Department Hiring Resident Aliens in Medical and Language Fields Business, Lifestyle & Leisure Dec 6, 2008
    Sikh News Women more vulnerable to cancer in Punjab's cotton fields (New Kerala) Breaking News Apr 1, 2008
    Sikh News Women more vulnerable to cancer in Punjab's cotton fields (Calcutta News) Breaking News Apr 1, 2008
    Sikh News Punjab's killing fields (The Times of India) Breaking News Feb 24, 2008
    Sikh News MQM fields 218 candidates in Punjab (The Pakistan Link) Breaking News Feb 1, 2008

  3. Geoff

    Geoff
    Expand Collapse
    Guest

    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" <IM4Jayhawks@discussions.microsoft.com> wrote in message
    news:D7182EC0-1CBE-4EFF-9BBE-1C00F67D86E0@microsoft.com...
    >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!
     
  4. IM4Jayhawks

    IM4Jayhawks
    Expand Collapse
    Guest

    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" <IM4Jayhawks@discussions.microsoft.com> wrote in message
    > news:D7182EC0-1CBE-4EFF-9BBE-1C00F67D86E0@microsoft.com...
    > >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!

    >
    >
    >
     
  5. Geoff

    Geoff
    Expand Collapse
    Guest

    Please list the 34 fields names, eg:

    GIFTS_FL
    ?
    ?
    ?
    ....

    Geoff


    "IM4Jayhawks" <IM4Jayhawks@discussions.microsoft.com> wrote in message
    news:698F76E7-8C02-4CAC-8BE3-4B49D4854001@microsoft.com...
    > 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" <IM4Jayhawks@discussions.microsoft.com> wrote in message
    >> news:D7182EC0-1CBE-4EFF-9BBE-1C00F67D86E0@microsoft.com...
    >> >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!

    >>
    >>
    >>
     
  6. IM4Jayhawks

    IM4Jayhawks
    Expand Collapse
    Guest

    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" <IM4Jayhawks@discussions.microsoft.com> wrote in message
    > news:698F76E7-8C02-4CAC-8BE3-4B49D4854001@microsoft.com...
    > > 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" <IM4Jayhawks@discussions.microsoft.com> wrote in message
    > >> news:D7182EC0-1CBE-4EFF-9BBE-1C00F67D86E0@microsoft.com...
    > >> >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!
    > >>
    > >>
    > >>

    >
    >
    >
     
  7. Geoff

    Geoff
    Expand Collapse
    Guest

    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" <IM4Jayhawks@discussions.microsoft.com> wrote in message
    news:37233B9B-57A6-4E74-882D-A07F2DF1BD38@microsoft.com...
    > 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" <IM4Jayhawks@discussions.microsoft.com> wrote in message
    >> news:698F76E7-8C02-4CAC-8BE3-4B49D4854001@microsoft.com...
    >> > 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" <IM4Jayhawks@discussions.microsoft.com> wrote in message
    >> >> news:D7182EC0-1CBE-4EFF-9BBE-1C00F67D86E0@microsoft.com...
    >> >> >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!
    >> >>
    >> >>
    >> >>

    >>
    >>
    >>
     
  8. Geoff

    Geoff
    Expand Collapse
    Guest

    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" <geoff@nospam.com> wrote in message
    news:OOkK$gjlGHA.408@TK2MSFTNGP03.phx.gbl...
    > 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" <IM4Jayhawks@discussions.microsoft.com> wrote in message
    > news:37233B9B-57A6-4E74-882D-A07F2DF1BD38@microsoft.com...
    >> 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" <IM4Jayhawks@discussions.microsoft.com> wrote in message
    >>> news:698F76E7-8C02-4CAC-8BE3-4B49D4854001@microsoft.com...
    >>> > 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" <IM4Jayhawks@discussions.microsoft.com> wrote in
    >>> >> message
    >>> >> news:D7182EC0-1CBE-4EFF-9BBE-1C00F67D86E0@microsoft.com...
    >>> >> >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!
    >>> >>
    >>> >>
    >>> >>
    >>>
    >>>
    >>>

    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
     

Share This Page