Solved

Splicing a date range into date

Posted on 2014-04-01
11
231 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 34

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 34

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
 

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 34

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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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 34

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

932 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

12 Experts available now in Live!

Get 1:1 Help Now