Convert text string to date

[zkoneffko]

I have a field that looks like this YCS1292. It is saved as a text field. The right 4 number are the month and the year the account was created. In this example it would be 12/1992. I need to convert this to a date so I can do a between function in the query. Can anyone help?

Thanks!

 

[Brianwarnock]

Expr1: DateSerial(Mid([tdate],6,2),Mid([tdate],4,2),1)
should do the trick assuming the format stays the same and leading zeroes are quoted.

 

[raskew]

Hi -

In the oft chance that the alpha prefix may not always be 3 characters, the following would return the first alpha sequence in a string, to which you'd apply Brian's logic.

The downside to this is, if we decided to throw an integer into the alpha prefix (e.g. YC5S), this function would return that integer rather than the desired 4 position integer representing year/month. Anyway, food for thought.

Public Function GetNumer2(ByVal pstr As String) As Currency
'*******************************************
'Purpose:   Returns the first numerical
'           sequence in a string
'Coded by:  raskew
'Inputs:    ? getNumer2("ABC123")
'Output:    123
'*******************************************
Dim n       As Integer
Dim strHold As String
Dim strKeep As String

   strHold = Trim(pstr)
   n = Len(strHold)
   Do While n > 0
      If val(strHold) > 0 Then
         strKeep = val(strHold)
         n = 0
      Else
         strHold = Mid(strHold, 2)
         n = n - 1
      End If
   Loop
   
   GetNumer2 = val(strKeep)
        
End Function

Bob

 

[Brianwarnock]

As long as the last 4 are mmyy with jan 2000 being 0100 ie all zeroes quoted then

DateSerial(Right([tdate2],2),Left(Right([tdate2],4),2),1)

will do the trick whatever happens elsewhere.

Brian

 

[zkoneffko]

Thanks that worked good!