Coercion of double 2

[AlanJordan]

Can anyone explain to me why the code that specifically creates a variable for each of the items properly adds data, but the commented out code does not?

I think it has to do with coercion of the data type, but I would expect that converting the contents of each of text boxes to a double would achieve the same purpose.

My guess is that things are getting thrown off when a text box is empty, and that Access just can convert it unless it is stuffed into a variable.

Thanks, in advance, Alan

Function TotalPayableHours(InArray As Variant) As Double
On Error GoTo errTPH

    Dim dbl1 As Double
    Dim dbl2 As Double
    Dim dbl3 As Double
    Dim dbl4 As Double
    Dim dbl5 As Double
    Dim dbl6 As Double
    Dim dbl7 As Double
    Dim dbl0 As Double
    Dim dblTotal As Double
    
    dbl1 = InArray(1, 4)
    dbl2 = InArray(2, 4)
    dbl3 = InArray(3, 4)
    dbl4 = InArray(4, 4)
    dbl5 = InArray(5, 4)
    dbl6 = InArray(6, 4)
    dbl0 = InArray(0, 4)
    dblTotal = dbl1 + dbl2 + dbl3 + dbl4 + dbl5 + dbl6 + dbl0
    TotalPayableHours = dblTotal
[COLOR=green]   'TotalPayableHours = CDbl(InArray(1, 4)) + CDbl(InArray(2, 4)) + CDbl(InArray(3, 4)) + CDbl(InArray(4, 4)) & _
    CDbl(InArray(5, 4)) + CDbl(InArray(6, 4)) + CDbl(InArray(0, 4))[/Color Green]
    With Me!txtTotPayHrs
        .Value = TotalPayableHours
        .Format = "Fixed"
        .DecimalPlaces = 2
    End With

Exit Function
errTPH:
    ErrBox "The problem originated in TotalPayableHours in frmTimecard."
End Function

 

[RoyVidar]

In the commented version, at the linebreak, you're using an ampersand (&), i e a text concatenation, in stead of +, is that it?

Roy-Vidar

 

[PHV]

And what about this ?
Function TotalPayableHours(InArray As Variant) As Double
Dim dblTotal As Double, i As Integer
For i = 0 To 6
dblTotal = dblTotal + Nz(InArray(i, 4), 0)
Next
With Me!txtTotPayHrs
.Value = dblTotal
.Format = "Fixed"
.DecimalPlaces = 2
End With
TotalPayableHours = dblTotal
End Function

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[AlanJordan]

Thanks for the replies.

Roy, the &_ is the way that I position code on more than one line so that it is readable. I don't think it is part of the problem.

PHV - Hmm. Using a null to zero function might work. I'll try that after lunch and let you know what happens.

Thanks.

Alan

 

[PHV]

the &_ is the way that I position code on more than one line
False !
The continuation character is the underscore, the ampersand is the concatenation operator.
A correct syntax:
TotalPayableHours = CDbl(InArray(1, 4)) + CDbl(InArray(2, 4)) + CDbl(InArray(3, 4)) + CDbl(InArray(4, 4)) + _
CDbl(InArray(5, 4)) + CDbl(InArray(6, 4)) + CDbl(InArray(0, 4))

Another:
TotalPayableHours = CDbl(InArray(1, 4)) + CDbl(InArray(2, 4)) + CDbl(InArray(3, 4)) + CDbl(InArray(4, 4)) _
+ CDbl(InArray(5, 4)) + CDbl(InArray(6, 4)) + CDbl(InArray(0, 4))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RoyVidar]

To follow up on PHV's suggestions, try the following in the immediate pane (ctrl+g):

[tt]? 1.23 & 1.23

and

? 1.23 + 1.23[/tt]

Anyway, shouldn't assigning either a text of a Null to a variable of datatype double give a runtime error? If so, which is it 13 or 94?

Roy-Vidar

 

[AlanJordan]

Okay. I've done some testing, and I've got the answers.

PHV, you are absolutely right. This marks the first
time in my programming experience that I've continued a line where I am doing a calculation. I've always continued lines where I am entering an error message or some other form of text. I got to think that the &_ combination was the way of continuing a line. Changing the &_ to +_ fixed the problem.

Roy,
Apparently, the process of converting to a double eliminates the Null. I'm guessing that it actually incorporates the Nz function in the background. No runtime errors resulted from the code.

Roy, yes. I understand that 1.23 & 1.23 is concatenated and results in 1.231.23 vs. 1.23 + 1.23 = 2.46. I just didn't realize that I was accidentally concatenating.

Many thanks. Please, both of you, accept a star.

Alan