building a title

[gwog]

using Access 97.

I have a form which contains several combo boxes. The user may select any number of combinations for the combo boxes (ie. select someting in 1 combo box or in 2 different combo boxes or in all of the combo boxes).

Then the user clicks a command button which opens a report (in preview) which uses a query as the source (whereby data filtered based on combo choices).

I am trying to build a title at the top which will print which combo box selections were made on the form. I was able to make it give me something that will work if a selection is made for each combo box. But when only 1 combo box has a selection the outcome is not very pretty.

Here is the code I have for source of text box on report. (Not very pretty I know):

=IIf([Forms]![frmReasonCodeAnalysis]![comboCG] Is Null," ","Cust Group of " & [Forms]![frmReasonCodeAnalysis]![comboCG]) & IIf([Forms]![frmReasonCodeAnalysis]![comboCustomer] Is Null,"and "," and Customer of " & [Forms]![frmReasonCodeAnalysis]![comboCustomer]) & IIf([Forms]![frmReasonCodeAnalysis]![cboChannel] Is Null," and "," and Channel of " & [Forms]![frmReasonCodeAnalysis]![cboChannel]) & IIf([Forms]![frmReasonCodeAnalysis]![combolob] Is Null," and "," and LOB of " & [Forms]![frmReasonCodeAnalysis]![combolob]) & IIf([Forms]![frmReasonCodeAnalysis]![comboBrand] Is Null," and "," and Brand of " & [Forms]![frmReasonCodeAnalysis]![comboBrand]) & IIf([Forms]![frmReasonCodeAnalysis]![comboSCCAT] Is Null," and "," and SCCAT of " & [Forms]![frmReasonCodeAnalysis]![comboSCCAT]) & IIf([Forms]![frmReasonCodeAnalysis]![comboSG] Is Null," and "," and Supply Group of " & [Forms]![frmReasonCodeAnalysis]![comboSG]) & IIf([Forms]![frmReasonCodeAnalysis]![combodc] Is Null," and "," and Location of " & [Forms]![frmReasonCodeAnalysis]![combodc]) & IIf([Forms]![frmReasonCodeAnalysis]![comboBucat] Is Null," and "," and BU Cat of " & [Forms]![frmReasonCodeAnalysis]![comboBucat]) & IIf([Forms]![frmReasonCodeAnalysis]![comboBusubcat] Is Null," and "," and BU Subcat of " & [Forms]![frmReasonCodeAnalysis]![comboBuSubcat]) & IIf([Forms]![frmReasonCodeAnalysis]![comboitemno] Is Null," and "," and ITEM# " & [Forms]![frmReasonCodeAnalysis]![comboitemno])


Which gives me the followg if only 2 combo box selections are made:

Cust Group of and Customer of and Channel of and LOB of LUNCH and Brand of and SCCAT of HOT DOGS and and Location of and BU Cat of and BU Subcat of and ITEM#

How can I make it just give me the wording and selection for the 2 combo boxes that contain data?

Thanks!

 

[gwog]

clarification.

The two combo boxes that contain data are:
combo name: Selection:
comboLOB LUNCH
comboSCCAT HOT DOGS


thanks.

 

[GingerR]

try also checking for "" as well as null

If you want it prettier and easier to maintain, try putting the code into the OnClick of a button that launches the report. Set a text box on your form to this Title value, then open the report.

Something like this (untested):

dim strTitle as string
strTitle = ""

if me.comboCG <> "" and not isnull(me.comboCG) then
   strTitle = "Cust Group of " & me.comboCG
end if

if me.comboCustomer <> "" and not isnull(me.comboCustomerCustomer) then
    if len(strTitle) = 0 then
       strTitle = "Customer of " & me.comboCustomer
    else
       strTitle = strTitle & ", Customer of " & me.comboCustomer
    end if
end if

{repeat above for each item}
.
.
.

me.txtTitle = strTitle
docmd.openReport "blah", acViewPreview

and in the report, the title text box can simply be
=Forms![frmReasonCodeAnalysis]![txtTitle]

Hope this helps--g

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244