SQL - Select part of data from table into List or Listview box 3

[konLao]

Hello all,

I have table TblCustomer with Name field. Data can be..

John C. Doe
John D. Doe
John E. Doe
...

I need to select records that have "John" for first name and "Doe" for Last name. I would be the same when I use Browse function.

Browse for 'John' $ name .and. 'Doe' $ name

But I want to use 'SQL - Select into cursor' so I can populate the list box.

Any suggestion?

Thank you,

KonLao

 

[jimstarr]

Hi KonLao,

You can use the same $ operator in a WHERE clause:

SELECT * FROM TblCustomer INTO CURSOR myCursor ;
WHERE 'John' $ name AND 'Doe' $ name


Jim

 

[konLao]

Thanks a bunch Jim

 

[StewartUK]

Hi KonLao,

I agree with Jim's reply. My comment would be that if you don't have spaces within the quotes you may well get back results wher the string appears within a longer name.

I think you will also find that using 0<AT() will work faster than $.

AT() returns a numeric stating where in the field name the string 'Doe' was found. So saying 0<AT('Doe',name) is the same as saying 'Doe'$name.

There is also ATC() which works the same except that it is case INsensitive.

Hope that helps as well,

Stewart

 

[jimoo]

If you are only loading the data into a list box, and there is not a ton of data, you may use an array.

SELECT * FROM TblCustomer INTO CURSOR myCursor ;
WHERE 'John' $ name AND 'Doe' $ name INTO ARRAY arListData

Also, if you want more control, or need to move add/remove list items - take a look at this faq.

Programmatically Loading & Referencing Listbox Items
faq184-4322


Finally, if you choose the array method. I usually create a property on my form to hold the array. Under Form / Menu create a propery and give it a name like arListData[1]

Notice the [1] at the end. That is how you declare a property as an array.

Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com

 

[rgbean]

KonLoa,
A more SQL like solution would be:

SELECT * FROM TblCustomer INTO CURSOR myCursor ;
WHERE name LIKE "John%Doe"

Rick