Retarded String Field in Table

[Soleil525]

Hi,
I’m working with a Goldmine database and Crystal Report version 8. There is a string field that the user enters the user name and the date the data is entered. Here is an example of the data in the table:
JohnD 20010910 10:29am
JannyDoe 20010830 8:55am
UserNumber5 19970120 1:10pm
Etc…
As you can see, the user name is varies in length. The user wants to filter the report by the date portion in this field. Can someone please help me to write a formula that extract only the date portion from this field so that I can base the date parameter on the formula. Your help is greatly appreciated.

 

[konad]

parse it out by doing a string search like
Right({table.tablename}, 15)

Returns the last 15 characters of the field as a String.
then you could do an isdate() function on that returned string to make sure you got it

 

[dgillz]

left(Right({table.tablename},15),8) will return the 8 characters that make up a date, or "20020408" for today's date. Convert that to a number and thern convert the number to a date:

NumbertoDate(ToNumber(Left(Right({table.tablename},15),8)))

The NumbertoDate() function is available for download on the CD website, or I can email it to you. It takes an 8 digit integer in the YYYYMMDD format and converts it to a real date.

Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[Soleil525]

Thank you all for you response. I'll try it and post the result.

Instructor,
Will you please send me the NumberToDate function. I just send you my email address.

 

[Soleil525]

I place the u2ltdate.dll in the Winnt\Crystal directory where all the other DLLs. Then I create a formula:

@ConvertDate
NumbertoDate(ToNumber(Left(Right({CONTSUPP.CITY},15),8)))

When I check the formula, No Error Found. However, when I place the ConvertDate formula on the report then refresh, I receive this message: "The String is non-numeric". What is wrong with this formula.

Instructor,
Thank you for the DLL.

 

[kenhamady]

try replacing the word ToNumber with the word VAL. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[kenhamady]

Actually, I don't trust counting from the right. I would key off of the first space, something like this:

NumbertoDate( Val( {field}
[InStr( {field},' ') + 1 to InStr({field},' ') + 10]) )


By the way, which Goldmine field is this?


Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[Soleil525]

Thank you all for your help. I've been successfully convinced the users to use a combination of true date field and another string field to pull the record. Hopefully they won't change their mind (. The field is ContSupp.City. The way the user explains to me is this field is generated by the system (the username of person that enter the record plus the date the record was created). I don't think it's true. I look at their data and noticed that some records have the username and date some don't and other have crazy data like U.S City and wild stuff in the field. The method suggested by Dgillz is working fine as long as it does not hit a record where only the username no date or some other wild stuff in the field. What can I say...can't argue with the user that their data is all screw up...Sorry I'm just venting!!! Again, thank you all you guys are the best ))

 

[kenhamady]

That table in Goldmine has records that are used many different ways, and so the field names aren't always helpful. What you are getting is the user name and the date the record was added. And, yes, these are added by the system for certain supplementary records (like linked files).

There is another field called LastDate in that record that gives the date last modified, which may be differnt. That is the only true date field I see in that table. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com