PDA

View Full Version : Solved: Text box Date Calculation Error - Type Mismatch



jammer6_9
04-29-2007, 11:11 PM
How can I calculate a Date type Textbox values?
txtDateEnd(4/30/2007) - txtDateStart(4/1/2007) = txtDaysRange(30 Days)



Private Sub txtDateEnd_Change()
txtDaysRange.Value = (txtDateEnd.Value) - (txtDateStart.Value)
End Sub

shasur
04-30-2007, 12:03 AM
Hi

Use DateDiff to find the days

Private Sub txtDateEnd_Change()
txtDaysRange.Value = DateDiff("d", txtDateStart.Value, txtDateEnd.Value)
End Sub

You need to add more code if you are interested to omit weekends and calculate only weekdays

jammer6_9
04-30-2007, 12:49 AM
That was a quick response shasur thanks BUT i am still having the same error as I enter the txtDateEnd for example "2-2-" it stops here comes the error Data Type mismatch...

Bob Phillips
04-30-2007, 01:06 AM
txtDaysRange.Text = CDate(txtDateEnd.Text) - CDate(txtDateStart.Text)

Bob Phillips
04-30-2007, 01:11 AM
Just saw your follow-up



With txtDateEnd
If Right(.Text, 1) = "/" Then
If Len(.Text) - Len(Replace(.Text, "/", "")) = 1 Then
.Text = .Text & Month(Date) & "/"
End If
.Text = .Text & Year(Date)
End If
End With
With txtDateStart
If Right(.Text, 1) = "/" Then
If Len(.Text) - Len(Replace(.Text, "/", "")) = 1 Then
.Text = .Text & Month(Date) & "/"
End If
.Text = .Text & Year(Date)
End If
End With
txtDaysRange.Text = CDate(txtDateEnd.Text) - CDate(txtDateStart.Text)

jammer6_9
04-30-2007, 01:52 AM
xld upon entering in txtDateStart "1/" gives automtic result of 1/4/20072007 and when I enter in txtDateEnd "1" gives Run time error '13' type mismatch


Just saw your follow-up



With txtDateEnd
If Right(.Text, 1) = "/" Then
If Len(.Text) - Len(Replace(.Text, "/", "")) = 1 Then
.Text = .Text & Month(Date) & "/"
End If
.Text = .Text & Year(Date)
End If
End With
With txtDateStart
If Right(.Text, 1) = "/" Then
If Len(.Text) - Len(Replace(.Text, "/", "")) = 1 Then
.Text = .Text & Month(Date) & "/"
End If
.Text = .Text & Year(Date)
End If
End With
txtDaysRange.Text = CDate(txtDateEnd.Text) - CDate(txtDateStart.Text)

jammer6_9
04-30-2007, 02:16 AM
File attache...

Paul_Hossler
04-30-2007, 11:12 AM
Another way -- I added a "Calc" and a "Exit" button and changes the Results textbox to a Label.

Could always use more error checking

Paul




Option Explicit
Private Sub btnCalc_Click()
Dim dateStart As Date, dateEnd As Date

dateStart = CDate(txtDateStart.Text)
dateEnd = CDate(txtDateEnd.Text)

labResult.Caption = (dateEnd - dateStart) & " Days"
End Sub

Private Sub btnExit_Click()
Me.Hide
Unload Me
End Sub

Private Sub UserForm_Activate()
labResult.Caption = ""
End Sub

Bob Phillips
04-30-2007, 11:28 AM
I thought I replied to this?

The change event is not good here. Try



Private Sub txtDateStart_Exit(ByVal Cancel As MSForms.ReturnBoolean)
CompleteDate Me.txtDateStart
If Me.txtDateEnd.Text <> "" And Me.txtDateStart.Text <> "" Then
txtDaysRange.Text = CDate(txtDateEnd.Text) - CDate(txtDateStart.Text)
End If
End Sub

Private Sub txtDateEnd_Exit(ByVal Cancel As MSForms.ReturnBoolean)
CompleteDate Me.txtDateEnd
If Me.txtDateEnd.Text <> "" And Me.txtDateStart.Text <> "" Then
txtDaysRange.Text = CDate(txtDateEnd.Text) - CDate(txtDateStart.Text)
End If
End Sub

Private Sub CompleteDate(ByRef DateField As MSForms.TextBox)
With DateField
If Right(.Text, 1) <> "/" Then
If Len(.Text) - Len(Replace(.Text, "/", "")) = 2 Then
Exit Sub
End If
.Text = .Text & "/"
End If
If Len(.Text) - Len(Replace(.Text, "/", "")) = 1 Then
.Text = .Text & Month(Date) & "/"
End If
.Text = .Text & Year(Date)
End With

End Sub

jammer6_9
04-30-2007, 01:17 PM
That was a perfect example "Paul_Hossler" :clap: and I thank you for that however I am going to "xld" codes wherein it fit best my program. Thanks once again xld for your help out :bow: ...