SubForm and query criteria

[JPR]

Hello friends,

I am having some problems with a sub form which does not display data as I wish.
My db refers to a small insurance company. Clients are identified with a number (similar to the SSN but it is not a SSN) ending with either one or two characters which indicate the type of clients (worker, spouse or child).

Once users type the insurance number in the search box, a list box will display all records that relate that number. By double clicking on a selected record, a form displays r data related to that record.
In this form, I would like to add a sub form which displays data for all the other clients except the one selected in the list box. I have been able to do this but cannot understand how to display also the last or last two characters which identify the client.

Example:

Records selected 000-00-0000W
The main form will display data related to 000-00-0000W
The sub form should display data related to 000-00-0000S and 000-00-0000C.

I am attaching a simple db file which hopefully makes it more clear where I am doing wrong.
Thank you for any help.

 

[June7]

LIKE operator isn't useful without wildcard character. Might as well just be = sign.

Like [forms]![frmMenu]![txtsearch] & "*"

Include field with full INSNO value in query and display that field.

 

[JPR]

Great. Got it working. Is there now a way to exclude from the sub the record the main records, meaning the one selected in the list box? Basically the sub form should display all other records. Thank you

 

[isladogs]

Yes - add the word Not to your query!
NOTE: If using a wildcard you can filter on the entire INSNO field

SELECT clients.INSNO, clients.FNAME, clients.LNAME
FROM clients
WHERE (((clients.INSNO) Not Like [forms]![frmMenu]![txtsearch] & "*"));

or omit the wildcard again

SELECT clients.INSNO, clients.FNAME, clients.LNAME
FROM clients
WHERE (((clients.INSNO)<>[forms]![frmMenu]![txtsearch]));

 

[JPR]

Thank you. Unfortunately the Like Not will return in the Sub Form all the other records. What I need is restrict it to all other records, with the same insurance no but different ending characters (basically same first 9 digits but different last two characters). Thank you

 

[June7]

qrySubClients:

SELECT clients.INSNO, clients.FNAME, clients.LNAME
FROM clients
WHERE (((Left([INSNO],9))=[forms]![frmMenu]![txtsubclient]) AND ((clients.INSNO)<>[Forms]![frmMenu]![txtclientsearch]));

 

[JPR]

Perfect. Exactly what I wanted. Thank you

 

[arnelgp]

for you to study.