Displaying records in different field order

  • Thread starter Thread starter francvs
  • Start date Start date
F

francvs

hi all, I am trying to set up a report for a multimedia library. The
report must display the records from a query in different ways,
depending on the value of a particular field. More precisely, the
report should show, for example, 'title' as first field of the row if
the corresponding record refers to a 'review' type; if the type is
'book', the first field displayed should be 'author', etc.

At the same time, blank fields (in my database there are plenty of)
should be suppressed, so that the resulting row makes sense as a
coherent bibliographic entry.
I think I need to write a function in VBA in order to build the
necessary string... but as I am new to Access, it's a bit hard to me...
As for the empty fields, I have already seen solutions like CanGrow and
CanShrink, but they seem to me unfitting, as blanks in a single row
happen to be many...

Any hint for the way to go?
Thanks in advance.
 
If you wanted fixed-width fields that swap places on your report, it would
be possible to use the Format event of the (Detail?) section on your report
to set the Left property of each text box so they are laid out the way you
want. (You can set the Width too, but this is not simple to work out for
proportional fonts.)

If you want to concatenate several fields together into one string that
becomes a biography entry, you probably want to pass all the fields to a
function in VBA, and use Select Case to arrange the items in the string.

If you create function like the one below, you could have one wide text box
on your report, and set its ControlSource to:
=ShowBio([EntryType], [Title], [Author], [Year])

Function ShowBio(varEntryType, varTitle, varAuthor, varYear) As Variant
Dim varResult As Variant
Select Case varEntryType
Case "review"
varResult = varTitle & ", " & varAuthor & ", " & varYear
Case "book"
varResult = varAuthor & strcSep & " (" & varYear & ") " &
varTitle
Case Else
varResult = Null
End Select
ShowBio = varResult
End Function
 
Back
Top