Access Report: Using CanShrinkLines(ParamArray arrLines()) and Forced Format to Uppercase

Question

Hi,

I have created a contact directory report with 6 columns.  I am using CanShrinkLines(ParamArray arrLines()) along with forcing the field Format to > for Uppercase.  I have added a field within the CanShrinkLines string.  Part of the way thru the report, some of the data is not being forced to Uppercase.  When I remove the field from the string, the Uppercase works fine.  It is random within the report and I can't figure out why this is happening.  Any suggestions?

Answer 1

I may give UCase a try at the beginning ex:  UCASE(CanShrinkLines(etc etc etc))

If your only problem is with the capitalization, that ought to work in place of the ">" format string.  However, I'd expect the stray parenthesis in the call to CanShrinkLines to still cause some sort of problem.

Answer 2

The control source is as follows:

=CanShrinkLines([LastName]+", "+[FirstName]+" "+[Degree],[Location Name],[Address1],[Address2],[City]+", "+[State]+" "+[Zip],[Voice Phone],[Specialty_Description]+"  "+" Gender: "+[Provider Gender],"NPI: "+[Provider NPI],"State Lic: "+[STATE_LIC]),[Handicap Accessible],"Office Language: "+[Languages at Location Level])

When I added State Lic, for some, everything after NPI is lower case.  It is random through out the report.

You have a stray closing parenthesis after [STATE_LIC].  Try this version:

=CanShrinkLines([LastName]+", "+[FirstName]+" "+[Degree],[Location Name],[Address1],[Address2],[City]+", "+[State]+" "+[Zip],[Voice Phone],[Specialty_Description]+"  "+" Gender: "+[Provider Gender],"NPI: "+[Provider NPI],"State Lic: "+[STATE_LIC],[Handicap Accessible],"Office Language: "+[Languages at Location Level])

Answer 3

I may give UCase a try at the beginning ex:  UCASE(CanShrinkLines(etc etc etc))

Answer 4

The module is as follows:

Public Function CanShrinkLines(ParamArray arrLines())

    ' Pass this function the lines to be combined

    ' For example: strAddress =

    ' CanShrinkLines(Name, Address1, Address2, City, State, Zip)

    Dim X As Integer, strLine As String

    For X = 0 To UBound(arrLines)

        If Not IsNull(arrLines(X)) And Trim(arrLines(X)) <> "" Then

          strLine = strLine & vbCrLf & arrLines(X)

        End If

    Next

    ' remove leading carriage return/line feed

    CanShrinkLines = Mid(strLine, 3)

End Function

I am using > in the Format of the Property Sheet for the Text box where I use CanShrinkLines.

The control source is as follows:

=CanShrinkLines([LastName]+", "+[FirstName]+" "+[Degree],[Location Name],[Address1],[Address2],[City]+", "+[State]+" "+[Zip],[Voice Phone],[Specialty_Description]+"  "+" Gender: "+[Provider Gender],"NPI: "+[Provider NPI],"State Lic: "+[STATE_LIC]),[Handicap Accessible],"Office Language: "+[Languages at Location Level])

When I added State Lic, for some, everything after NPI is lower case.  It is random through out the report.

Answer 5

I believe CanShrinkLines is a custom VBA function; possibly the one posted by Rey Obrero here.  Did you modify this function?  If so, please post the modified function.  If that's not the function, please post the function you are using.

You say you are forcing the field to upper case with the ">" format character.  This is applied in the Format property of a text box?  How are you invoking the CanShrinkLines function?  What is the ControlSource of the text box?