need help converting text to date in CR11

[adoctor]

Hello.

I started a thread last week and got some great feedback, but I still don't have a workable formula for my issue. Here it is:

I use CR11 with The Museum System that utilizes MS Access. I have a text field called Display Date that we use to enter dates for bibliography records and I want to sort by ascending date. There is a real date field called Begin Date that has only a year in it and I was able to sort by using the Sort Expert. All dates come up sorted correctly by year, but it's the month and day that don't sort correctly. THe dates are in different formats, including one that shows 'City, Year' and I want to sort in date order. I know that I have to convert the text dates to dates. Here is a sample list of dates in the order they are sorting right now:

17 March 1958, 65
(November 1965)
(November 1965)
(January 1966)
(February 1969)
(New York, 1973)
(Buffalo, 1976)
5 December 1976
(1977)
17 April 1977
27 August 1977
16 April 1977
5 February 1977
September 1981
(San Diego, CA, 1984)
8 December 1997
23 October 1997
9 October 1997

I got a formula suggestion from synapsevampire(Programmer) and tried to make it work but kept getting an error message. Here's the formula sent to me:

whileprintingrecords;
stringvar array MyDate:= join({table.mmmm-yyyy field}," ");
datevar OutDate;
numbervar MyMonth;
if ubound(MyDate) = 3 then
(
if MyDate[2] = "January" then
MyMonth:=1
else
if MyDate[2] = "February" then
MyMonth:=2
else
if MyDate[2] = "March" then
MyMonth:=3
else
... you get the idea, finish this ...
);
if ubound(MyDate) = 3 then
OutDate:= cdate(val(MyDate[3]),MyMonth,val(MyDate[1]);
Outdate

This should work on the dates with 3 values.

And here's what I tried. Can anyone tell me what is incorrect, and once this is correct, do I sort on the formula...not sure what the next step is:


whileprintingrecords;
stringvar array MyDate:= join({ReferenceMaster.DisplayDate}," ");
datevar OutDate;
numbervar MyMonth;
if ubound(MyDate) = 3 then
(if MyDate[2] = "January" then MyMonth:=1 else
if MyDate[2] = "February" then MyMonth:=2 else
if MyDate[2] = "March" then MyMonth:=3 else
if MyDate[2] = "April" then MyMonth:=4 else
if MyDate[2] = "May" then MyMonth:=5 else
if MyDate[2] = "June" then MyMonth:=6 else
if MyDate[2] = "July" then MyMonth:=7 else
if MyDate[2] = "August" then MyMonth:=8 else
if MyDate[2] = "September" then MyMonth:=9 else
if MyDate[2] = "October" then MyMonth:=10 else
if MyDate[2] = "November" then MyMonth:=11 else
if MyDate[2] = "December" then MyMonth:=12 else '');
if ubound(MyDate) = 3 then
OutDate:= cdate(val(MyDate[3]),MyMonth,val(MyDate[1]));
Outdate
I get an error message asking me for the correct field name for this bit: stringvar array MyDate:= join({table.mmmm-yyyy field}," ");

When I replace {table.mmmm-yyyy field} with {ReferenceMaster.DisplayDate} it tells my that a string array is required.

Your help will be much appreciated!

Angie

 

[synapsevampire]

Synapsevampire is a fewl

whileprintingrecords;
stringvar array MyDate:= split({ReferenceMaster.DisplayDate}," ");
datevar OutDate;
numbervar MyMonth;
if ubound(MyDate) = 3 then
(if MyDate[2] = "January" then MyMonth:=1 else
if MyDate[2] = "February" then MyMonth:=2 else
if MyDate[2] = "March" then MyMonth:=3 else
if MyDate[2] = "April" then MyMonth:=4 else
if MyDate[2] = "May" then MyMonth:=5 else
if MyDate[2] = "June" then MyMonth:=6 else
if MyDate[2] = "July" then MyMonth:=7 else
if MyDate[2] = "August" then MyMonth:=8 else
if MyDate[2] = "September" then MyMonth:=9 else
if MyDate[2] = "October" then MyMonth:=10 else
if MyDate[2] = "November" then MyMonth:=11 else
if MyDate[2] = "December" then MyMonth:=12 else '');
if ubound(MyDate) = 3 then
OutDate:= cdate(val(MyDate[3]),MyMonth,val(MyDate[1]));
Outdate

Keep in mind that this will only work on the 3 date values.

-k

 

[adoctor]

Thanks for the quick response. Just tried this and CR is asking me for a number after the "12 else" here: if MyDate[2] = "December" then MyMonth:=12 else '');


A

 

[adoctor]

If I change the "December" line to this, it's error free:

if MyDate[2] = "December" then MyMonth:=12 );

Does this wreck the formula? And what do I do with this once it's correct?

THank you, thank you so much for your help...Angie