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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Olaf DoschkeSoftware 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FoxPro

From novice to tech pro — start learning today.