woodwyn
asked on
VFP query displaying 0 or 1 instead of empty string where year is 1900
When using the following select statement empty strings are desired in columns where YEAR(dateFields) = 1900. This works fine if the first record in a dateField column does not contain 1900, meaning we get dates where desired and empty strings where year = 1900. If the first record does contain 1900 the entire column for all records is populated with either an empty string or a 0 or a 1. In the screenshot you can see that, as desired, column FirstInv starts with a valid date and displays an empty string when YEAR(FirstInv) = 1900. The issue is demonstrated in the columns Ship and SoftClose, both having first records with YEAR(Ship) and Year(SoftClose) = 1900. The entire columns results are either empty strings, 0 or 1.
SELECT ALLTRIM(ChildJobNum) AS JobNum, ALLTRIM(JobDesc) AS JobDesc, ;
ALLTRIM(AccountManager) AS AcctMan, ;
ALLTRIM(ProjectManager) AS ProjMan, ;
ALLTRIM(DisplayType) AS DisplayType, ;
ALLTRIM(BoothType) AS BoothType, ;
IIF(YEAR(dtOrdered)=1900,' ',DTOC(dtO rdered)) AS Ordered, ;
IIF(YEAR(dtShip)=1900,'',D TOC(dtShip ))AS Ship, ;
IIF(YEAR(ShowEndDate)=1900 ,'',DTOC(S howEndDate )) AS ShowEnd, ;
IIF(YEAR(dtSoftClosed)=190 0,'',DTOC( dtSoftClos ed)) AS SoftClosed, ;
IIF(YEAR(dtHardClosed)=190 0,'',DTOC( dtHardClos ed)) AS HardClosed, ;
IIF(YEAR(dtFirstInv)=1900, '',DTOC(dt FirstInv)) AS FirstInv, ;
ProductionLeadTime, ReturnTime, ProjectCompletedTime, NotSoftClosed, AccountingReviewTime, NotClosed, ;
InvoicingTime AS Invoicing, OverallInvTime, NotInvoiced, TotalJobLife AS JobLife ;
FROM SQLResult INTO TABLE (lcDIR_temp + "tLeadTimes")
SELECT ALLTRIM(ChildJobNum) AS JobNum, ALLTRIM(JobDesc) AS JobDesc, ;
ALLTRIM(AccountManager) AS AcctMan, ;
ALLTRIM(ProjectManager) AS ProjMan, ;
ALLTRIM(DisplayType) AS DisplayType, ;
ALLTRIM(BoothType) AS BoothType, ;
IIF(YEAR(dtOrdered)=1900,'
IIF(YEAR(dtShip)=1900,'',D
IIF(YEAR(ShowEndDate)=1900
IIF(YEAR(dtSoftClosed)=190
IIF(YEAR(dtHardClosed)=190
IIF(YEAR(dtFirstInv)=1900,
ProductionLeadTime, ReturnTime, ProjectCompletedTime, NotSoftClosed, AccountingReviewTime, NotClosed, ;
InvoicingTime AS Invoicing, OverallInvTime, NotInvoiced, TotalJobLife AS JobLife ;
FROM SQLResult INTO TABLE (lcDIR_temp + "tLeadTimes")
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Well, yes, NULLDISPLAY will not change the display of the dates in Excel, but you should want to export the date type instead of strings, if you want to be able to continue working on dates in Excel. Even just sorting by dates gets wrong sorting, if a date arrives as string.
If your export arrives as dates in excel, then that's okay, but it may depend on english locale. The best way to get a date column in excel is to export a date.
Bye, Olaf.
If your export arrives as dates in excel, then that's okay, but it may depend on english locale. The best way to get a date column in excel is to export a date.
Bye, Olaf.
ASKER
This result set is exported to Excel. Does your suggestion - I would rather recommend not to change from a date to char (DTOC), but use .NULL. instead of '' and just the field otherwise. Then SET NULLDISPLAY TO ''. - still apply?