mx

Solved

Posted on 2009-12-29

Hello EE,

I'm receiving a syntax error on the module below. I'm using Access 2007 in compatability mode. The purpose of the module is to convert different text fields into a consistent MMDDYYYY string format (for import into SAP.)

Could you please take a look at the code below and let me know what syntax I'm using incorrectly. I get a syntax error on the second ElseIf.

Thanks,

LVBarnes

I'm receiving a syntax error on the module below. I'm using Access 2007 in compatability mode. The purpose of the module is to convert different text fields into a consistent MMDDYYYY string format (for import into SAP.)

Could you please take a look at the code below and let me know what syntax I'm using incorrectly. I get a syntax error on the second ElseIf.

Thanks,

LVBarnes

```
Public Function ConvertStrDate(ByVal dt As Variant) As String
Dim datDate As String
If IsNull(dt) Then
dt = Null
ElseIf IsDate(dt) Then
datDate = Format(dt, "mmddyyyy")
ElseIf Len(dt) = 8 And Left(dt,4) In ("2008","2009","2010","2011","2012") Then '20091225 SYNTAX ERROR HERE
datDate = Format(DateSerial(Left(dt, 4), Mid(dt, 5, 2), Mid(dt, 7, 2)), "mmddyyyy")
ElseIf Len(dt) = 8 And Right(Dt,4) In ("2008","2009","2010","2011","2012") Then '12252009
datDate = Format(DateSerial(Right(dt, 4), Mid(dt, 1, 2), Mid(dt, 3, 2)), "mmddyyyy")
ElseIf Len(dt) = 5 Isdate(Cdate(dt)) = True Then 'Excel date in number format
datDate = Format(CDate(dt), "mmddyyyy")
ElseIf Len(dt) = 7 Then 'a mmddyyyy date with the leading zero omitted
dt = "0" & dt
If Left(dt,4) In ("2008","2009","2010","2011","2012") Then
datDate = Format(DateSerial(Right(dt, 4), Mid(dt, 1, 2), Mid(dt, 3, 2)), "mmddyyyy")
End If
Else
datDate = "N/A"
End If
ConvertStrDate = datDate
End Function
```

8 Comments

mx

ElseIf Len(dt) = 8 And InStr(1, ":2008:2009:2010:2011:2012

mx

ElseIf Len(dt) = 5 And IsDate(CDate(dt)) = True Then 'Excel date in number format.

Let me know if you think this equivalent to above.

The dates I'm trying to convert are:

Dt

081009

08102009

20090810

40035

8/10/09

81009

8-10-09

8102009

Aug-10

```
Public Function ConvertStrDate(ByVal dt As Variant) As String
Dim datDate As String
If IsNull(dt) Then
datDate = "N/A"
ElseIf IsDate(dt) Then
datDate = Format(dt, "mmddyyyy")
ElseIf Len(dt) = 8 And CInt(Left(dt, 4)) >= 2008 And CInt(Left(dt, 4)) <= 2012 Then '20091225 SYNTAX ERROR HERE
datDate = Format(DateSerial(Left(dt, 4), Mid(dt, 5, 2), Mid(dt, 7, 2)), "mmddyyyy")
ElseIf Len(dt) = 8 And CInt(Right(dt, 4)) >= 2008 And CInt(Right(dt, 4)) <= 2012 Then '12252009
datDate = Format(DateSerial(Right(dt, 4), Mid(dt, 1, 2), Mid(dt, 3, 2)), "mmddyyyy")
ElseIf Len(dt) = 5 And IsDate(CDate(dt)) = True Then 'Excel date in number format
datDate = Format(CDate(dt), "mmddyyyy")
ElseIf Len(dt) = 7 Then 'a mmddyyyy date with the leading zero omitted
dt = "0" & dt
If CInt(Left(dt, 4)) >= 2008 And CInt(Left(dt, 4)) <= 2012 Then
datDate = Format(DateSerial(Right(dt, 4), Mid(dt, 1, 2), Mid(dt, 3, 2)), "mmddyyyy")
End If
Else
datDate = "N/A"
End If
ConvertStrDate = datDate
End Function
```

For example, IsDate(cdate("MX")) will produce that exact error. So ... dt must always be something that can be interpreted as a date.

mx

Thank you for your help.

LVBarnes

```
Public Function ConvertStrDate(dt As String) As String
Dim datDate As String
Dim yr As Integer ' current year
yr = Year(Date)
Dim BufferYr As Integer
BufferYr = 2
Dim StartYr As Integer 'Within reason start year
StartYr = yr - BufferYr
Dim EndYr As Integer 'Within reason end year
EndYr = yr + BufferYr
If IsNull(dt) Then
datDate = "N/A"
ElseIf IsDate(dt) Then
datDate = Format(dt, "mmddyyyy")
ElseIf Len(dt) = 8 And CInt(Left(dt, 4)) >= StartYr And CInt(Left(dt, 4)) <= EndYr Then '20091225
datDate = Format(DateSerial(Left(dt, 4), Mid(dt, 5, 2), Mid(dt, 7, 2)), "mmddyyyy")
ElseIf Len(dt) = 8 And CInt(Right(dt, 4)) >= StartYr And CInt(Right(dt, 4)) <= EndYr Then '12252009
datDate = Format(DateSerial(Right(dt, 4), Mid(dt, 1, 2), Mid(dt, 3, 2)), "mmddyyyy")
ElseIf Len(dt) = 5 And IsNumeric(dt) = True Then 'Excel date in number format
datDate = Format(dt, "mmddyyyy")
ElseIf Len(dt) = 7 Then 'a mmddyyyy date with the leading zero omitted
dt = "0" & dt
If CInt(Right(dt, 4)) >= StartYr And CInt(Right(dt, 4)) <= EndYr Then
datDate = Format(DateSerial(Right(dt, 4), Mid(dt, 1, 2), Mid(dt, 3, 2)), "mmddyyyy")
End If
Else
datDate = "N/A"
End If
ConvertStrDate = datDate
End Function
```

By clicking you are agreeing to Experts Exchange's Terms of Use.

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**16** Experts available now in Live!