VBA - CCur

[Guest]

Hi


How do I convert 12345
into
£123.45

[txtTendered] is a currency field which is fed by vba like this
Private Sub box1_Click()
Me.txtTendered = Me.txtTendered & 1
End Sub

Private Sub box2_Click()
Me.txtTendered = Me.txtTendered & 2
End Sub

Etc.

This is converted to currency by CCur like this

Private Sub SomeButtonName_Click()
If Me.SaturdayCost - Me.txtTendered <= 0 Then
Me.txtdiscount = CCur(Me.txtTendered)
Me.Change = Me.txtTendered - Me.SaturdayCost
Else
DoCmd.OpenForm "frmNotEnoughPaid", acNormal, "", "", , acNormal
End If
End Sub

As you can see it's a simple (unbound) calculator with the answer in Me.Change


The problem is that
Me.txtTendered = Me.txtTendered & 2
Me.txtTendered = Me.txtTendered & 3
Me.txtTendered = Me.txtTendered & 4

Will work to enter the number 12345
BUT
Me.txtdiscount = CCur(Me.txtTendered)

Will make

12345 into £12345.00 and not £123.45 (which what I'm after)
At the moment I have Me.txtTendered = Me.txtTendered & . (with the .)

But I would rather not have this last control as it seems a bit "daft"

Any suggestions?

Many thanks


PS.
Oh and I can't use a msg-box for the else (it's a touch screen and the ok
button on a standard msg is not big enough) - this is why I use frmNotEnough
(which is simply a warning and a close option).

 

[Baz]

Divide it by 100?

 

[Guest]

Hi Baz

You are a genius

Ha Ha Ha why didn't I think of that - oh well monday morning

Many thanks

--
Wayne
Manchester, England.

Divide it by 100?

Hi


How do I convert 12345
into
£123.45

[txtTendered] is a currency field which is fed by vba like this
Private Sub box1_Click()
Me.txtTendered = Me.txtTendered & 1
End Sub

Private Sub box2_Click()
Me.txtTendered = Me.txtTendered & 2
End Sub

Etc.

This is converted to currency by CCur like this

Private Sub SomeButtonName_Click()
If Me.SaturdayCost - Me.txtTendered <= 0 Then
Me.txtdiscount = CCur(Me.txtTendered)
Me.Change = Me.txtTendered - Me.SaturdayCost
Else
DoCmd.OpenForm "frmNotEnoughPaid", acNormal, "", "", , acNormal
End If
End Sub

As you can see it's a simple (unbound) calculator with the answer in Me.Change


The problem is that
Me.txtTendered = Me.txtTendered & 2
Me.txtTendered = Me.txtTendered & 3
Me.txtTendered = Me.txtTendered & 4

Will work to enter the number 12345
BUT
Me.txtdiscount = CCur(Me.txtTendered)

Will make

12345 into £12345.00 and not £123.45 (which what I'm after)
At the moment I have Me.txtTendered = Me.txtTendered & . (with the .)

But I would rather not have this last control as it seems a bit "daft"

Any suggestions?

Many thanks


PS.
Oh and I can't use a msg-box for the else (it's a touch screen and the ok
button on a standard msg is not big enough) - this is why I use frmNotEnough
(which is simply a warning and a close option).

 

[Jamie Collins]

Divide it by 100?

Note that dividing by an INTEGER coerces the result to a DOUBLE FLOAT
(and the rule of thumb is to avoid floating point types with financial
data) e.g.

SELECT TYPENAME(123456 / 100)

returns 'Double'.

OK, so you can explicitly cast the result but perhaps the better
approach is to multiple by a CURRENCY amount e.g.

SELECT TYPENAME(123456 * CCUR(0.01))

returns 'Currency'.

Jamie.

--