reports with a varying number of fields as input

[Anthony]

Hi

I have a report based on a query which, for some records, may have certain
fields that are empty. These particular fields are based on combo boxes in
the underlying table.

How do I go about creating a report that will produce grammatically correct
sentences for all records, even if the number of fields with values in them
changes in the underlying query.

To illustrate what I mean, just say I have four fields in my query
containing the following values:
Field No. 1: "A"
Field No. 2: "B"
Field No. 3: "C"
Field No. 4: "D"

I want to be able to produce a report that will be grammatically correct no
matter how many (or which) of these four fields contain data. For example:

The values are "A", "B", "C" and "D".
The values are "A", "B" and "C".
The values are "A" and "B".
The values are "A", "C" and "D".
The values are "B" and "D".
......etc, etc.

Any suggestions?
Anthony

 

[Jeff Boyce]

Anthony

Access isn't magic. The way to solve your problem is to figure out how YOU
solve it. Then tell Access to do that.

How do you know whether to put a comma (",") or the word "and" between two
"fields"? How do you know when to add a period (".")?

Derive all the rules you apply, then build some code behind the report that
does the same thing to build an unbound text control's source.

Good luck

Jeff Boyce
<Access MVP>

 

[Anthony]

Thanks for the hint Jeff. I made up some code that I think may produce the
desired results (see below), but the text box I created on my report to test
if this code works has a blank Event tab in the Properties Box.

1. Am I on the right track with respect to the code.
2. How do I enter this code into the Properties box of the unbound text box.

Private Sub GetData()

Dim optype1 As String
Dim optype2 As String
Dim optype3 As String
Dim optype4 As String

optype1 = Query![Operation].[OperationType]
optype2 = Query![Operation].[OperationSubType]
optype3 = Query![Operation].[OtherOptionsA]
optype4 = Query![Operation].[OtherOptonsB]

If (optype2 = Empty) & (optype3 = Empty) & (optype4 = Empty) Then
[Operation].[OperationName] = optype1
ElseIf (optype3 = Empty) & (optype4 = Empty) Then
[Operation].[OperationName] = optype1 & " and " & optype2
'etc., etc., etc....

End If
End Sub

Thanks in advance for any further suggestions.
Anthony

 

[Jeff Boyce]

Anthony

The "value" of your text control in your report needs to be the result of
your function. One approach might be to use the OnFormat event of the
report section (?Detail section) in which the text control lives to run the
function and set the value.

"Empty" has a different meaning to Access than "Null". And if your
underlying data fields might contain zero length strings, you'd need to test
for those as well. Here's a simple combination:

If Nz([YourField],"") = "" Then
...

Just a thought, but would it make sense to "work backwards"? The last field
containing a value needs a period after it, right? So it would also be
possible to build a text string by adding the fields in front of each other.
I suspect you'll still need to know how many, to know if you need a comma
and/or an "and".

Good luck

Jeff Boyce
<Access MVP>

 

[Anthony]

Thanks for your suggestions, Jeff. I didn't have much luck getting the code
to run in OnFormat for the Detail section of my report, so I decided to
reorganise my table so that there are only two fields instead of four. I
then created an expression in my query: IIf(IsNull([FieldB]),
[FieldA],[FieldA]&" and "&[FieldB]). Works a treat and the data are better
organised.

Anthony