Problem using CDate with date as string datatype

[loisk]

Hi,

I am facing some problems using date expression as string datatype.
Here's clip of my code.
The (Intvdate) is string datatype in the MySQL linked table.

Private Sub Form_BeforeUpdate(Cancel As Integer)
 
  Dim strToday As String
  Dim noBeforeDay As String
  Dim Intvdate As String
 
  strToday = Date
  noBeforeDay = "04/01/2007"
 
       If CDate((Me.Intvdate) > strToday) Then
               MsgBox "Today is " & strToday & "!" & vbNewLine & _
                "Completion date cannot be later than today. Please fix!"
               Intvdate.SetFocus
               Cancel = True
               Me.Intvdate = ""
        End If
 
        If CDate((Me.Intvdate) < noBeforeDay) Then      
               MsgBox "Completion date cannot be earlier than 04/01/2007. Please fix"
               Intvdate.SetFocus
               Cancel = True
               Me.Intvdate = ""
        End If
 
End Sub

The problem is that the message is triggered ("...cannot be earlier than 04/01/2007") even if the intvdate entered is "01/10/2008"

Can anyone tell me how to correctly convert the string into date while the datatype is string? Thank you for your help!

[puppydogbuddy]

Hi,

I am facing some problems using date expression as string datatype.
Here's clip of my code.
The (Intvdate) is string datatype in the MySQL linked table.

Private Sub Form_BeforeUpdate(Cancel As Integer)
 
  Dim strToday As String
  Dim noBeforeDay As String
  Dim Intvdate As String
 
  strToday = Date
  noBeforeDay = "04/01/2007"
 
       If CDate((Me.Intvdate) > strToday) Then
               MsgBox "Today is " & strToday & "!" & vbNewLine & _
                "Completion date cannot be later than today. Please fix!"
               Intvdate.SetFocus
               Cancel = True
               Me.Intvdate = ""
        End If
 
        If CDate((Me.Intvdate) < noBeforeDay) Then      
               MsgBox "Completion date cannot be earlier than 04/01/2007. Please fix"
               Intvdate.SetFocus
               Cancel = True
               Me.Intvdate = ""
        End If
 
End Sub

The problem is that the message is triggered ("...cannot be earlier than 04/01/2007") even if the intvdate entered is "01/10/2008"

Can anyone tell me how to correctly convert the string into date while the datatype is string? Thank you for your help!

You are on the right track with CDate, except you did not apply it to all of your date strings.

strToday = Date()
noBeforeDay = "04/01/2007"

If CDate(Me.Intvdate) > CDate(strToday) Then
If CDate(Me.Intvdate) < CDate(noBeforeDay) Then

[loisk]

You are on the right track with CDate, except you did not apply it to all of your date strings.

strToday = Date()
noBeforeDay = "04/01/2007"

If CDate(Me.Intvdate) > CDate(strToday) Then
If CDate(Me.Intvdate) < CDate(noBeforeDay) Then

Hi Puppydogpuppy,

Thanks for your reply!
I thought I applied all of my strings by wrapping both expressions, i.e., CDate((Me.Intvdate) > noBeforeDay).

[puppydogbuddy]

Hi Puppydogpuppy,

Thanks for your reply!
I thought I applied all of my strings by wrapping both expressions, i.e., CDate((Me.Intvdate) > noBeforeDay).

No, CDate must be applied individulally to each date string. Consequently, strToday and noBefore'Day remain as date strings that must be converted to Date values for evaluation.in your expressions: For example, the correct expression for CDate((Me.Intvdate) > noBeforeDay) is CDate(Me.Intvdate) > CDate(noBeforeDay).

[loisk]

No, CDate must be applied individulally to each date string. Consequently, strToday and noBefore'Day remain as date strings that must be converted to Date values for evaluation.in your expressions: For example, the correct expression for CDate((Me.Intvdate) > noBeforeDay) is CDate(Me.Intvdate) > CDate(noBeforeDay).

I see! Thank you so much for your help!