MS Access - String to Date Conversion (Part II)

maroulator
maroulator used Ask the Experts™
on
To whom it may concern,

Referrencing the attached file, I have been able to succesfully convert a string with the format ddmmyyyy to a date with the format mm/dd/yyyy, using the formula below. In the attached file, this is shown by converting the value in the Start Date field to the value in the New Start Date field. My sticking point, however, is the last cell of the column whereby I need to convert a value of mmddyyyy - mmddyyyy to a value of mm/dd/yyyy - mm/dd/yyyy. What formula can I use to accommodate the entire column?

I think this is a tough one, so any insight is extremely appreciated.

-Maroulator

IIf([Start Date]="No Existing Value","No Existing Value",Format(DateSerial(Right([Start Date],4),Mid([Start Date],3,2),Left([Start Date],2)),"mm/dd/yyyy")) AS [New Start Date]
Example.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
Dodsworth

Commented:
hello

What version of Access?
Are you wanting to display this data in a form/continuous form or to update data?
jerryb30

Commented:
IIf(InStr([start date],"-")>0,Format(DateSerial(Mid([Start Date],5,4),Mid([Start Date],3,2),Left([Start Date],2))) & " - " & Format(DateSerial(Right([Start Date],4),Mid([Start Date],Len([start date])-6,2),Mid([Start Date],Len([start date])-8,2))),IIf([Start Date]="No Existing Value","No Existing Value",Format(DateSerial(Right([Start Date],4),Mid([Start Date],3,2),Left([Start Date],2)),"mm/dd/yyyy"))) AS [new start date]
jerryb30
Commented:
Typo:
 IIf(InStr([start date],"-")>0,Format(DateSerial(Mid([Start Date],5,4),Mid([Start Date],3,2),Left([Start Date],2))) & " - " & Format(DateSerial(Right([Start Date],4),Mid([Start Date],Len([start date])-5,2),Mid([Start Date],Len([start date])-7,2))),IIf([Start Date]="No Existing Value","No Existing Value",Format(DateSerial(Right([Start Date],4),Mid([Start Date],3,2),Left([Start Date],2)),"mm/dd/yyyy")))
maroulator

Author

Commented:
Dodsworth,

To answer your question, it's MS Access 2010 and I am looking to eventually display the data in a continuous form.
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
Why are you storing these values as strings in the first place?  These are dates, store them that way and you will not have to deal with converting them, and it will be much easier to perform aggregations based upon the dates.

Then, if you want to display "No Existing Value" instead of a date, you can do that using a query and the NZ function:  NZ([DateField], "No Existing Value")

I have a great little function (fnDateRange) which accepts two dates and returns a string that looks similar to your concatenated data above.

Public Function fnDateRange(StartDate As Variant, EndDate As Variant) As String

    'Make sure the date values are valid
    If IsNullOrBlank(StartDate) And IsNullOrBlank(EndDate) Then
        fnDateRange = ""
        Exit Function
    ElseIf IsNull(StartDate) = False And IsNull(EndDate) Then
        EndDate = StartDate
    ElseIf IsNull(StartDate) And IsNull(EndDate) = False Then
        StartDate = EndDate
    End If
   
    If DateValue(StartDate) = DateValue(EndDate) Then
        fnDateRange = Format(StartDate, "d mmm yy")
    ElseIf (Month(StartDate) = Month(EndDate)) And _
       (Year(StartDate) = Year(EndDate)) Then
       fnDateRange = Day(StartDate) & "-" & Day(EndDate) & " " _
                   & Format(StartDate, "mmm yy")
    ElseIf (Month(StartDate) <> Month(EndDate)) And _
       (Year(StartDate) = Year(EndDate)) Then
       fnDateRange = Format(StartDate, "d mmm") & " - " _
                   & Format(EndDate, "d mmm yy")
    Else
       fnDateRange = Format(StartDate, "d mmm yy") & " - " _
                   & Format(EndDate, "d mmm yy")
       
    End If
                   
End Function

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial