Finding similar records?

[Paul Fenton]

We have a table of Subjects with their Social Security #'s as the
identifier. Some of the SSAN's are identical to others but
differentiated by a 1 letter suffix. Example:

111111111
111111111A
111111111B
etc.

In the course of entering new subjects, we need to check to see if the
SSAN entered has any matches in the existing subjects. The user has
no way of knowing if it's there in any form at all... all he has is a
9 digit SSAN.

I've been trying to design a query that gets the SSAN from the entry
form, then finds all instances of that number, including those with a
suffix.

If I set the criteria on a query form to <Like "000000000" & "*">, I
get all the records. But I can't seem to be able to pass that string
to the query form using .Parameters or by using a function that
resolves to <Like "000000000" & "*">

How can I find the records that I need?


Paul Fenton
PLG & Associates.

 

[Guest]

Hi Paul,

If the SSN is always located on the left portion of the string, you may want
to make a new calculated field extracting the left 9 characters, then
reference your form field containing the SSN in the criteria for that row.
So the row source would be something like:

SSN Extract: Left([YourTableSSNField],9)

Where you would use the appropriate table field name. Then for the
criteria, just enter:

Forms![YourFormName]![YourSSNControlName]

This query could be used as the rowsource for an unbound list box or combo
box, or for a new form.

One other thing that may work, is to add a subform. In that case, the
record source of the subform would be the same query without the criteria
(you could also add any other fields that you wanted to display as well).
Then you could link the subform to the form by the SSN field, so the matches
would automatically display.

HTH, Ted Allen

 

[Paul Fenton]

Ted, that's an excellent idea that never occurred to me. Thank you so
much!

Paul Fenton

Hi Paul,

If the SSN is always located on the left portion of the string, you may want
to make a new calculated field extracting the left 9 characters, then
reference your form field containing the SSN in the criteria for that row.
So the row source would be something like:

SSN Extract: Left([YourTableSSNField],9)

Where you would use the appropriate table field name. Then for the
criteria, just enter:

Forms![YourFormName]![YourSSNControlName]

This query could be used as the rowsource for an unbound list box or combo
box, or for a new form.

One other thing that may work, is to add a subform. In that case, the
record source of the subform would be the same query without the criteria
(you could also add any other fields that you wanted to display as well).
Then you could link the subform to the form by the SSN field, so the matches
would automatically display.

HTH, Ted Allen

We have a table of Subjects with their Social Security #'s as the
identifier. Some of the SSAN's are identical to others but
differentiated by a 1 letter suffix. Example:

111111111
111111111A
111111111B
etc.

In the course of entering new subjects, we need to check to see if the
SSAN entered has any matches in the existing subjects. The user has
no way of knowing if it's there in any form at all... all he has is a
9 digit SSAN.

I've been trying to design a query that gets the SSAN from the entry
form, then finds all instances of that number, including those with a
suffix.

If I set the criteria on a query form to <Like "000000000" & "*">, I
get all the records. But I can't seem to be able to pass that string
to the query form using .Parameters or by using a function that
resolves to <Like "000000000" & "*">

How can I find the records that I need?


Paul Fenton
PLG & Associates.

 

[Guest]

My pleasure.

-Ted Allen

Ted, that's an excellent idea that never occurred to me. Thank you so
much!

Paul Fenton

Hi Paul,

If the SSN is always located on the left portion of the string, you may want
to make a new calculated field extracting the left 9 characters, then
reference your form field containing the SSN in the criteria for that row.
So the row source would be something like:

SSN Extract: Left([YourTableSSNField],9)

Where you would use the appropriate table field name. Then for the
criteria, just enter:

Forms![YourFormName]![YourSSNControlName]

This query could be used as the rowsource for an unbound list box or combo
box, or for a new form.

One other thing that may work, is to add a subform. In that case, the
record source of the subform would be the same query without the criteria
(you could also add any other fields that you wanted to display as well).
Then you could link the subform to the form by the SSN field, so the matches
would automatically display.

HTH, Ted Allen

We have a table of Subjects with their Social Security #'s as the
identifier. Some of the SSAN's are identical to others but
differentiated by a 1 letter suffix. Example:

111111111
111111111A
111111111B
etc.

In the course of entering new subjects, we need to check to see if the
SSAN entered has any matches in the existing subjects. The user has
no way of knowing if it's there in any form at all... all he has is a
9 digit SSAN.

I've been trying to design a query that gets the SSAN from the entry
form, then finds all instances of that number, including those with a
suffix.

If I set the criteria on a query form to <Like "000000000" & "*">, I
get all the records. But I can't seem to be able to pass that string
to the query form using .Parameters or by using a function that
resolves to <Like "000000000" & "*">

How can I find the records that I need?


Paul Fenton
PLG & Associates.