CDate conversion question

[mehaa]

Hi all,

I am a novice with access programming.And just started to learn.
I have a question similar to one of the posts in this forum.
I have a semester field that shoudl have input data in the format FSYYYY, SSYYYY OR SUYYYY (SS- spring, FS- Fall, SU-Summer and YYYY is the 4 digit year format.)
E.g. FS2010 for current semester.

The semesters in the university where i work run like this:

SS - 1/10/ TO 5/24/

SU - 6/6/ TO 8/9/

FS - 8/15/ TO 12/23/

i want to have a code that can check the current date with the above date ranges and and insert a yes if the semester is current and no if it is not.

That is if it is FS2010 then it is the current semester and Active field is "Yes"

I tried to use a code that is not mine(have found it in this forum), but i can't get it to work:



============================================

Private Sub Stu_title_subform_Current()
'  03/SP
'CDate : If you have a string or an expression that you want to convert to a date value, use  CDate()  based on the following formula:Result = CDate(Value to Convert)
 
  Select Case Left(Semester, 2)
  Case "SS"
   If Date >= CDate(Left(Semester, 2) & "1/10/") And Date <= CDate(Left(Semester, 2) & "5/24/") Then
    Me.Active = "Yes"
Else
    Me.Active = "No"
End If
   Case "SU"
If Date >= CDate(Left(Semester, 2) & "6/6/") And Date <= CDate(Left(Semester, 2) & "8/9/") Then
      Me.Active = "Yes"
Else
    Me.Active = "No"
End If
   Case "Fs"
If Date >= CDate(Left(Semester, 2) & "8/15/") And Date <= CDate(Left(Semester, 2) & "12/23/") Then
    Me.Active = "Yes"
Else
    Me.Active = "No"
End If
 
  End Select
 
End Sub

===============================================

Private Sub Semester_AfterUpdate()
 
 
Select Case Left(Semester, 2)
Case "SS"
If Date >= CDate(Left(Semester, 2) & "1/10/") And Date <= CDate(Left(Semester, 2) & "5/24/") Then
 
    Me.Active = "Yes"
Else
    Me.Active = "No"
End If
Case "SU"
If Date >= CDate(Left(Semester, 2) & "6/6/") And Date <= CDate(Left(Semester, 2) & "8/9/") Then
      Me.Active = "Yes"
Else
    Me.Active = "No"
End If
Case "FS"
If Date >= CDate(Left(Semester, 2) & "8/15/") And Date <= CDate(Left(Semester, 2) & "12/23/") Then
    Me.Active = "Yes"
Else
    Me.Active = "No"
End If
 
End Select
 
End Sub

i don't know where it is going wrong

Any help is much appreciated.


Thanks
m_i

[colintis]

The problem I see in here is you are trying to convert a string to date. The output from CDate(Left(Semester, 2) & "1/10/") is "SS1/10/". So you should change to the following:

Private Sub Stu_title_subform_Current()
'  03/SP
'CDate : If you have a string or an expression that you want to convert to a date value, use  CDate()  based on the following formula:Result = CDate(Value to Convert)
 
  Select Case Left(Semester, 2)
  Case "SS"
   If Date >= CDate("1/10/" & Right(Semester, 4)) And Date <= CDate("5/24/" & Right(Semester, 4)) Then
    Me.Active = "Yes"
Else
    Me.Active = "No"
End If
   Case "SU"
If Date >= CDate("6/6/" & Right(Semester, 4)) And Date <= CDate("8/9/" & Right(Semester, 4)) Then
      Me.Active = "Yes"
Else
    Me.Active = "No"
End If
   Case "Fs"
If Date >= CDate("8/15/" & Right(Semester, 4)) And Date <= CDate("12/23/" & Right(Semester, 4)) Then
    Me.Active = "Yes"
Else
    Me.Active = "No"
End If
 
  End Select
 
End Sub

Private Sub Semester_AfterUpdate()
 
    Select Case Left(Semester, 2)
        Case "SS"
            If Date >= CDate("1/10/" & Right(Semester, 4)) And Date <= CDate("5/24/" & Right(Semester, 4)) Then
 
                Me.Active = "Yes"
            Else
                Me.Active = "No"
            End If
        Case "SU"
            If Date >= CDate("6/6/" & Right(Semester, 4)) And Date <= CDate("8/9/" & Right(Semester, 4)) Then
                  Me.Active = "Yes"
            Else
                Me.Active = "No"
            End If
        Case "FS"
            If Date >= CDate("8/15/" & Right(Semester, 4)) And Date <= CDate("12/23/" & Right(Semester, 4)) Then
                Me.Active = "Yes"
            Else
                Me.Active = "No"
            End If
    End Select
 
End Sub

Notice, Right(Semester, 4) will get the 4 characters from the right, which is the "2010". So ("1/10/" & Right(Semester, 4)) will become "1/10/2010", and remember to get the Date having the same date format (e.g. mm/dd/yyyy) to your CDate date, otherwise there it will be confusing to the codes with month and day.