Solved

Splicing a date range into date

Posted on 2014-04-01
11
225 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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
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.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

759 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

19 Experts available now in Live!

Get 1:1 Help Now