Print Page | Close Window

Need help with date formulas and extracting data

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2022
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=894
Printed Date: 05 May 2025 at 10:35am


Topic: Need help with date formulas and extracting data
Posted By: ChrisJ517
Subject: Need help with date formulas and extracting data
Date Posted: 20 Jun 2007 at 12:32pm

I have a some fields that I need some help pulling data from:

 

1.  Field CAL.ACCOUNTNO  This string field contains both a Sales Rep and a period.  For Example, QSCJONES20070228.  I need to be able to pull out the 20070228 and then convert it to read Feb-07 in a date format.  I also need to pull out the sales rep name.  However, I don't think I can use the left or right function because the name will always be a different length.   How can I accomplish both of these?

 
2. Field CAL.COMPANY. This string field contains data that is formated like this:           50000              0         211987              0
 
This first set of numbers is what I need.  This is a sales quota number.  I need to extract that and put it in number format.  Again, not sure if I can use the left or right function becuase this number could be 50000 or 100000 (a different lenght).  Is there a way I can have it pull everything from left to right until it gets to a space?
 
Thanks for your help


Replies:
Posted By: BrianBischof
Date Posted: 20 Jun 2007 at 1:48pm
The first question isn't hard. You know that the date is always going to be 8 characters long and its at the end. So you can use that in your calculations. The length of the name is going to be the length of the field minus 8. Use that result with the Left() function to parse out the Name. The date is always the right-most eight characters, so I would save that to a string and then parse the string out into year, month, day and pass that to the Date() function to convert it to a date variable.

The second question can use InStr() to find the first space in the string. Then use that number (minus 1) and you have the length of the salesperson number and that can be used with the Left() function.

Viola!


-------------
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>

Posted By: ChrisJ517
Date Posted: 20 Jun 2007 at 1:51pm
How would I write the Instr formula?

Posted By: BrianBischof
Date Posted: 20 Jun 2007 at 1:56pm
I'm typing this in off the top of my head without testing any of it....

NumberVar SpaceLoc;
StringVar SalesNo;
SpaceLoc := Instr({Cal.Company}, " ");
SalesNo := Left({Cal.Company}, SpaceLoc-1);



-------------
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>

Posted By: ChrisJ517
Date Posted: 21 Jun 2007 at 6:56am
Didn't work.  The formula didn't have any errors, but it didn't return any values.  Any ideas?

Posted By: BrianBischof
Date Posted: 21 Jun 2007 at 11:14am
No ideas without being able to play with the data. It's a fairly easy formula so I would think that something trivial is being missed here. I would suggest taking it one step at at time and learning how the function works. Create a formula that just returns the Instr() value and pass it different strings. Then look at the return values to see how your string data works with it. Then you'll probably have an "aha!" moment and see how to change the formula to make it work. What I gave you should be correct, so its probably one small detail that I missed that would make it work for your particular string value.

One more question: It appears that there are leading spaces in front of the salesman id. Was this just how you typed it in the question or are there a certain number of leading spaces? That will make a big difference.


-------------
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>


Print Page | Close Window