Solved

Splicing a date range into date

Posted on 2014-04-01
11
235 Views
Last Modified: 2014-04-24
I have the following date...

1/1/2013 - 1/31/2013

I would like to get the date to be.. I want just the end of the date as my date.

1/31/2013
0
Comment
Question by:Newbi22
  • 6
  • 4
11 Comments
 
LVL 35

Expert Comment

by:PatHartman
ID: 39970387
You can use InStrRev() to find the rightmost space and then copy everything past that space to the date field.

Me.MyDate = Right(Me.DateRange, InstrRev(Me.DateRange, " ") + 1)

If your data is not consistent, you may need to validate the string before trying to copy it to MyDate.
0
 

Author Comment

by:Newbi22
ID: 39970403
What is the difference in

Me.MyDate

Me.Date Range

My field name is called CompDate
0
 
LVL 35

Expert Comment

by:PatHartman
ID: 39970455
Me.MyDate is the destination control and Me.DateRange is the source.  So, Me.DateRange is Me.CompDate in your form.  If you are not doing this in the code module of a form, you wouldn't use the Me. qualifier.  But you didn't specify where you were doing this so I assumed a form since I thought I was answering an Access question.  If you want to do this in T-SQL, you will need to figure out the equivalent T-SQL function.  If you want to do this in an Access querydef, the InstrRev() function will work.
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

Author Comment

by:Newbi22
ID: 39970471
I have a report writer. It see the date as a text string. I need to create a calculated field to cut the last part of the date off, so I get the month end. From what you provide me, I got....

Right(CompDate, InstrRev(CompDate, " ") + 1)
0
 
LVL 35

Expert Comment

by:PatHartman
ID: 39970814
What is your report writer?  Are you using Access at all?  If not, someone else will need to help you.

This will convert the string to a date as long as Access recognizes it as a valid format.
CDate(Right(Me.DateRange, InstrRev(Me.DateRange, " ") + 1))

Once the string is a date, you can use date functions such as Month(...) to extract the month part of the date.  Why do you need just the month?  If you are trying to create selection criteria, you almost certainly need the year as well.
0
 

Author Comment

by:Newbi22
ID: 39972581
Pat,

I do not need just the month. I want the last date in the date range. I need to split it off the from the range. I have it from another person....

var dateArr = CompDate.split(" - ");
dateArr[1];
0
 

Author Comment

by:Newbi22
ID: 40020998
I've requested that this question be closed as follows:

Accepted answer: 0 points for Newbi22's comment #a39970471

for the following reason:

Works... thank you.
0
 

Author Comment

by:Newbi22
ID: 40020879
I was gone and could not get back to it
0
 
LVL 35

Accepted Solution

by:
PatHartman earned 500 total points
ID: 40020990
Sorry, the function should have been Mid()
Mid("1/1/2013 - 1/31/2013", InstrRev("1/1/2013 - 1/31/2013", " ") + 1)
0
 

Author Closing Comment

by:Newbi22
ID: 40020999
ok
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server functions 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 Ac…
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.

770 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