Can a String be converted into a Date field in VBA?

[dowlingm815]

I need to calculate a value for a date field. However, the month is evaluated to determine the start and end date. Therefore, the fields are declared as strings. The values are moved in for the month, day and year. Thereafter, the string needs to be converted into a date field to query on the date.

Any assistance, would be greatly appreciated.

The following is my current code:

Private Sub txt_MM_cmb_AfterUpdate()

On Error GoTo Err_Hndlr
    
    Dim txt_MM As String
    Dim txt_dd_start As String
    Dim txt_dd_end As String
    Dim txt_yy As String
    Dim txt_start As String
    Dim txt_end As String
        
    
    txt_MM = " "
    txt_dd_start = " "
    txt_dd_end = " "
    txt_yy = "20" & [Forms]![F_Waiver_Yr]![Txt_Waiver_Yr]
    txt_start = " "
    txt_end = " "
    
    
     
    txt_dd_start = "01"

    Select Case Forms!F_Waiver_Yr!txt_MM_cmb
        Case Is = "January"
            txt_MM = "01"
            txt_dd_end = "31"
        Case Is = "February"
            txt_MM = "02"
            Dim Date1 As Date, Date2 As Date, IsLeapYear As Boolean
            Date1 = "January 1," & Year(Now()): Date2 = "December 31," & Year(Now())
            If DateDiff("d", Date1, Date2) = 365 Then
                IsLeapYear = True
                txt_dd_end = "29"
                MsgBox " This is a Leap Year"
            Else
                txt_dd_end = "28"
            End If
        Case Is = "March"
            txt_MM = "03"
            txt_dd_end = "31"
        Case Is = "April"
            txt_MM = "04"
            txt_dd_end = "30"
        Case Is = "May"
            txt_MM = "05"
            txt_dd_end = "31"
        Case Is = "June"
            txt_MM = "06"
            txt_dd_end = "30"
        Case Is = "July"
            txt_MM = "07"
            txt_dd_end = "31"
        Case Is = "August"
            txt_MM = "08"
            txt_dd_end = "31"
        Case Is = "September"
            txt_MM = "09"
            txt_dd_end = "30"
        Case Is = "October"
            txt_MM = "10"
            txt_dd_end = "31"
        Case Is = "November"
            txt_MM = "11"
          txt_dd_end = "30"
        Case Else
            txt_MM = "12"
            txt_dd_end = "31"
    End Select
    
    txt_start = txt_MM & txt_dd_start & txt_yy
    txt_end = txt_MM & txt_dd_end & txt_yy
    
    txt_start = Format(CDate([txt_start]), "mm/dd/yyyy")
    txt_end = Format(CDate([txt_start]), "mm/dd/yyyy")
    
         
txt_MM_cmb_AfterUpdate_Exit:

  Exit Sub


Err_Hndlr:
    MsgBox "[" & Err.Number & "]:  " & Err.Description, vbInformation, "txt_MM_cmb_AfterUpdate()"
End Sub

[Oralloy]

A quick example of using CDate().

Private Sub TEST_CDate()
  Dim myString As String
  Dim myDate As Date
  myString = "February 12, 1969  11:59:59 PM"
  myDate = CDate(myString)
End Sub

[dowlingm815]

Oralloy,

The string data would need to be converted from the form of "02/28/2010" to a date field "02/28/2010." from what i understand of CDate it converts the spelled out date, therefore, my date would be 02/12/1969. i looked into this hopefully and was disappointed since it doesn't actually convert a numeric string.

Thanks anyway.

mary

[Oralloy]

Mary,

I just tried it with your date in Access 2007, and it looks like the date converted correctly in the locals window.

Give it a try and see?

Luck!

[dowlingm815]

Oralloy,

My apologies for the delayed response. However, the simplest approach was to move the string value into the date field.

Thank you for your help.

Mary

[Oralloy]

Mary,

No problems. Hopefully it helped a little.

I often prefer explicit conversions to implicit conversions, as it lets me know what's going on in the code when I have to revisit it later. I'm a little gun-shy about conversions because I've had to debug and clean up lots of code where implicit conversions worked in the "success" cases, and caused terrible things to happen when bogus values are entered by the user.

Anyway - I sure hope your project is working out well.

Cheers!