changing a number into text

[ghacig]

Hi all,

This is for a medical database. I have a table called Medications that includes five fields indicating the amount of pills to be taken (Mor, Noon, AfN, Eve, HS). All five fields are Double. I need to print out a prescription for selected medications, but I would like to change the number stored in the above fields into text.

Ex: Mor = 1.25 I would like the text to say: one and a quarter pills....

The possible values of the fields start at 0.25 and increase by 0.25 up to 6.75

Is there an easy way to do this without have to write a mile long code for each case.

I was wondering if there is a way to write a code that selects the left and the right sides of the decimal separately.

Thanks for your help

 

[SgtJarrow]

I started to build a function to get you started but was turning out longer than a simple select statement would be.

As long as you don't expect the max to change from 6.75, you could get away with something like:

dim strText As String
Select Case dblNumber
    Case 0.25: strText = "a quarter of a pill"
    Case 0.50: strText = "half a pill"
    Case 0.75: strText = "three quarters of a pill"
    Case 1.00: strText = "one pill"
    Case 1.25: strText = "one and a quarter pills"
    ...
End Select

Of course, this is a bit long winded, but my function was getting longer by the moment.

You also might want to search for some of the examples of numbers to text that appear here and on the net. Most of them apply to check writing, but you could probably modify one fairly easily to meet your needs.

Hope that helps some.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[ghacig]

Thanks. This is what I wanted to avoid, because the code would become extremely long. I was wondering if there is a way to specify the left and right of the decimal seperately. Something like this:

for the left of the decimal:
case 0: strText1 = ""
case 1: strText1 = "one"
case 2: strText1 = "two"
...

and for th right of the decimal:

case 00: strText2 = ""
case 25: strText2 = "quarter"
case 50: strText2 = "half"
case 75: strText2 = "three quarters"


then combine the two: strText = strText 1 & strText & " pill(s)"

I would appreciate your help. Thanks.

 

[SgtJarrow]

Sure, you can do this. Left(value, InStr(1, value, ".") -1) should find everything left of the decimal. Mid(value, InStr(1, value, ".")) everything on the right. You may need to adjust the -1 or so to find the exact value.

This would be rough and dirty. Good grammar it would not solve, i.e. one pill, two pills, quarter pill, three and half pills. You included the (s) in your last post, so if that is sufficient, then that should get you close enough to figure out a solution.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[CautionMP]

Not sure it's any shorter but...

Public Function NumberToText(InputNumber As Double) As String
Dim lngWhole As Long
Dim lngDecimal As Long
Dim strWhole As String, strPartial As String
If InputNumber = 0 Then
  NumberToText = "no pills"
  Exit Function
End If
lngWhole = Int(InputNumber)
lngDecimal = (InputNumber - lngWhole) * 100
Select Case lngWhole
  Case 1
    strWhole = "one"
  Case 2
    strWhole = "two"
  Case 3
    strWhole = "three"
  Case 4
    strWhole = "four"
  Case 5
    strWhole = "five"
  Case 6
    strWhole = "six"
  Case Else
    strWhole = "too many"
End Select
Select Case lngDecimal
  Case 25
    strPartial = "a quarter"
  Case 50
    strPartial = "a half"
  Case 75
    strPartial = "three quarter"
End Select
If InputNumber < 1 Then
  NumberToText = strPartial & " of a pill"
ElseIf InputNumber = 1 Then
  NumberToText = strWhole & " pill"
Else
  If lngDecimal = 0 Then
    NumberToText = strWhole & " pills"
  Else
    NumberToText = strWhole & " and " & strPartial & " pills"
  End If
End If
End Function

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)