How to print underscores instead of a field value, in a report (4 Viewers)

[HCL]

I'm sorry if this has already been asked.
I am printing out a quiz sheet and want to print out a series of underscores, alongside the question, that represents the length of the answer.
For example, for the State Capital of Alabama to produce __________ (10 underscores for the answer Montgomery). Obviously, the number of underscores printed will vary according to the number of characters in the answer.
I'm sure there is an easy way of doing this but with my limited knowledge of access, I'm struggling.
Thanks.

 

[tvanstiphout]

dim s1 as string
dim s2 as string
s1 = "Montgomery"
s2 = String$(Len(s1), "_")
debug.print s1, s2

This prints:
Montgomery __________

 

[MarkK]

You might prefer something like...

Sub Test()
    Debug.Print GetRSPUnderscore("Montgomery")
End Sub

Private Function GetRSPUnderscore(Text As String) As String
    Const PER_CHAR As Integer = 4
    Dim i As Integer
    Dim tmp
    
    For i = 1 To Len(Text)
        tmp = tmp & " " & String(PER_CHAR, "_")
    Next
    
    GetRSPUnderscore = Trim(tmp)
End Function

Then in the report Detail_Format() event (where presumably you have the answer in a hidden textbox tbHiddenAnswer and a textbox to display your underscores) you'd do...

Me.tbRSPUnderscores = GetRSPUnderscores(Me.tbHiddenAnswer)

...which would print a string like...

"____ ____ ____ ____ ____ ____ ____ ____ ____ ____"

 

[MajP]

I would assume you are calling this from a query so I would make Marks function Public and make the width a parameter.

Public Function GetRSPUnderscore(Text As String, Optional Per_Char = 4) As String
    Dim i As Integer
    Dim tmp
    
    For i = 1 To Len(Text)
        tmp = tmp & " " & String(Per_Char, "_")
    Next
    
    GetRSPUnderscore = Trim(tmp)
End Function

Then in SQL assuming you have a Question and answer in your query do something like

SELECT
    [Q] & ":  " & GetRSPUnderscore ([a]) AS QandA
FROM
    Table1;

 

[MajP]

I added to the function in case you have multiple word answers.

Public Function GetRSPUnderscore(Text As String, Optional Per_Char = 4) As String
    Dim i As Integer
    Dim tmp
    
    For i = 1 To Len(Text)
        If Mid(Text, i, 1) = " " Then
          tmp = tmp & "    "
        Else
          tmp = tmp & " " & String(Per_Char, "_")
        End If
    Next
    
    GetRSPUnderscore = Trim(tmp)
End Function

My answer for biggest animal is Blue Whale. Now you get

____ ____ ____ ____     ____ ____ ____ ____ ___

 

[Isaac]

Skip VBA use SQL

[YourColumn] & String(Len([YourColumn]), "_")

 

[MajP]

Skip VBA use SQL

As pointed out that is pretty unusable. Mark's modified on the left used in a query and yours on the right.

SELECT
    [Q] & ":  " & GetRSPUnderscore ([a]) AS QandA,
    [Q] & ":  " & String (Len([a]), "_") AS Demo
FROM
    Table1;

 

[cheekybuddha]

As pointed out that is pretty unusable.

Surely just a small adjustment required:

SELECT
  [Q] & ":  " & GetRSPUnderscore ([a]) AS QandA,
  [Q] & ":  " & Trim(String(Len([a]), "_ ")) AS Demo
FROM Table1;

(nb untested )

 

[Isaac]

As pointed out that is pretty unusable. Mark's modified on the left used in a query and yours on the right.
View attachment 123111

SELECT
    [Q] & ":  " & GetRSPUnderscore ([a]) AS QandA,
    [Q] & ":  " & String (Len([a]), "_") AS Demo
FROM
    Table1;

I ran it and it worked quite fine.

 

[MajP]

Surely just a small adjustment required:

Surely not. It only prints the first character of any string

character

Required; Variant. Character code specifying the character or string expression whose first character is used to build the return string. If character contains Null, Null is returned.

?string(10,"ABC")
AAAAAAAAAA
?string(10,"_           ")
__________

 

[cheekybuddha]

Surely not

 

[cheekybuddha]

That'll teach me not to check the docs! Have been writing you'll much js/ts and confusing with string.repeat()