Solved

MS Access - String to Date Conversion (Part II)

Posted on 2012-12-26
5
446 Views
Last Modified: 2012-12-30
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
0
Comment
Question by:maroulator
5 Comments
 
LVL 1

Expert Comment

by:Dodsworth
ID: 38722391
hello

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

Expert Comment

by:jerryb30
ID: 38722411
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]
0
 
LVL 26

Assisted Solution

by:jerryb30
jerryb30 earned 250 total points
ID: 38722419
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")))
0
 

Author Comment

by:maroulator
ID: 38722526
Dodsworth,

To answer your question, it's MS Access 2010 and I am looking to eventually display the data in a continuous form.
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 250 total points
ID: 38723618
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
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

815 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now