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.  

 Results.png
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")
woodwynAsked:
Who is Participating?
 
Olaf DoschkeConnect With a Mentor Software DeveloperCommented:
VFP determines the results column types and widths from the first result record.So you better define SPACE(10) instead of an empty string, you end up with Char(10) fields sometimes and Char(1) at other times.

For computed fields always use expressions, which result in the same type and width no matter what case, this is not only true for IIF and ICASE, this also means avoiding ALLTRIM, but if you need it PADR or PADL the result to a width large enough to hold all types of results.

So here just replace all '' with SPACE(10) or more and you should be fine.

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

Bye, Olaf.
0
 
woodwynAuthor Commented:
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?
0
 
Olaf DoschkeSoftware DeveloperCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.