Subject Re: Query error
From Mervyn Bick <invalid@invalid.invalid>
Date Tue, 5 Sep 2023 12:03:18 +0200
Newsgroups dbase.getting-started

On 2023/09/04 18:38, Tom wrote:

> Hi all,
>
> Mervyn has solved the puzzle. I will let him explain how he went about
> it as I am still trying to digest exactly what he did.
>
> Thank you very much Mervyn !
>
> Regards,
>
> Tom
>

      l0 = "Select d.TestDate,  cast(d.Testdate as char(10)) castDate,"
      l0 += "d.Provider, d.Client,  d.Category, d.Allergen,"
      l0 += "d.TestLevel, d.StdTestRange, n.Demodata_Notes   "
      l0 += "FROM DemoData d "
      l0 += "Left Join DemoNotes n On n.Fk_DemoData = d.Pk_DemoData "
-->  l0 += "WHERE  cast(d.TestDate as char(10)) LIKE :TestDate AND   "
      l0 += "LOWER(d.Category) LIKE :Category AND   "
      l0 += "LOWER(d.Allergen) LIKE :Allergen AND   "
      l0 += "n.Demodata_Notes LIKE :Notes   "
      l0 += "ORDER BY TestDate, Category, Allergen "

dBASE doesn't recognise the calculated field containing the date in
character form in the WHERE clause.  It is, therefore, necessary to
recalculate the date in character format for the WHERE clause.

No matter what date display format is used, e.g American, British or
MDY, ordering on a date field (TestDate in this case) will order the
rowset correctly.

dBASE does recognise the calculated field in an ORDER BY clause but
CAST(d.TestDate as char(10)) will always produce strings in the form
mm/dd/ccyy no matter what format is used to display the date field on
screen.  It is, therefore, not possible to use the calculated field to
order the rowset as it will not be in chronological order.

If the date display format is AMERICAN it wouldn't matter if either the
CAST value or the date value is displayed.  It is, however, essential to
include the date field so that the rowset can ordered correctly.

If the date display format is not AMERICAN and it required that the user
enter a date search string in the display format rather than in AMERICAN
format then a bit of programming will be needed to reformat the search
string.

Mervyn.