Query takes dashes out of SSN

[dp1981]

I know this is probably the dumbest thing to ask, but I am just not getting it. I'm tryin to run a Query, and I assume it's a SELECT query for Last Name, First Name, SSN. The tbl containing this information [tbl_Employees] automatically places the dashes in the SSN through an input mask. However for one type of report I will later need to generate, the SSN cannot contain the dashes.
What is the criteria for this?!? I am so lost! I know it's probably something so simple that I'm just overlooking it, but I'm clueless. Can someone help me out? Thanks so much!

 

[Banana]

It's not problem with query; but how your table is designed.

When building a Input Mask, wizard will ask you whether you want to save the input literally or with markups. It sounds like you opted for a literal value, so if you looked at your tables, you'll see that all dashes are not there anymore; they are only visible on the form.

If you want to keep the dash, you'll need to rebuild the input mask so it will store the markups with the values.

 

[dp1981]

It's not problem with query; but how your table is designed.

When building a Input Mask, wizard will ask you whether you want to save the input literally or with markups. It sounds like you opted for a literal value, so if you looked at your tables, you'll see that all dashes are not there anymore; they are only visible on the form.

If you want to keep the dash, you'll need to rebuild the input mask so it will store the markups with the values.

Weird. Actually, the dash is still in my original tbl. After I went into design view, I realized I incorrectly identified something that could be imperative to know. It was NOT an input mask, but rather a format that I have entered for the SSN field. The format I placed was "@@@-@@-@@@@" (obviously without the quotations). I imported the SSN's from a spreadsheet in Excel straight into the tbl_Employees. Is that part of the problem? And is there any way to keep the format, but make it disappear ONLY for this one particular query I need?

Thanks for the quick response!!!!!!

 

[Banana]

Hmmm, now you taught me something new. I never even knew format in table design existed!

Now, I've never had the opportunity to see how changing formatting/input mask would affect the data. But I would do this:

Copy a small set of records from table into a new table with same design layout. Go ahead and modify the formatting and see what happens, and figure if you can get it to display the values you want to see (literally or with markups). If you cannot modify without losing the desired markups, try to use an append query to copy a table to another table with correct input mask and without formatting and see if this works. Of course do all of that on a copy, not the production table!

HTH.

 

[JoeyY]

Not sure how you want your number to look, but you could try to replace [SSN] in your query with

Left([SSN],3) & Mid([SSN],5,2) & Right([SSN],4)

 

[KenHigg]

To get back to your original problem I assume you could parse it out then put it back with something like:

myNewSSN: mid([mySSN],1,3) & mid([mySSN],5,2) & mid([mySSN],9,4)

???

edit: Joey just did beat me to it

 

[dp1981]

Hmm, i think i'm not understanding the numbers placed in the Query, Joey (or Ken, for that matter, too!). I get the theory of splitting it up, and it sounds good...just confused a bit by the numbers. Would you mind explaining that a bit?

Banana, glad I could teach you something. That is DEFINITELY the first time that's happened in regards to Access! Lol

 

[Banana]

The functions all ask for parameters after the string which affect how they will handle the string.

So,

Left(MyString, [i]Position of character to start the new string[/i])

So, if we want to get 'world' out of "Hello, world!", we need to count the position from left, and "Hello, " (notice the space) requires 8 characters, so we enter 9:

Left("Hello, world!", 9)

will return "world!".

Right() works just like Left, except that you count from right, not from left, so in same sample, we only count 6:

Right("Hello, world!", 6)

will return "world!"

Mid() has one more parameter: start position and how many character to get out of a string, so to get just "world" without the "!", we can say:

Mid("Hello, world!", 9, 5)

The new string starts on 9th character and will be 5 character long, and therefore return "world".

For more information, look to help files. Nary, make it your friend.

 

[pbaldy]

You could also use the Replace function to replace the "-" with "", which might be simpler.

 

[JoeyY]

Actually, the Left function should start from the left and take as many characters you specify.

ex. Left("Hello, world!",5) should give "Hello"

Paul,

I like your suggestion better.

 

[Banana]

JoeyY,

Doh! Thanks for correcting me!

You'd think having worked with string function for so long, I'd know better, but noo, it goes right in one and out the other!

 

[ajetrumpet]

JoeyY,

Doh! Thanks for correcting me!

You'd think having worked with string function for so long, I'd know better, but noo, it goes right in one and out the other!

Right in one string and out the other?

 

[KenHigg]

I think I like Pauls suggestion... Didn't think of it - Thanks!

 

[dp1981]

you guys are silly! holy cow! :-D

thanks for all the suggestions! i knew it was something simple. i'm going to try paul's suggestion first (uh, nothing personal but it seems simpler...lol). if that doesn't work, you guys definitely explained the left-mid-right thing to me. i appreciate it, as always!!!

 

[pbaldy]

When you're as lazy as I am, you tend to look for the simplest solutions.

 

[dp1981]

HAHA! Funny Paul! I tried it. It worked. Problem solved. Boss can get off my arse now. You're a lifesaver! :-D

 

[Banana]

dp1981, mind explaining what in the world your boss was doing in your arse?!?



Glad to know it worked out for you.

 

[ajetrumpet]

dp1981, mind explaining what in the world your boss was doing in your arse?!?

He said ON his arse, not in it! Eeewww!

 

[Banana]

On, or In, sheesh!

Any sane human being wouldn't be on or in other's arse, no?

 

[ajetrumpet]

Not sure about that, I've lived in some pretty strange areas, with some pretty strange people. I would like to forget about that too, so don't remind me, EH?