Link to home
Start Free TrialLog in
Avatar of woodwyn
woodwynFlag for United States of America

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.  

 User generated image
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(dtOrdered)) AS Ordered, ;
      IIF(YEAR(dtShip)=1900,'',DTOC(dtShip))AS Ship, ;
      IIF(YEAR(ShowEndDate)=1900,'',DTOC(ShowEndDate)) AS ShowEnd, ;
      IIF(YEAR(dtSoftClosed)=1900,'',DTOC(dtSoftClosed)) AS SoftClosed, ;
      IIF(YEAR(dtHardClosed)=1900,'',DTOC(dtHardClosed)) AS HardClosed, ;
      IIF(YEAR(dtFirstInv)=1900,'',DTOC(dtFirstInv)) AS FirstInv, ;
      ProductionLeadTime, ReturnTime, ProjectCompletedTime, NotSoftClosed, AccountingReviewTime, NotClosed, ;
      InvoicingTime AS Invoicing, OverallInvTime, NotInvoiced, TotalJobLife AS JobLife ;
FROM SQLResult INTO TABLE (lcDIR_temp + "tLeadTimes")
ASKER CERTIFIED SOLUTION
Avatar of Olaf Doschke
Olaf Doschke
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of woodwyn

ASKER

Hello Olaf.  It's been a while.  Your suggestion works perfectly.  

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?
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.