Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.
Course Of The Month
1 day, 19 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
MS Access - String to Date Conversion (Part II)
Posted on 2012-12-26
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(R
Example.xlsx
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(R
Example.xlsx
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
2
- +1
5 Comments
hello
What version of Access?
Are you wanting to display this data in a form/continuous form or to update data?
What version of Access?
Are you wanting to display this data in a form/continuous form or to update data?
IIf(InStr([start date],"-")>0,Format(DateSe
Typo:
IIf(InStr([start date],"-")>0,Format(DateSe
IIf(InStr([start date],"-")>0,Format(DateSe
Dodsworth,
To answer your question, it's MS Access 2010 and I am looking to eventually display the data in a continuous form.
To answer your question, it's MS Access 2010 and I am looking to eventually display the data in a continuous form.
Active today
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
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
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works
The attack works by targeti…
- The Email Laundry
- Email Servers
- Cybersecurity
- Email Protocols
- Network Security
- Security, *Phishing
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses
Course of the Month1 day, 19 hours left to enroll
717 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.