Subject Re: Combobox field list using DISTINCT
From Mervyn Bick <invalid@invalid.invalid>
Date Thu, 28 Apr 2016 15:37:54 +0200
Newsgroups dbase.getting-started

On 28/04/2016 14:52, edward racht wrote:
> Thank You.
>
> I have a challenge figuring out how to get the following code to work.
>
> By work I mean that it runs but doesn't remove all rows that do not have
>
> "md LIKE 'Code' OR CE <> ''"
>
> Something confuses the program when OR CE <> '' is added.
>
> This is another nuance with my report.
>
> "rs" is a table
> 'CAP' is a field
> 'md' is a filed
> 'Code' is a variation in the field 'md'
>
>
> "select distinct CAP from rs WHERE md LIKE 'Code' OR CE <> ''"

The LIKE predicate in a WHERE clause is used in SQL to match patterns in
the field much as you would use the $ comparison operator in dBASE.

When constructing the search pattern  % represents any number of
characters and _ represents a single character.  If, as an example, you
wanted to select records where the field could contain say bit-code,
byte-code or UNIcode  you would use  ...WHERE fieldname LIKE '%code'.

In your case if you only want records with 'Code' in the field instead
of LIKE use the equal sign.  (The LIKE should give you the same results
but you should only use it where you need to find a pattern.)

   "select distinct CAP from rs where md = 'Code' or ce <> ''"

This will get you ALL rows where CE is not blank no matter what is in MD.

If you only want records where md contains 'Code' and you don't want
records with blank ce fields then you need to AND the two.

   "select distinct CAP from rs where md = 'Code' and ce <> ''"

Mervyn.