[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Access VBA If Elseif Syntax Error

Posted on 2009-12-29
8
Medium Priority
?
953 Views
Last Modified: 2013-11-27
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
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

Open in new window

0
Comment
Question by:Lawrence Barnes
  • 4
  • 3
8 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 1800 total points
ID: 26140202
Well, unfortunately you can't use the IN() operator here ... so you need to go with a different approach.

mx
0
 
LVL 4

Assisted Solution

by:fsouzabrasil
fsouzabrasil earned 200 total points
ID: 26140210
send ("2008","2009","2010","2011","2012") to array and find on array values.
0
 
LVL 75
ID: 26140215
Something like this:

ElseIf Len(dt) = 8 And InStr(1, "20082009201020112012", Left(dt, 4)) Then
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 1800 total points
ID: 26140238
Better yet ... using a colon as a delimiter to eliminate confusion:

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

mx
0
 
LVL 5

Author Comment

by:Lawrence Barnes
ID: 26140304
I went a slightly different route after seeing MX's first post, but I think it's in the same direction.  Currently I'm getting a Run-time error '13' (type mismatch) on:
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

Open in new window

0
 
LVL 75
ID: 26140333
Put a break point on that line ... as see what the value of dt is when the error occurs.

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

mx
0
 
LVL 5

Author Comment

by:Lawrence Barnes
ID: 26140911
It does not resolve all date formats, but eliminates a few.  Posting for others traveling down this path.

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

Open in new window

0
 
LVL 5

Author Closing Comment

by:Lawrence Barnes
ID: 31670884
Thank you
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

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

Join & Ask a Question