| Subject | Re: Like query |
| From | Tom <IHaveNoEmail@ddress> |
| Date | Wed, 25 May 2022 11:35:35 -0400 |
| Newsgroups | dbase.getting-started |
| Attachment(s) | NewInvoiceEntries.png, CastDateEntry.png, CodeModification.png |
On 5/25/22 5:45 AM, Mervyn Bick wrote: > On 2022/05/24 20:23, Tom wrote: >> dBase 13.1 >> Windows 10 Pro >> Version 21H2 >> >> Having stolen one of Mervyn's sample forms, I have changed databases, >> tables and fields for a trial. The field name contains a space but >> otherwise is a character field. Using single quotes, double quotes and >> brackets around the field name still fails to 'find' any entries I >> type. Ultimately, I would like to use 'like' with the date field in >> the table too. >> >> Would you be so kind as to show me how to get this form to work. A >> copy of the sample form and a screenshot of the table design are >> included. > See NewInvoiceEntries image for additional table entries I made sometime in the past. > I'm afraid the problem is due to the limitations of localSQL. In > "proper" SQL, such as Firebird, [select * from invoice where lower("CARD > NAME") like :search] works as expected. Note double quotes, not single > quotes. > > Outside of a SELECT statement localSQL can deal with spaces in > fieldnames by wrapping the fieldname in single quotes and using the > table name (or a correlation name i.e alias) e.g invoice.'card name' > Figures, about the only thing I did not try (did not remember). > The LIKE predicate only works with character fields. To get it to work > with a date you need to convert the field to characters. The cast() > function in SQL will do this but once again localSQL has a problem with > the space in a fieldname in a SELECT statement. > See CastDateEntry image for dBase's exception to your mm/dd/yyyy statement. Explains why I could not find my '04' date entries. > The solution is to add two new columns temporarily, populate them and > then remove the two fields when the form closes. The two fields are not > visible when the form opens but you can scroll across in the grid to > view them. > > A revised version of your form is attached. See CodeModification image. Search works much better with this small correction. More coffee? > > Mervyn. > As always, your code dealing with exceptions to the norm situations is outstanding. Certainly helpful to me and hopefully others. Regards, Tom | |