Simple Prob: Current Date - A given Date

[VBAmateur]

I am trying to subtract a given date value from the current date and the following is what I am trying to use but its saying 'Type Mismatch'. I know this has got to be an easy one, Any suggestions??

txtDaysSinceAssnd = DateDiff("d", DateValue(Now), DateValue(txtDateEntered))

[seaweed]

I pasted your code exactly as it was into the click event of a command button, typed a valid date into txtDateEntered, and the results were perfect. There is nothing wrong with your code.

The problem may be that you aren't entering a valid date to start with. You might try some data validation before executing that line of code, such as the following code suggests:

Option Explicit

Private Sub cmdShowDateDiff_Click()
    
    If IsDate(txtDateEntered) Then
        txtDaysSinceAssnd = DateDiff("d", DateValue(Now), DateValue(txtDateEntered))
    Else
        MsgBox "Please enter a valid date", , "Bad Date Entered"
        With txtDateEntered
            .SelStart = 0
            .SelLength = Len(.Text)
            .SetFocus
        End With
    End If

End Sub

Hope that helps!

~seaweed

[Magik]

Cut and Paste this code into a form with two text boxes Text1 and Text2 and one command button Command1 and you should be able to follow the flow of the code really easily.
Basically, you have to use the Date type variable and make sure that the date you are sending the DateDiff Function is correctly formatted. In this example I used the FormatDate(DateEntered, vbshortdate) function to format the date properly. Let me know if you have questions.


Option Explicit

Function GetDate(DateEntered As Date) As String
Dim DaysDiff As Integer
DaysDiff = DateDiff("d", Date, DateEntered, vbUseSystemDayOfWeek, vbUseSystem)
GetDate = DaysDiff
End Function

Private Sub Command1_Click()
Dim DateEntered As Date, txtDaysSince As String
If Text1.Text <> "" Then
DateEntered = Text1.Text
DateEntered = FormatDateTime(DateEntered, vbShortDate)
txtDaysSince = GetDate(DateEntered)
Text2.Text = txtDaysSince
End If
End Sub

[seaweed]

I'm not trying to be too critical here, but the code from Magik will still most likely give you a Type Mismatch error if you don't enter a valid date. The format function won't be able to format "Hello" into a valid date for you!

Also, most naming conventions for VB say that the prefix "txt" should be reserved for text box controls, not strings. You should use "s" or "str" for strings (according to my professors, anyway).