Creating a date range prompt

[cfrench]

Should be simple enough I thought...

I will provide the full scenario because I'm not sure if a solution would be simpler on the database side or the Webfocus side.

Data was exported out of a PIC database into .csv files which were pulled into SQL Server 7 via DTS. There is an ODBC connection to the Webfocus Server (the only way our local support people knew how to setup).

All the fields were set as varchar 255. I am a novice in means of database development but changed the field properties in design table mode making dates 'datetime' and dollar figures as 'money' leaving the rest varchar but altering field sizes.

The dates weren't viewable until I changed the .mas file Actual as A20 and I put the Usage as MDYY.

However, I cannot do any where/if statements on the date fields for any variation.

0 ERROR AT OR NEAR LINE 11 IN PROCEDURE MEMFEX FOCEXEC *
(FOC177) INVALID DATE CONSTANT: )
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0



I also receive some errors on sum fields:

(FOC1400) SQLCODE IS 409 (HEX: 00000199)
: [22005] [Microsoft][ODBC SQL Server Driver][SQL Server]The sum or averag
: e aggregate operation cannot take a varchar data type as an argument. [3
: 7000] [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could
: not be prepared.
L (FOC1406) SQL OPEN CURSOR ERROR. : SALESANALYSIS

My intuition is that these issues could be alleviated more efficiently via altering the database, but the support staff are trying to create a webfocus workaround. I'll either at this point!

I would really appreciate if someone could provide with some suggestions to alleviate my issues.

Thank you in advance.

 

[FrancieFL]

You may want to try and set the usage to "A8MDYY" for your column, then create the below define.
Define MyDate/MDYY = InDate ;$

The way I do this is I give the master "FIELD" name a temporary name and the Define field is the one that is used on reports and in the WHERE.

Hope this helps.
Francie

 

[FrankExp]

You can change the field in the database to a date-format. After that you can tell FOCUS the field has: actual=DATE and usage=MDYY.
Then you can write table-statements with the correct where-clauses. You even can add or substract a number of days to this date-field by using :

COMPUTE
  DAYPLUS10/MDYY = DATEFIELD + 10;

Focus handles the translations from statements in your code to the values in the DBMS and can compare these. Frank van de Kant

http://www.explicit.nl

 

[cfrench]

Got it. I re-imported the data into the database,, transforming the data in the process to represent datetime. Focus then recognized it as a date.

Thanks everyone for your responses.