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: ...
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.