formula editor question

Options
I'm trying to use the function Year() in Formula editor.  I copied this direction from the online help example of what I entered:

The following examples are applicable to Crystal syntax:

<a name="1000074676"></a>Year({file.LAST INVOICE ON})

Returns 1989, where the last invoice date was in 1989.



However when I entered the line above into the formula editor using my field name (Year({CnRelEdu_1.CnRelEdu_1_Date_graduated}), and check it, I receive an error saying that I need to enter an actual year between the first open parentheses and the first bracket.

So what am I doing wrong?

 

Tagged:

Comments

  • {CnRelEdu_1.CnRelEdu_1_Date_graduated} is stored as a string rather than a date. You can see the field type in Crystal by going to the Field Explorer pane, right-clicking on Database Fields and selecting Show Field Type.



    The following formula will take the last 4 characters from the string (I'm assuming that you're using 4-digit years in your graduation dates, i.e. mm/dd/yyyy) and convert them into a number:

    val(right({CnRelEdu_1.CnRelEdu_1_Date_graduated},4))
  • Hello Madeline,

    I have often found that dates do goofy things depending upon how they are formatted.  Many times I have had to put the "Date" function in front of a date field before I could use another function, such as Year.



    For example:   Year(Date({CnRelEdu_1.CnRelEdu_1_Date_graduated}))

    The Date function first makes sure it is in a good date format before the Year function does its thing.

    Give that a try, and have a good day.

     
  • Chester Bammel:

    For example:   Year(Date({CnRelEdu_1.CnRelEdu_1_Date_graduated}))

    The Date function first makes sure it is in a good date format before the Year function does its thing.

    Give that a try, and have a good day.

     

    I don't know about Madeleine, but I get an error "bad date format string" when trying to use the Date() function on that field. Not sure whether that's down to the format that we use for the date (dd/mm/yyyy).

Categories