TextBox mismatch ..... calculation
 

Hi All,

I have the following code which executes well until the last line of code
TextBox9.Text. When it gets there it gives me a "mismatch error" althogh the
values stored for the calculation in TextBox10 and TextBox11 are correct.

Private Sub CommandButton3_Click()
' Calculate Total Days on Hire
' Make sure user has entered both dates
If Me.TextBox2.Text < "" And Me.TextBox5 < "" Then
Me.TextBox3.Text = Format(CDate(Me.TextBox5.Text) -
CDate(Me.TextBox2.Text), "#,##0.0000")
End If
' Before calculations commence check that all related TextBoxes are
complete
' Check for Total Days calculation and Daily Hire and Commissions Rate
If TextBox2.Text = "" Or TextBox5.Text = "" Or TextBox12.Text = "" Or
TextBox11.Text = "" Then
MsgBox "In order to proceed you need to complete all boxes relating
to" & vbCrLf & _
" Date From, Date To, Daily Hire Rate and Commissions Rate."
Else
' Calculate Total Hire
TextBox10.Text = Format(Val(CDbl(Me.TextBox12.Text)) *
Val(CDbl(Me.TextBox3.Text)), "#,##0.00")
' Calculate Total Commissions
TextBox9.Text = Format(Val(CDbl(Me.TextBox10.Text)) *
Val(CDbl(Me.TextBox11.Text)), "#,##0.00")
End If
End Sub

TextBox10 formated as - .Value = Format(.Text, "#,##0.00")
TextBox11 formated as - .Value = FormatPercent((CDbl(TextBox11) / 100), 2)

I have been looking at this for too long and my mind has completely
blocked.......
Many thanks for any assistance/sgl

TextBox mismatch ..... calculation
 

According to the VBA help on VAL you don't need the Val and the CDBL

Note The Val function recognizes only the period (.) as a valid decimal
separator. When different decimal separators are used, as in international
applications, use CDbl instead to convert a string to a number.


You code doesn't make sensed it seem the CDBL and the VAL are backwards.
Your code has CDBL(String). CDBL is suppose to be for a number. You should
have
CDBL(Val(string)). Note that text boxes contains strings.

"sgl" wrote:

Hi All,

I have the following code which executes well until the last line of code
TextBox9.Text. When it gets there it gives me a "mismatch error" althogh the
values stored for the calculation in TextBox10 and TextBox11 are correct.

Private Sub CommandButton3_Click()
' Calculate Total Days on Hire
' Make sure user has entered both dates
If Me.TextBox2.Text < "" And Me.TextBox5 < "" Then
Me.TextBox3.Text = Format(CDate(Me.TextBox5.Text) -
CDate(Me.TextBox2.Text), "#,##0.0000")
End If
' Before calculations commence check that all related TextBoxes are
complete
' Check for Total Days calculation and Daily Hire and Commissions Rate
If TextBox2.Text = "" Or TextBox5.Text = "" Or TextBox12.Text = "" Or
TextBox11.Text = "" Then
MsgBox "In order to proceed you need to complete all boxes relating
to" & vbCrLf & _
" Date From, Date To, Daily Hire Rate and Commissions Rate."
Else
' Calculate Total Hire
TextBox10.Text = Format(Val(CDbl(Me.TextBox12.Text)) *
Val(CDbl(Me.TextBox3.Text)), "#,##0.00")
' Calculate Total Commissions
TextBox9.Text = Format(Val(CDbl(Me.TextBox10.Text)) *
Val(CDbl(Me.TextBox11.Text)), "#,##0.00")
End If
End Sub

TextBox10 formated as - .Value = Format(.Text, "#,##0.00")
TextBox11 formated as - .Value = FormatPercent((CDbl(TextBox11) / 100), 2)

I have been looking at this for too long and my mind has completely
blocked.......
Many thanks for any assistance/sgl

TextBox mismatch ..... calculation
 

Your comments noted but sorry I am still totally confused. When testing this
macro the previous line somehow calculates correctly as I have written it and
the TextBox10 is updated. This is:

' Calculate Total Hire
TextBox10.Text = Format(Val(CDbl(Me.TextBox12.Text)) *
Val(CDbl(Me.TextBox3.Text)), "#,##0.00")

It seems to me I am doing something wrong with the "Percent" multiplier as
TextBox11 is a commission percent Data Entry (3.75%). The formatting of which
is

TextBox11 formated as: TextBox11 .Value = FormatPercent((CDbl (TextBox11) /
100), 2)

Thanks once again/sgl

"Joel" wrote:

According to the VBA help on VAL you don't need the Val and the CDBL

Note The Val function recognizes only the period (.) as a valid decimal
separator. When different decimal separators are used, as in international
applications, use CDbl instead to convert a string to a number.


You code doesn't make sensed it seem the CDBL and the VAL are backwards.
Your code has CDBL(String). CDBL is suppose to be for a number. You should
have
CDBL(Val(string)). Note that text boxes contains strings.

"sgl" wrote:

Hi All,

I have the following code which executes well until the last line of code
TextBox9.Text. When it gets there it gives me a "mismatch error" althogh the
values stored for the calculation in TextBox10 and TextBox11 are correct.

Private Sub CommandButton3_Click()
' Calculate Total Days on Hire
' Make sure user has entered both dates
If Me.TextBox2.Text < "" And Me.TextBox5 < "" Then
Me.TextBox3.Text = Format(CDate(Me.TextBox5.Text) -
CDate(Me.TextBox2.Text), "#,##0.0000")
End If
' Before calculations commence check that all related TextBoxes are
complete
' Check for Total Days calculation and Daily Hire and Commissions Rate
If TextBox2.Text = "" Or TextBox5.Text = "" Or TextBox12.Text = "" Or
TextBox11.Text = "" Then
MsgBox "In order to proceed you need to complete all boxes relating
to" & vbCrLf & _
" Date From, Date To, Daily Hire Rate and Commissions Rate."
Else
' Calculate Total Hire
TextBox10.Text = Format(Val(CDbl(Me.TextBox12.Text)) *
Val(CDbl(Me.TextBox3.Text)), "#,##0.00")
' Calculate Total Commissions
TextBox9.Text = Format(Val(CDbl(Me.TextBox10.Text)) *
Val(CDbl(Me.TextBox11.Text)), "#,##0.00")
End If
End Sub

TextBox10 formated as - .Value = Format(.Text, "#,##0.00")
TextBox11 formated as - .Value = FormatPercent((CDbl(TextBox11) / 100), 2)

I have been looking at this for too long and my mind has completely
blocked.......
Many thanks for any assistance/sgl

TextBox mismatch ..... calculation
 

I think it's time to figure out what's wrong with the values in those two
textboxes.

Dim myVal10 as double
dim myVal11 as double

....
myval10 = cdbl(me.textbox10.text)
myval11 = cdbl(me.textbox11.text)

I bet you'll find that since you have included the % sign in the textbox, you'll
have to remove it, check if numeric, and then do the arithmetic.



sgl wrote:

Hi All,

I have the following code which executes well until the last line of code
TextBox9.Text. When it gets there it gives me a "mismatch error" althogh the
values stored for the calculation in TextBox10 and TextBox11 are correct.

Private Sub CommandButton3_Click()
' Calculate Total Days on Hire
' Make sure user has entered both dates
If Me.TextBox2.Text < "" And Me.TextBox5 < "" Then
Me.TextBox3.Text = Format(CDate(Me.TextBox5.Text) -
CDate(Me.TextBox2.Text), "#,##0.0000")
End If
' Before calculations commence check that all related TextBoxes are
complete
' Check for Total Days calculation and Daily Hire and Commissions Rate
If TextBox2.Text = "" Or TextBox5.Text = "" Or TextBox12.Text = "" Or
TextBox11.Text = "" Then
MsgBox "In order to proceed you need to complete all boxes relating
to" & vbCrLf & _
" Date From, Date To, Daily Hire Rate and Commissions Rate."
Else
' Calculate Total Hire
TextBox10.Text = Format(Val(CDbl(Me.TextBox12.Text)) *
Val(CDbl(Me.TextBox3.Text)), "#,##0.00")
' Calculate Total Commissions
TextBox9.Text = Format(Val(CDbl(Me.TextBox10.Text)) *
Val(CDbl(Me.TextBox11.Text)), "#,##0.00")
End If
End Sub

TextBox10 formated as - .Value = Format(.Text, "#,##0.00")
TextBox11 formated as - .Value = FormatPercent((CDbl(TextBox11) / 100), 2)

I have been looking at this for too long and my mind has completely
blocked.......
Many thanks for any assistance/sgl

--

Dave Peterson

TextBox mismatch ..... calculation
 

Thanks Dave. It was the % sign that was doing the damage. I removed it.
However, now the user would have to enter the commission amount as 0.0375
which I do not want him to do. I would prefer that he enters the amount as he
is used to applying it as 3.75%. Any ideas please!


"Dave Peterson" wrote:

I think it's time to figure out what's wrong with the values in those two
textboxes.

Dim myVal10 as double
dim myVal11 as double

....
myval10 = cdbl(me.textbox10.text)
myval11 = cdbl(me.textbox11.text)

I bet you'll find that since you have included the % sign in the textbox, you'll
have to remove it, check if numeric, and then do the arithmetic.



sgl wrote:

Hi All,

I have the following code which executes well until the last line of code
TextBox9.Text. When it gets there it gives me a "mismatch error" althogh the
values stored for the calculation in TextBox10 and TextBox11 are correct.

Private Sub CommandButton3_Click()
' Calculate Total Days on Hire
' Make sure user has entered both dates
If Me.TextBox2.Text < "" And Me.TextBox5 < "" Then
Me.TextBox3.Text = Format(CDate(Me.TextBox5.Text) -
CDate(Me.TextBox2.Text), "#,##0.0000")
End If
' Before calculations commence check that all related TextBoxes are
complete
' Check for Total Days calculation and Daily Hire and Commissions Rate
If TextBox2.Text = "" Or TextBox5.Text = "" Or TextBox12.Text = "" Or
TextBox11.Text = "" Then
MsgBox "In order to proceed you need to complete all boxes relating
to" & vbCrLf & _
" Date From, Date To, Daily Hire Rate and Commissions Rate."
Else
' Calculate Total Hire
TextBox10.Text = Format(Val(CDbl(Me.TextBox12.Text)) *
Val(CDbl(Me.TextBox3.Text)), "#,##0.00")
' Calculate Total Commissions
TextBox9.Text = Format(Val(CDbl(Me.TextBox10.Text)) *
Val(CDbl(Me.TextBox11.Text)), "#,##0.00")
End If
End Sub

TextBox10 formated as - .Value = Format(.Text, "#,##0.00")
TextBox11 formated as - .Value = FormatPercent((CDbl(TextBox11) / 100), 2)

I have been looking at this for too long and my mind has completely
blocked.......
Many thanks for any assistance/sgl

--

Dave Peterson

TextBox mismatch ..... calculation
 

Look for the % sign.

If you find it, remove it, then check to see if it's still numeric. If it is,
then divide by 100.



sgl wrote:

Thanks Dave. It was the % sign that was doing the damage. I removed it.
However, now the user would have to enter the commission amount as 0.0375
which I do not want him to do. I would prefer that he enters the amount as he
is used to applying it as 3.75%. Any ideas please!

"Dave Peterson" wrote:

I think it's time to figure out what's wrong with the values in those two
textboxes.

Dim myVal10 as double
dim myVal11 as double

....
myval10 = cdbl(me.textbox10.text)
myval11 = cdbl(me.textbox11.text)

I bet you'll find that since you have included the % sign in the textbox, you'll
have to remove it, check if numeric, and then do the arithmetic.



sgl wrote:

Hi All,

I have the following code which executes well until the last line of code
TextBox9.Text. When it gets there it gives me a "mismatch error" althogh the
values stored for the calculation in TextBox10 and TextBox11 are correct.

Private Sub CommandButton3_Click()
' Calculate Total Days on Hire
' Make sure user has entered both dates
If Me.TextBox2.Text < "" And Me.TextBox5 < "" Then
Me.TextBox3.Text = Format(CDate(Me.TextBox5.Text) -
CDate(Me.TextBox2.Text), "#,##0.0000")
End If
' Before calculations commence check that all related TextBoxes are
complete
' Check for Total Days calculation and Daily Hire and Commissions Rate
If TextBox2.Text = "" Or TextBox5.Text = "" Or TextBox12.Text = "" Or
TextBox11.Text = "" Then
MsgBox "In order to proceed you need to complete all boxes relating
to" & vbCrLf & _
" Date From, Date To, Daily Hire Rate and Commissions Rate."
Else
' Calculate Total Hire
TextBox10.Text = Format(Val(CDbl(Me.TextBox12.Text)) *
Val(CDbl(Me.TextBox3.Text)), "#,##0.00")
' Calculate Total Commissions
TextBox9.Text = Format(Val(CDbl(Me.TextBox10.Text)) *
Val(CDbl(Me.TextBox11.Text)), "#,##0.00")
End If
End Sub

TextBox10 formated as - .Value = Format(.Text, "#,##0.00")
TextBox11 formated as - .Value = FormatPercent((CDbl(TextBox11) / 100), 2)

I have been looking at this for too long and my mind has completely
blocked.......
Many thanks for any assistance/sgl

--

Dave Peterson


--

Dave Peterson

TextBox mismatch ..... calculation
 

My apologies for the delay in replying but I had to take a break away from
this project. I am sure are right with your suggestions. I have found some of
your other responses to similar problems in this newsgroup. Thanks again

"Dave Peterson" wrote:

Look for the % sign.

If you find it, remove it, then check to see if it's still numeric. If it is,
then divide by 100.



sgl wrote:

Thanks Dave. It was the % sign that was doing the damage. I removed it.
However, now the user would have to enter the commission amount as 0.0375
which I do not want him to do. I would prefer that he enters the amount as he
is used to applying it as 3.75%. Any ideas please!

"Dave Peterson" wrote:

I think it's time to figure out what's wrong with the values in those two
textboxes.

Dim myVal10 as double
dim myVal11 as double

....
myval10 = cdbl(me.textbox10.text)
myval11 = cdbl(me.textbox11.text)

I bet you'll find that since you have included the % sign in the textbox, you'll
have to remove it, check if numeric, and then do the arithmetic.



sgl wrote:

Hi All,

I have the following code which executes well until the last line of code
TextBox9.Text. When it gets there it gives me a "mismatch error" althogh the
values stored for the calculation in TextBox10 and TextBox11 are correct.

Private Sub CommandButton3_Click()
' Calculate Total Days on Hire
' Make sure user has entered both dates
If Me.TextBox2.Text < "" And Me.TextBox5 < "" Then
Me.TextBox3.Text = Format(CDate(Me.TextBox5.Text) -
CDate(Me.TextBox2.Text), "#,##0.0000")
End If
' Before calculations commence check that all related TextBoxes are
complete
' Check for Total Days calculation and Daily Hire and Commissions Rate
If TextBox2.Text = "" Or TextBox5.Text = "" Or TextBox12.Text = "" Or
TextBox11.Text = "" Then
MsgBox "In order to proceed you need to complete all boxes relating
to" & vbCrLf & _
" Date From, Date To, Daily Hire Rate and Commissions Rate."
Else
' Calculate Total Hire
TextBox10.Text = Format(Val(CDbl(Me.TextBox12.Text)) *
Val(CDbl(Me.TextBox3.Text)), "#,##0.00")
' Calculate Total Commissions
TextBox9.Text = Format(Val(CDbl(Me.TextBox10.Text)) *
Val(CDbl(Me.TextBox11.Text)), "#,##0.00")
End If
End Sub

TextBox10 formated as - .Value = Format(.Text, "#,##0.00")
TextBox11 formated as - .Value = FormatPercent((CDbl(TextBox11) / 100), 2)

I have been looking at this for too long and my mind has completely
blocked.......
Many thanks for any assistance/sgl

--

Dave Peterson


--

Dave Peterson