OK, I consider myself to be a reasonably bright person, but when it comes to working with dates in FOCUS, why do I have such a hard time?

I want to create a column title that is the last day of the prior month. I can write a define to calculate the date, but I can't figure out how to get it to appear as a column title.

Anyone??

So if I understand you correctly, it's not the date that's the problem. The question really is, how to get a value into a column title? The only way I can think of doing that is to out the value in an & var.

TABLE FILE file
PRINT LASTDAY
ON TABLE SAVE AS LASTDAY
END
-RUN
-READ LASTDAY &LASTDAY.A10.

TABLE FILE file
PRINT MYDATE AS &LASTDAY

hmm. well, there's no reason to print it out to a file and reread it. you can calculate your date in dialog manager, as an &var, using the systemvar for today: &YYMD for example as a starting point, if you need one.
and then use it directly in your fex
PRINT CAR AS '&MYVAR'
note, that if you make your date nice and pretty
"July 27, 2004", that comma will force your colhead to wrap to 2 lines;
But...is your date actually a field or a DEFINE'd field in your actual database?

That's not gong to help you calculate the last day of the prior month however. Calculating dates like that in DM is just not worth the headache. The minimal overhead of TABLE FILE, HOLD, and -READ is worth it.

The date does exist in the database (not a defined field), but it's in the wrong format (YYMD instead of MtrDYY). I couldn't get it to work when I created a defined field that reformatted the existing date field. How do you get a defined field value into a column title?

Here's what I'm trying to do in a nutshell:

Prompt user for a beginning date and an end date in YYMD format. Pass those dates to the database to retrieve the right data. That works fine.

But then I want to take those 2 dates, reformat them to be in MtrDYY (Month spelled out, day, 4 digit year) and then use those reformatted dates as column titles.

I tried the -READ idea, but it didn't work. I kept getting a date conversion error.

Any more ideas would be greatly appreciated before I rip all my hair out!!

Something like this: Sorry if it didn't align well.

PAGE 1



July 20, 2004 July 21, 2004 July 22, 2004
MAJ
------------------------------------------------------------------
AAPA 0 1 0
AE 0 4 0
ASTU 1 0 2
BIOL 2 1 1
BRCT 0 0 1
BSAD 0 0 1
CENG 1 0 1
CET 0 0 1
CHEM 1 0 0
Came from
SET STYLEMODE = FIXED
DEFINE FILE MMAPTBL_UNO_PROD
PRETTYDATE/MtrDYY = MM515;
END
TABLE FILE MMAPTBL_UNO_PROD
WHERE MM515 IS-FROM '&DATE1' TO '&DATE2'
COUNT STU_ID AS ''
BY MAJOR_1 AS 'MAJ'
ACROSS PRETTYDATE AS ''
END

-READ shouldn't be doing any type of date conversion. As long as you use SAVE or HOLD FORMAT ALPHA followed by a -RUN and -READ into an alpha (-READ filename &var.An.) then there shouldn't be a problem. Can you post your code?

Here's my code....

SET &BEG_DATE = &BEG_DATE.Please enter beginning date (YYMD): .;
SET &END_DATE = &END_DATE.Please enter ending date (YYMD): .;


TABLE FILE VHOLDING
PRINT stuff
BY stuff
WHERE (START_DATE EQ &BEG_DATE OR &END_DATE)
ON TABLE HOLD AS PORTREV
MORE
FILE REALESTATE
WHERE (START_DATE EQ &BEG_DATE OR &END_DATE)
END

DEFINE FILE PORTREV
LAST_YR/D15=IF START_DATE EQ &BEG_DATE THEN STATEMENT_VALUE ELSE 0;
CURR_YR/D15=IF START_DATE EQ &END_DATE THEN STATEMENT_VALUE ELSE 0;
END

TABLE FILE PORTREV
SUM
CURR_YR AS ' ' <-- This is where I want &BEG_DATE reformatted
LAST_YR AS ' '<-- This is where I want &END_DATE reformatted

Ok, I see the problem now. When you SAVE or HOLD the MtrDYY date, it actual saves as YYMD. Try this.

SET PAGE=NOPAGE
DEFINE FILE PORTREV
BEGDATE1/YYMD = &BEG_DATE;
BEGDATE2/MtrDYY = BEGDATE1;
ENDDATE1/YYMD = &END_DATE;
ENDDATE2/MtrDYY = ENDDATE1;
END
TABLE FILE PORTREV
PRINT
BEGDATE2 AS ''
ENDDATE2 AS ''
IF RECORDLIMIT EQ 1
ON TABLE HOLD AS MYDATE FORMAT WP
END
-RUN
-READ MYDATE
-READ MYDATE &NEWBEG.A20. &NEWEND.A20.
-TYPE &NEWBEG &NEWEND

That got me a little closer, but the -READ isn't working for some reason. I get FOC295: A Value is Missing for : NEWBEG.

Here is some code with the car file and the results. &H1 has a date with a translated month that I used as the column name for country in the car table request below.


DEFINE FILE CAR
FRMDT/MDY WITH COUNTRY='072704';
FRMDTA/A8MDYY=FRMDT;
MTH/A2=EDIT(FRMDTA,'99');
DAYS/A2=EDIT(FRMDTA,'$$99');
YRS/A4=EDIT(FRMDTA,'$$$$9999');
MTHT/A3=DECODE MTH(01 JAN 02 FEB 03 MAR 04 APR 05 MAY 06 JUN
07 JUL 08 AUG 09 SEP 10 OCT 11 NOV 12 DEC ELSE ' ');
OUTDATE/A13=MTHT|' '|DAYS|' '|YRS;
END
TABLEF FILE CAR
PRINT OUTDATE
IF RECORDLIMIT EQ 1
ON TABLE HOLD AS HOLD1 FORMAT ALPHA
END
-RUN
-SET &H1=' ';
-READ HOLD1 &H1


TABLEF FILE CAR
PRINT COUNTRY AS '&H1'
END

PAGE 1


JUL 27 2004
-------------
ENGLAND
FRANCE
ITALY
JAPAN
W GERMANY

The problem with that is I don't want to hardcode the date. But just for haha's, I tried that exact code, and I got the same error:
A Value is Missing for &H1

It seems to get hung up on the -READ in both cases. Could it be something in the version I'm using? WF 5.2.4? Or a setting I'm missing?

The date in my example was hardcoded in the define but it could be a easily be a date that was prompted for from a form and then inserted in the define.

The code was developed in mainframe Focus 7.11. I pasted the exact same code in an RPC and executed it in WF 4.36 and it also worked there too. Can you be more specific about the error message your getting?

Jen, this code below works, and the -READ works nicely but dates don't seem to get saved in the reformatted logoc;

DEFINE FILE PORTREV
BEGDATE1/YYMD WITH ASSET_CLASS= &BEG_DATE;
BEGDATE2/MtrDYY WITH ASSET_CLASS= BEGDATE1;
ENDDATE1/YYMD WITH ASSET_CLASS= &END_DATE;
ENDDATE2/MtrDYY WITH ASSET_CLASS= ENDDATE1;
END
TABLE FILE PORTREV
PRINT BEGDATE2 ENDDATE2
IF RECORDLIMIT EQ 1
ON TABLE SAVE FORMAT ALPHA
END
-RUN
-READ SAVE &NEWBEG.A8. &NEWEND.A8.
-TYPE Newbeg is: &NEWBEG NewEnd is: &NEWEND
-RUN

Here's the echo;
Newbeg is: 02282002 NewEnd is: 02282002

Note: when the file gets saved, it doesn't save the reformatted smart date, it saves the 8 character date!!!

To handle all of the various date formats in headings and for downloads to Excel that our customers wanted, I created a focus file called CALENDAR (many years ago).

It can be JOINed to based on a real Focus date, then you can print out (BY, ACROSS, whatever) whichever date format you like.

If you contact me offline, I can send you the .mas and the .fex that builds the .foc file. We're currently in a unix environment--don't know whether modifications might be needed for other environments.

I'm at suzysmith@mac.com.

I did get that to work - the trick was to use SAVE instead of HOLD. Now to figure out how to format the date so it looks pretty on a report!

Susannah? You've been conspicuously quiet on this one. Where are you? I need your bag of tricks!