Query records with same ID Number (1 Viewer)

[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] & "*"

 

[Pat Hartman]

All in all, it is far better to never mush multiple pieces of data in a single field. You don't save anything. All you do is add complexity. Best solution is to separate SSN and the trailing character into two fields. You can use a compound unique index if you need to. Indexes can contain up to 10 fields. You have to build compound indexes using the indexes dialog. You cannot build them using design view on the table.

And finally, SSN should NEVER, EVER be used as a primary key. There are privacy lasw that prevent that. Hope you are not violating them.

 

[JPR]

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