how to set the column for label and column for data

[jennypretty]

Hello,

I am trying to create a report to set all Labels on one column (left) and data to 2nd column, but it does not work.
My code below will combine a few table columns on one column and delete any empty rows.
Can any one help please?
Thanks.

Dim nBaseXAxis As String 'var to keep left most position of the cursor at starting
Dim nBaseFontSize As Integer
Dim nBaseFontColor As Variant

Option Compare Database
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
'//-- Report format
nBaseXAxis = 0
nBaseFontSize = 12

nBaseFontColor = vbBlack

printLabel "Last Name: " & Trim(Me.LastName.Value), nBaseXAxis, 12, vbBlack
'Me.Print Me.LastName.Value & vbCrLf & vbCrLf
Me.Print ""

'//-- PRINT Q5 -------------------------------------------------
strRowVal = ""

If Len(Trim(Nz(Me.Q51.Value, ""))) > 0 Then
strRowVal = strRowVal & Q51 & "*"
End If

If Len(Trim(Nz(Me.Q52.Value, ""))) > 0 Then
strRowVal = strRowVal & Q52 & "*"
End If

If Len(Trim(Nz(Me.Q53.Value, ""))) > 0 Then
strRowVal = strRowVal & Q53 & "*"
End If

If Len(Trim(Nz(Me.Q54.Value, ""))) > 0 Then
strRowVal = strRowVal & Q54 & "*"
End If
If Len(Trim(Nz(Me.Q55.Value, ""))) > 0 Then
strRowVal = strRowVal & Q55 & "*"
End If

If (Len(Trim(strRowVal)) > 0) Then

strRowValArr = Split(strRowVal, "*")
printLabel "Q5: " & Trim(strRowValArr(0)), nBaseXAxis, 12, vbBlack
x = 420
For i = 1 To UBound(strRowValArr)
printLabel strRowValArr(i), x, 12, vbBlack
Next

End If

End Sub

Public Function printLabel(strText, nHAxis, nTextFontSize, nTextFontColor)

Me.CurrentX = nHAxis
Me.FontBold = True
Me.FontSize = nTextFontSize
Me.ForeColor = nTextFontColor

Me.Print strText

'Me.CurrentX = nHAxis
Me.FontBold = False
Me.FontSize = nBaseFontSize
Me.ForeColor = nBaseFontColor

End Function
Private Sub Report_Open(Cancel As Integer)
End Sub

 

[lark_tilly]

Hi jennypretty!

I'm a little confused about what you have here.
Where are the values you want to print coming from?
Are they in a table or on a form or ?
If they're in a table you should be able to query them and make a
report from the query but I don't know if that's what you have.
Can you provide more detail or a copy of the db or a stripped down
version of it? I'm using Access 2000 so please save it to that or 97
so I can open it up.

 

[jennypretty]

The data come from the table.
I can't use wizard to create report so I have to write a code Because wizard can't combine a few columns into one column (vertically).

Thanks.

 

[lark_tilly]

Can you post an example of your table with some dummy data?
I think what you want is fairly straightforward once I'm sure what
the data actually is like.

 

[jennypretty]

Here is my file.
In general, there is q5a, q5b, q5c, q5d, q5e, I want to display them in one column on report, instead of 5 different columns, and skip any empty row instead of showing empty space on that ONE column.

thanks.

 

[lark_tilly]

I'm thinking I still don't know exactly what you're looking for but here's a db that has my first shot at it. It has a table with data, a query against that table and a report using that query. Take a look and let me know where we need to go from here. I'm sure I can give you just what you want if you hang in there until I get it right. The db is in Access 2000 so if you have a problem let me know.

 

[jennypretty]

I have a db. But the report format is not the format I want.
Can you modify this format to be column-like format? like the attached file.

Q5 (label) will line in one column.
Data will line in the 2nd column.

Any empty row will be removed on repot.

Can it be this way?

Thanks.

 

[lark_tilly]

Take a look at the attchment and let me know if that is correct.
I can do this but it will take some code behind the scenes to make it happen.
I don't know any other way to leave out blank columns.
What is the table's name and are the fields Q5A Q5B...or q5a q5b or ?
Also, what version of Access are you using?

 

[jennypretty]

Hello,
I need ONLY online Q5 for lable, and the rest of don't have to show lable for one row. It will repeat the same for 2nd row.
Like the one attached.

I have this to eliminate the empty rows:
strRowVal = ""

If Len(Trim(Nz(Me.Q51.Value, ""))) > 0 Then
strRowVal = strRowVal & Q51 & "*"
End If


thanks.