Difficult rowsource

[Guest]

Hi

I have a form with 4 unbound textboxes in it (defaults are: "Enter forename", "Enter surname", etc). There is also list box showing all data from a table

What I want is for the user to be able to enter in forename or surname - whatever info they have (not necessary to enter all 4 txtboxes)

I then want the list box to filter for only the matching records (eg if they only enter "John" in the forename txtbox, it will show all people from the table with forename "John". If they also enter surname "Smith", it only shows the records from the table with "John Smith" forename/surname)

I have set it up so that the sql row source of the list box references the txtboxes on the form, but I don't understand how to make it variable, so that if nothing is entered in the textbox, no criteria is applied. Could somebody help me with the criteria code/sql code

Many thanks

Basil

 

[John Viescas]

The general technique is to use:

SELECT *
FROM MyTable
WHERE
((MyTable.forename LIKE [Forms]![MyForm][txtforename] & "*") OR
([Forms]![MyForm][txtforename] IS NULL))
AND
((MyTable.surname LIKE [Forms]![MyForm][txtsurname] & "*") OR
([Forms]![MyForm][txtsurname] IS NULL))
AND ...

However, it is easy to get "query too complex" when you add the third,
fourth, or fifth criteria like this because Access "normalizes" the WHERE
clause into a series of ORed ANDs (instead of ANDed ORs).

If you run into that problem, the only solution is to build the SQL on the
fly in code - ignoring Null or empty tests altogether - and set the
RowSource property of the list box with the correct SQL.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

Hi,

I have a form with 4 unbound textboxes in it (defaults are: "Enter

forename", "Enter surname", etc). There is also list box showing all data
from a table.

What I want is for the user to be able to enter in forename or surname -

whatever info they have (not necessary to enter all 4 txtboxes).

I then want the list box to filter for only the matching records (eg if

they only enter "John" in the forename txtbox, it will show all people from
the table with forename "John". If they also enter surname "Smith", it only
shows the records from the table with "John Smith" forename/surname).

I have set it up so that the sql row source of the list box references the

txtboxes on the form, but I don't understand how to make it variable, so
that if nothing is entered in the textbox, no criteria is applied. Could
somebody help me with the criteria code/sql code?