Need some help with fields (1 Viewer)

[dedwards1960]

I have a report which I need to and two fields in an unbound field.

I have written an event to convert a field called rank to full name.

example:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.txtRank = "PO1" Then
Me.txtShowRank = "PETTY OFFICER 1ST CLASS"
Else
If Me.txtRank = "PO2" Then
Me.txtShowRank = "PETTY OFFICER 2ND CLASS"
Else

Me.txtShowRank = ""
End If

End If

End Sub

How I would like to include the firstname and lastname fields to this.

so it will show PETTY OFFICER 2ND CLASS FIRSTNAME LASTNAME.

Can anyone help with this.

Thanks to all that give me ideas.

 

[pbaldy]

Concatenation:

Me.txtShowRank = "PETTY OFFICER 1ST CLASS " & FirstNameField & " " & LastNameField

 

[dedwards1960]

That works

Thank you for the quick reply.

I had tried that but did not work for me. My code was wrong as what you show works the way I want it.

Thanks Again

 

[dedwards1960]

Need more Info

Thanks For everything

I have one more situation and would like to know if this can be done also.

For each Trade Group there is a level of Trade (1 , 2, 3)
I have a module for converting the numberic value to Roman II.
When I put this on the report I do this =dhRoman([LEVEL])
which then changes the number to roman.

I would like to have this in my on Format Event.

Can this be done.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If Me.txtPOSITION = 1 Then
Me.txtShowPosition = "Boatswain Trade Group"
Else
If Me.txtPOSITION = 2 Then
Me.txtShowPosition = "Boatswain Trade Group"
Else
If Me.txtPOSITION = 3 Then
Me.txtShowPosition = "Gunnery Trade Group"
Else
If Me.txtPOSITION = 4 Then
Me.txtShowPosition = "Sail Trade Group"
Else

Me.txtShowPosition = ""

End If
End If
End If
End If

End Sub

Thanks Again

 

[pbaldy]

Yes, though you might want to use Select/Case instead of If/Then. I would have a table that contained those values, and then you could simply join that table to your report's source query and bring in the names directly. More dynamic than the code based solution.

 

[dedwards1960]

Can you Assist

How do I do this by creating a table.

Do you have a sample I could follow and do I have to put some code in the report.

Thanks Again

 

[dedwards1960]

MY Module to Convert number to Roman

Here is my module for converting my numbers to roman.

Option Compare Database

Public Function dhRoman(ByVal intValue As Integer) As String
Dim varDigits As Variant
Dim intPos As Integer
Dim IntDigit As Integer
Dim strTemp As String

' Build up the array of roman digits
varDigits = Array("I", "V", "X", "L", "C", "D", "M")
intPos = LBound(varDigits)
strTemp = ""

Do While intValue > 0
IntDigit = intValue Mod 10
intValue = intValue \ 10
Select Case IntDigit
Case 1
strTemp = varDigits(intPos) & strTemp
Case 2
strTemp = varDigits(intPos) & varDigits(intPos) _
& strTemp
Case 3
strTemp = varDigits(intPos) & varDigits(intPos) _
& varDigits(intPos) & strTemp
Case 4
strTemp = varDigits(intPos) & varDigits(intPos + 1) _
& strTemp
Case 5
strTemp = varDigits(intPos + 1) & strTemp
Case 6
strTemp = varDigits(intPos + 1) & varDigits(intPos) _
& strTemp
Case 7
strTemp = varDigits(intPos + 1) & varDigits(intPos) _
& varDigits(intPos) & strTemp
Case 8
strTemp = varDigits(intPos + 1) & varDigits(intPos) _
& varDigits(intPos) & varDigits(intPos) & strTemp
Case 9
strTemp = varDigits(intPos) & varDigits(intPos + 2) _
& strTemp
End Select

intPos = intPos + 2
Loop

dhRoman = strTemp

End Function

 

[pbaldy]

No code required, as that would be a standard type lookup table. You'd have a field for Trade Group ID and another for the description:

1 - Boatswain Trade Group
2 - Boatswain Trade Group
3 - Gunnery Trade Group
etc

Then that table would be joined on the ID field in your query, and the name would be returned by the query instead of having code.

 

[Adeptus]

No code required, as that would be a standard type lookup table. You'd have a field for Trade Group ID and another for the description:

1 - Boatswain Trade Group
2 - Boatswain Trade Group
3 - Gunnery Trade Group
etc

Then that table would be joined on the ID field in your query, and the name would be returned by the query instead of having code.

Do the same for the ranks too! (in a separate table)

 

[Adeptus]

Here is my module for converting my numbers to roman.
...

It doesn't look like it would work for numbers above 9...
it looks like it converts each digit to Roman, so 11 would be II, 99 would be IXIX (10, 100, and 10000000 would all be I?)

If you only need numbers up to 9 then that's great, otherwise I'd rethink it.
OK, just found this SQL2000 code and converted the Roman numerals part (conversion both ways). Yes I was bored

 

[dedwards1960]

Thanks All

I was planning on creating some tables for the ranks and the courses after the summer camp is done. So for now I will do with what I have and then be able to work on a much better version of the database.

Thanks again to all for your suggestion and assistance.

This is the best forum I have found for people helping people.