Hi all,
We've been on 7.6.11 hotfix 5 for ages and yesterday installed hotfix 8. Today a couple of reports aren't working that search by a date range. Simple report using just one foc file. The data is built into the foc files from txt files.
The txt master file:

FILE=HQAATTRIB, SUFFIX=COM
SEGNAME=PILOT1, SEGTYPE=S2, $
FIELD=DATE, ALIAS=E01, USAGE=DMY, ACTUAL=DATE, $
FIELD=RPOS, ALIAS=E02, USAGE=A4, ACTUAL=A4, $
FIELD=SIZE, ALIAS=E03, USAGE=A15, ACTUAL=A15, $
FIELD=THICK, ALIAS=E04, USAGE=D7.2, ACTUAL=D7.2, $
FIELD=GRADE, ALIAS=E05, USAGE=A7, ACTUAL=A7, $
FIELD=CONDITION, ALIAS=E06, USAGE=A4, ACTUAL=A4, $
FIELD=DESC, ALIAS=E07, USAGE=A20, ACTUAL=A20, $
FIELD=SECT_NO, ALIAS=E08, USAGE=A8, ACTUAL=A8, $
FIELD=PIPE_NO, ALIAS=E09, USAGE=A8, ACTUAL=A8, $
FIELD=CAST_NO, ALIAS=E10, USAGE=A8, ACTUAL=A8, $
FIELD=COIL, ALIAS=E11, USAGE=I5, ACTUAL=I5, $
FIELD=SLAB, ALIAS=E12, USAGE=A15, ACTUAL=A15, $
FIELD=QUAL, ALIAS=E13, USAGE=A3, ACTUAL=A3, $
FIELD=BLOCK, ALIAS=E14, USAGE=I7, ACTUAL=I7, $
FIELD=SUPPLIER_COIL, ALIAS=E15, USAGE=A12, ACTUAL=A12, $


The generated foc master file:
FILENAME=HQATTRIBFOC, SUFFIX=FOC , $
SEGMENT=SEG01, SEGTYPE=S1, $
FIELDNAME=FOCLIST , ALIAS=E01, USAGE=I5, $
FIELDNAME=DATE, ALIAS=E02, USAGE=DMY, $
FIELDNAME=RPOS, ALIAS=E03, USAGE=A4, $
FIELDNAME=SIZE, ALIAS=E04, USAGE=A15, $
FIELDNAME=THICK, ALIAS=E05, USAGE=D7.2, $
FIELDNAME=GRADE, ALIAS=E06, USAGE=A7, $
FIELDNAME=CONDITION, ALIAS=E07, USAGE=A4, $
FIELDNAME=DESC, ALIAS=E08, USAGE=A20, $
FIELDNAME=SECT_NO, ALIAS=E09, USAGE=A8, $
FIELDNAME=PIPE_NO, ALIAS=E10, USAGE=A8, $
FIELDNAME=CAST_NO, ALIAS=E11, USAGE=A8, $
FIELDNAME=COIL, ALIAS=E12, USAGE=I5, $
FIELDNAME=SLAB, ALIAS=E13, USAGE=A15, $
FIELDNAME=QUAL, ALIAS=E14, USAGE=A3, $
FIELDNAME=BLOCK, ALIAS=E15, USAGE=I7, $
FIELDNAME=SUPPLIER_COIL, ALIAS=E16, USAGE=A12, $

The report html happily shows the date to allow the selection of from and to date. Under hotfix 8 no data is returned (even when same from and to are used) . Under hotfix 5 report works fine. Actual date in txt file is format dd/mm/yy

fex is doing a simple
WHERE DATE GE &D1;
WHERE DATE LE &D2;


Help!

This message has been edited. Last edited by: Darryl_uk,

Try putting quotes around your date variables in the WHERE clauses. Otherwise WF may think they are numbers instead of dates.

WHERE DATE GE '&D1';
WHERE DATE LE '&D2';

What is the format of &D1 and &D2 ?

Dan - thanks, that makes sense. So easy I didn't spot it. The report should never have worked since it was written (6 years ago)..!

Waz - A good question that I have never got my head around. the data arrives in the txt file as '99/99/99', is defined as 'dmy', is loaded into the foc as 'dmy', is picked up and displayed on the html screen as 99/99/99 so arrives from the html into the fex as..? I go put it into debug and look see.

Many thanks gents - I can't get hotfix 8 back up until tomorrow when the chap who does such things is back in work. But once done I'll update and close.

The date that is displayed as 13/03/08 on the screen comes into the program as:
WHERE DATE GE 130308;
so yes I can see where the lack of quotes may cause a direct numeric comparison rather than a date comparison. Quotes now added in and will see what happens tomorrow under service pack 8.

Thanks again

I suspect that in HF8 you'll find that the date is returned as '13/03/08' instead.

Somewhere around HF5 or 6 the date from date-fields accidentally got its delimiters stripped when passed on to the procedure (which, I must add, was actually rather convenient for us as our dates are stored as integers).

Wep, Waz & Dan,

All correct and working as per your info. With Hotfix 8 the slashes stay in the date passed back in by the html so the quotes around the parameter fixed the where statement.

Many thanks