By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,122 Members | 927 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,122 IT Pros & Developers. It's quick & easy.

Problem using CDate with date as string datatype

P: 97
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2.  
  3.   Dim strToday As String
  4.   Dim noBeforeDay As String
  5.   Dim Intvdate As String
  6.  
  7.   strToday = Date
  8.   noBeforeDay = "04/01/2007"
  9.  
  10.        If CDate((Me.Intvdate) > strToday) Then
  11.                MsgBox "Today is " & strToday & "!" & vbNewLine & _
  12.                 "Completion date cannot be later than today. Please fix!"
  13.                Intvdate.SetFocus
  14.                Cancel = True
  15.                Me.Intvdate = ""
  16.         End If
  17.  
  18.         If CDate((Me.Intvdate) < noBeforeDay) Then      
  19.                MsgBox "Completion date cannot be earlier than 04/01/2007. Please fix"
  20.                Intvdate.SetFocus
  21.                Cancel = True
  22.                Me.Intvdate = ""
  23.         End If
  24.  
  25. 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!
Jan 10 '08 #1
Share this Question
Share on Google+
4 Replies


puppydogbuddy
Expert 100+
P: 1,923
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2.  
  3.   Dim strToday As String
  4.   Dim noBeforeDay As String
  5.   Dim Intvdate As String
  6.  
  7.   strToday = Date
  8.   noBeforeDay = "04/01/2007"
  9.  
  10.        If CDate((Me.Intvdate) > strToday) Then
  11.                MsgBox "Today is " & strToday & "!" & vbNewLine & _
  12.                 "Completion date cannot be later than today. Please fix!"
  13.                Intvdate.SetFocus
  14.                Cancel = True
  15.                Me.Intvdate = ""
  16.         End If
  17.  
  18.         If CDate((Me.Intvdate) < noBeforeDay) Then      
  19.                MsgBox "Completion date cannot be earlier than 04/01/2007. Please fix"
  20.                Intvdate.SetFocus
  21.                Cancel = True
  22.                Me.Intvdate = ""
  23.         End If
  24.  
  25. 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
Jan 11 '08 #2

P: 97
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).
Jan 11 '08 #3

puppydogbuddy
Expert 100+
P: 1,923
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).
Jan 11 '08 #4

P: 97
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!
Jan 11 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.