Query records with same ID Number (1 Viewer)

JPR

Registered User
Joined
Jan 23, 2009
Messages
48
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

Super Moderator
Staff member
Joined
Jan 23, 2006
Messages
12,592
Try a query
Code:
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

Registered User
Joined
Jan 23, 2009
Messages
48
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

error reading drive A:
Joined
May 7, 2009
Messages
8,639
you don't need the Left([ssn], 9) there, if you modify your criteria as:

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

Pat Hartman

Super Moderator
Staff member
Joined
Feb 19, 2002
Messages
28,546
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

Registered User
Joined
Jan 23, 2009
Messages
48
Thank you for your advice.
Don't worry. No violation.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom