Feet-Inches-Fraction Issue 1

[vepz]

I want to format a cell to display 6'-6 1/2". The trouble occurs when the fractionportion is zero. I get 6'-6 0". To solve this, I created an IIf statement.

=IIf([fraction]=0,[feet] & "' - " & [inch] & """",[feet] & "' - " & [inch] & """")

When the fraction is 0, I get the correct output with the fraction eliminated. However, when there is a fractional portion, I get an error message. However, if I put the [feet] & "' - " & [inch] & """" in its own cell, I get the correct output.

Any suggestions?

 

[dhookom]

I don't see where the [fraction] field will ever display. What is the error message? Is [fraction] truly a numeric field or is it text?

Duane
Hook'D on Access
MS Access MVP

 

[vepz]

Sorry.
=[feet] & "' - " & [inch] & " " & [fraction] & """"

I am not sure. I know that the above expression returns the desired result when it is not included in the iif statement.

How can I tell? Error is just #error.

Thanks.

 

[SkipVought]

What is the data type of the field [fraction]?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MajP]

You might want to account for all 8 cases, and then I would roll a custom function.

Public Function formatFtInchFrac(varFt As Variant, varInch As Variant, varFrac As Variant) As String
  varFt = Nz(varFt, 0)
  varInch = Nz(varInch, 0)
  varFrac = Nz(varFrac, 0)
  If varFt = 0 And varInch = 0 And varFrac = 0 Then
    formatFtInchFrac = 0
  ElseIf (varFt = 0 And varInch = 0) And Not varFrac = 0 Then
    formatFtInchFrac = varFrac & """"
   ElseIf (varFt = 0 And varFrac = 0) And Not varInch = 0 Then
    formatFtInchFrac = varInch & """"
  ElseIf Not varFt = 0 And (varInch = 0 And varFrac = 0) Then
    formatFtInchFrac = varFt & "'"
  ElseIf Not varFt = 0 And Not varInch = 0 And varFrac = 0 Then
    formatFtInchFrac = varFt & "'-" & varInch & """"
  ElseIf Not varFt = 0 And varInch = 0 And Not varFrac = 0 Then
    formatFtInchFrac = varFt & "'-" & varFrac & """"
  ElseIf varFt = 0 And Not varInch = 0 And Not varFrac = 0 Then
    formatFtInchFrac = varInch & " " & varFrac & """"
  ElseIf Not varFt = 0 And Not varInch = 0 And Not varFrac = 0 Then
    formatFtInchFrac = varFt & "'-" & varInch & " " & varFrac & """"
  End If
End Function

Test cases:
?formatFtInchFrac(0,0,0)
0

?formatFtInchFrac(0,0,"1/2")
1/2"

?formatFtInchFrac(0,6,0)
6"

?formatFtInchFrac(6,0,0)
6'

?formatFtInchFrac(6,6,0)
6'-6"

?formatFtInchFrac(6,0,"1/2")
6'-1/2"

?formatFtInchFrac(0,6,"1/2")
6 1/2"

?formatFtInchFrac(6,6,"1/2")
6'-6 1/2"

 

[vepz]

I like the idea of taking care of each case. How do I incorporate this code, thank you by the way, into my database? I an basically a novice and could use alittle more help. You really have a clear understanding and from the look of the code, I can understand how it will handle the formatting.

I appreciate your help,

 

[vepz]

Can I get some help applying this code to my form?

Thanks,

 

[dhookom]

First create a new blank module and paste the code into it. Save the module with a name like "modCalcs". Then you can use the function almost anywhere you would use any other function. For instance, in a control source, you could use:
=formatFtInchFrac([Feet],[Inch],[Fraction])

You could also use this in a query like:
FtInchFract: formatFtInchFrac([Feet],[Inch],[Fraction])




Duane
Hook'D on Access
MS Access MVP