formating a text value

R

Rodolfo Fontes

Hi group,

I have a querie that list the products name from another table.
In the querie, products like "PC COMPUTER", that have 11 caracters, should
appear like "PC COMPUT", with 9 caracters.
And products like "PC", should be like "0000000PC".

How can I do that?
i've tried:
NAME: format([Product]; "000000000")
but it looks like only works on numeric values.

Thanks for any help,
Rodolfo Fontes
 
K

Ken Warthen

Rodolfo,

This may be something of a kludgey solution, but I think it will work. Open
up a module in Access and paste the following function.

Public Function fncNineChFormat(strDescription As Variant) As Variant
Dim intDescriptionLength As Integer
Dim intShortDescription As Integer

If IsNull(strDescription) Then
fncNineChFormat = "000000000"
Else
intDescriptionLength = Len(strDescription)
End If

If intDescriptionLength >= 9 Then
fncNineChFormat = Left(strDescription, 9)
Else:
intShortDescription = 9 - intDescriptionLength
Select Case intShortDescription
Case 0
fncNineChFormat = "000000000" & strDescription
Case 1
fncNineChFormat = "0" & strDescription
Case 2
fncNineChFormat = "00" & strDescription
Case 3
fncNineChFormat = "000" & strDescription
Case 4
fncNineChFormat = "0000" & strDescription
Case 5
fncNineChFormat = "00000" & strDescription
Case 6
fncNineChFormat = "000000" & strDescription
Case 7
fncNineChFormat = "0000000" & strDescription
Case 8
fncNineChFormat = "00000000" & strDescription
Case Else
fncNineChFormat = "000000000"
End Select
End If


End Function

Then open your query in design view and enter the following into a field
value:
Formatted Product: fncNineChFormat([Product])

That should give you the result you're looking for.

Good luck,

Ken
 
Top