Query records with same ID Number

[JPR]

Hello,

I have a table with a 9 digit SSN field. This number is followed by one or two additional characters (letters or numbers).

It may happen that same records have the same 9 digits and will need to create a query that returns all those records with just the same first 9 digits. Query source will be a textbox on a form.

Example:

Records:

000000000A
000000000B
000000000C
111111111A
123456789A

If in the textbox, the criteria is 000000000, then the query should return:

000000000A
000000000B
000000000C

Is there a way, I can achieve this?

Thank you for any help.

 

[jdraw]

Try a query

SELECT Table9SSN.MSSN
FROM Table9SSN
WHERE (((Left([MSSN],9))="000000000"))
GROUP BY Table9SSN.MSSN;

make sure this is the result you need,
then work with this syntax and your form's text box.

 

[JPR]

Resolved. Just changed a few lines and got what I was looking for. Will share. Thank you


SELECT mytable.[ssn], First(Left([ssn],9)) AS Expr1
FROM mytable
GROUP BY mytable.[ssn]
HAVING (((First(Left([ssn],9))) Like [forms]![myform]![ssnsearch]));

 

[arnelgp]

you don't need the Left([ssn], 9) there, if you modify your criteria as:

Like [Forms]![myForm]![ssnsearch] & "*"

 

[JPR]

Thank you for your advice.
Don't worry. No violation.