Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2004
    Posts
    21

    Question Unanswered: Stringing Numeric Fields together

    I have a date field in my database that is broken out into Century, Year, Month, and Day. They are all 2 position numeric. How can I string together the fields to have a resulting field look like this:

    04/14/2004

    I need to have the slashes (/) in-between the month, day, and century as shown above. I have tried to use the string, and concatanate functions, but have been getting errors. How can this be accomplished? Any help would be greatly appreciated!!

    Thanks!

  2. #2
    Join Date
    Mar 2004
    Posts
    15
    What sort of database? What is the data type for the field? Are you sure it's just one field in the DB, not separate fields? If it is all shoved into one field, what's the format for the data? Would March 15th, 2004 look like: 20040315?

  3. #3
    Join Date
    Apr 2004
    Posts
    21
    This is coming from a DB2 database enviroment. The data in in 4 seperate fields:

    1. Century
    2. Year
    3. Month
    4. Day

    All of the fields are defined as numeric no decimal.

  4. #4
    Join Date
    Mar 2004
    Posts
    15
    Ok, go to your field explorer on the CR toolbar, it looks like a little window with 3 sets of 3 small lines, and says "Insert Fields" when you mouseover it.

    Once you've opened that, right click "Formula Fields" and select New.

    Name your new field, i.e. "formattedDate".

    Now, if none of your fields will ever be empty for the selections you're doing, it's as easy as entering the following:
    Code:
    CSTR({table.Month}) + "/" + CSTR({table.Day}) + "/" + CSTR({table.Century}) + CSTR({table.Year)}
    Close that out, then drag your new formula field from your field explorer onto your report and you should be good to go.

  5. #5
    Join Date
    Apr 2004
    Posts
    21
    Thank you so much for the help. It worked except for one thing. The fields coming from the DB2 enviroment are defined as numeric 2 positions with no decimals. When Crystal imports them, it automatically adds a 2 position decimal to the field. When building the formuls, I was expecting a result like 01/01/2004, but instead I get a result of 1.00/1.00/20.0004.00. How can I make Crystal not add a two position decimal to the numeric fields coming in? I also noticed that Crystal is doing this to all fields that are numeric with no decimals. It automatically put a two position decimal on the field in Crystal.

  6. #6
    Join Date
    Mar 2004
    Posts
    15
    doh, forgot to add the format strings, ok
    Code:
    CSTR({table.Month}, "00") + "/" + CSTR({table.Day}, "00") + "/" 
       + CSTR({table.Century, "00"}) + CSTR({table.Year), "00"}
    If you want to just display it like 3/5/2004 instead of 03/05/2004, change the format strings in the cstr functions for month and day to "0".
    Last edited by Disson; 04-16-04 at 19:50.

  7. #7
    Join Date
    Apr 2004
    Posts
    21
    That worked perfectly! Thank you so much for your help!!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •