Followup to Parameter Query with Input mask.

[Elaine]

I think this past post was basically referencing what I am trying to do, but
I don't really understand the solution.

I have a table that has a field with social security numbers in it. I am
trying to set it up so the users can query using the SSN without having to
input the (-). In other words I want them to be able to just type the
numbers. They are not inputting the information. I am simply trying to
build a query. I have tried using a parameter query, but if I leave out
the - it does not work.

Can someone tell me how to do that?

Thanks for your help.

Elaine Johnson

 

[John Spencer (MVP)]

Assuming that the SSN is stored as a string (which it should be) and the string
is separated by dashes then:
Field: SSN
Criteria: Format([Enter SSN Numbers Only],"###\-##\-####")

 

[Elaine]

John, I tried this and it still did not work. We have kind of a convulted
system at work. We have recently converted to a (national) web based data
base that is administered at our headquarters. But I have the option of
downloading part of the information into an Excel file. Because this system
is new, our queries and reports that we used to run are non-existant and the
"higher ups" are pretty slow at giving us what we want. So I am downloading
information into an Excel file, linking to it with Access and then writing
my own queries using Access.

This is all pretty new to me and I have only limited experience in Access,
but enough to write the queries that I want. You said the SSN should be
stored as a string. I have no idea whether it is or not - so that may be
what the problem is. Everything works exactly the way I want it to if I
include the (-) in the SSN. But I really wanted to make it easier for the
users.

Does any of this information give you any other ideas of what I could do? I
am using Access 2002.

Thanks,

Elaine

Assuming that the SSN is stored as a string (which it should be) and the string
is separated by dashes then:
Field: SSN
Criteria: Format([Enter SSN Numbers Only],"###\-##\-####")

I think this past post was basically referencing what I am trying to do, but
I don't really understand the solution.

I have a table that has a field with social security numbers in it. I am
trying to set it up so the users can query using the SSN without having to
input the (-). In other words I want them to be able to just type the
numbers. They are not inputting the information. I am simply trying to
build a query. I have tried using a parameter query, but if I leave out
the - it does not work.

Can someone tell me how to do that?

Thanks for your help.

Elaine Johnson

 

[John Spencer (MVP)]

Try declaring your parameters in your SQL statement; perhaps Access is confused
as the data type.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1; [Enter SSN Numbers Only]
Select the data type of the parameter in column 2 [Text]

I would suspect other problems, but you say that including the dashes works, so
Access seems to have interpreted the column as a text column. Beyond that I am stuck.

John, I tried this and it still did not work. We have kind of a convulted
system at work. We have recently converted to a (national) web based data
base that is administered at our headquarters. But I have the option of
downloading part of the information into an Excel file. Because this system
is new, our queries and reports that we used to run are non-existant and the
"higher ups" are pretty slow at giving us what we want. So I am downloading
information into an Excel file, linking to it with Access and then writing
my own queries using Access.

This is all pretty new to me and I have only limited experience in Access,
but enough to write the queries that I want. You said the SSN should be
stored as a string. I have no idea whether it is or not - so that may be
what the problem is. Everything works exactly the way I want it to if I
include the (-) in the SSN. But I really wanted to make it easier for the
users.

Does any of this information give you any other ideas of what I could do? I
am using Access 2002.

Thanks,

Elaine

Assuming that the SSN is stored as a string (which it should be) and the string
is separated by dashes then:
Field: SSN
Criteria: Format([Enter SSN Numbers Only],"###\-##\-####")

I think this past post was basically referencing what I am trying to do, but
I don't really understand the solution.

I have a table that has a field with social security numbers in it. I am
trying to set it up so the users can query using the SSN without having to
input the (-). In other words I want them to be able to just type the
numbers. They are not inputting the information. I am simply trying to
build a query. I have tried using a parameter query, but if I leave out
the - it does not work.

Can someone tell me how to do that?

Thanks for your help.

Elaine Johnson

 

[Annelie]

I just went through the same problem. Go to the table and take out the input
mask, if you have one. Save table and look at the social security numbers,
and you should see all the dashes that were typed. Highlight the social
security column and do find and replace. Find -, replace with nothing. Be
sure to set the match to part of the field. This will only work if the field
properties are set to text, which I assume that it is because of the dashes,
if the field is a numeric field, you would loose the zeros.
When you get rid of the dashes, you can work with input masks.
Annelie

Try declaring your parameters in your SQL statement; perhaps Access is confused
as the data type.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1; [Enter SSN Numbers Only]
Select the data type of the parameter in column 2 [Text]

I would suspect other problems, but you say that including the dashes works, so
Access seems to have interpreted the column as a text column. Beyond that I am stuck.

John, I tried this and it still did not work. We have kind of a convulted
system at work. We have recently converted to a (national) web based data
base that is administered at our headquarters. But I have the option of
downloading part of the information into an Excel file. Because this system
is new, our queries and reports that we used to run are non-existant and the
"higher ups" are pretty slow at giving us what we want. So I am downloading
information into an Excel file, linking to it with Access and then writing
my own queries using Access.

This is all pretty new to me and I have only limited experience in Access,
but enough to write the queries that I want. You said the SSN should be
stored as a string. I have no idea whether it is or not - so that may be
what the problem is. Everything works exactly the way I want it to if I
include the (-) in the SSN. But I really wanted to make it easier for the
users.

Does any of this information give you any other ideas of what I could do? I
am using Access 2002.

Thanks,

Elaine

Assuming that the SSN is stored as a string (which it should be) and

the
string

is separated by dashes then:
Field: SSN
Criteria: Format([Enter SSN Numbers Only],"###\-##\-####")

Elaine wrote:

I think this past post was basically referencing what I am trying to

do,
but

I don't really understand the solution.

I have a table that has a field with social security numbers in it.

I
am

trying to set it up so the users can query using the SSN without

having
to

input the (-). In other words I want them to be able to just type the
numbers. They are not inputting the information. I am simply trying to
build a query. I have tried using a parameter query, but if I leave out
the - it does not work.

Can someone tell me how to do that?

Thanks for your help.

Elaine Johnson