Input mask problem

[Guest]

Initally when entering the Social Security number it was entered using
dashes(xxx-xx-xxxx) The field was later changed to using a the social
security number input mask. The problem is this: When using a parameter
query to do a SS# look up.. Only records where dashes were entered are found
or if the search parameter thru a form has the SS# input mask only those
records that were later entered using the SS# Input mask are found. How can
I change early records to be like the later records without going in and
retyping the SS#? Is there some other work arround?

 

[Douglas J. Steele]

I'm not 100% clear what you're asking for: do you want to remove the dashes
from the ones that were entered with them? If so, try an Update query along
the lines of

UPDATE MyTable SET SSN = Replace([SSN], "-", "")

To accomplish this through the GUI, create a query on MyTable, convert it to
an Update query (through the Query menu), and put Replace([SSN], "-", "") in
the Update To line under SSN. (of course, replace MyTable and SSN with
whatever the actual names are!)

 

[Van T. Dinh]

I think the problem is that you have set the InputMask but selected not to
store the InputMask characters with the data. Hence, in your Table, there
are SSN with "-" in the data (earlier Records) and some without "-" (later
Records).

Suggest you change the InputMask string so that the InputMask characters are
stored with the data entered. For SSNs that have been stored without "-",
do an Update Query to change the SSNs to include "-" in the data.

Check Access Help on the 3 different components of the InpuMask string.

 

[Guest]

When displayed all SS# look alike with the dashes in place. Is it still
possible that the latter numbers do not have the "-" but only show them when
displayed?

 

[fredg]

When displayed all SS# look alike with the dashes in place. Is it still
possible that the latter numbers do not have the "-" but only show them when
displayed?

Your mask (000\-00\-0000;;_) was not set up to be stored with the
data, therefore the field contains previous entries with the hyphen,
and later entries without the hyphen. What you are seeing in the
control display is the mask itself, not the actual stored data.
Determine for yourself what it is you want to store. Then create an
update query to change the stored data to either with hyphens or
without. Then change the mask to be stored with the data (if you chose
to store hyphens) or leave it as is (if you chose to remove the
hyphens from the existing data).

Run an Update query.
To change all data to include hyphens:
Update YourTable Set YourTable.[SSN] =
IIf(InStr([SSN],"-")>0,[SSN],Format([SSN],"@@@-@@-@@@@");

Then change the mask to store future entries with the mask.
Place a 0 between the 2 right side mask semi-colons, i.e.
000\-00\-0000;0;_

To change all stored data to not include hyphens:
Update YourTable Set YourTable.[SSN] = Replace([SSN],"-","");

I would suggest you store the mask with the data, as then you will not
need to re-format the data again when you display it in a Report.

But it is your decision.

 

[Guest]

Using this expression in the Update to field:
[SOCIAL SECURITY #]=IF(InStr[SOCIAL SECURITY #],"-") >0, [SOCIAL SECURITY
#],Format([SOCIAL SECURITY #],"@@@-@@-@@@@");
I get a invalid syntack error. Also I did a report on the data and all SS#
have hyphens.

When displayed all SS# look alike with the dashes in place. Is it still
possible that the latter numbers do not have the "-" but only show them when
displayed?

Your mask (000\-00\-0000;;_) was not set up to be stored with the
data, therefore the field contains previous entries with the hyphen,
and later entries without the hyphen. What you are seeing in the
control display is the mask itself, not the actual stored data.
Determine for yourself what it is you want to store. Then create an
update query to change the stored data to either with hyphens or
without. Then change the mask to be stored with the data (if you chose
to store hyphens) or leave it as is (if you chose to remove the
hyphens from the existing data).

Run an Update query.
To change all data to include hyphens:
Update YourTable Set YourTable.[SSN] =
IIf(InStr([SSN],"-")>0,[SSN],Format([SSN],"@@@-@@-@@@@");

Then change the mask to store future entries with the mask.
Place a 0 between the 2 right side mask semi-colons, i.e.
000\-00\-0000;0;_

To change all stored data to not include hyphens:
Update YourTable Set YourTable.[SSN] = Replace([SSN],"-","");

I would suggest you store the mask with the data, as then you will not
need to re-format the data again when you display it in a Report.

But it is your decision.

 

[fredg]

Using this expression in the Update to field:
[SOCIAL SECURITY #]=IF(InStr[SOCIAL SECURITY #],"-") >0, [SOCIAL SECURITY
#],Format([SOCIAL SECURITY #],"@@@-@@-@@@@");
I get a invalid syntack error. Also I did a report on the data and all SS#
have hyphens.

:

** snipped **

You need to use the Immediate If function IIf not If.
Also, you're missing an Opening Parenthesis in your InStr() function.

Update ... etc....
[SOCIAL SECURITY #]=IIF(InStr([SOCIAL SECURITY #],"-") >0, [SOCIAL
SECURITY #], Format([SOCIAL SECURITY #],"@@@-@@-@@@@");

 

[Guest]

THIS IS HOW MY STRING LOOKS NOW STILL GETS ERROR. IT'S MISSING A CLOSING )
SOMEWHERE
[SOCIAL SECURITY #]=IIF(InStr([SOCIAL SECURITY #],"-") >0, [SOCIAL SECURITY
#],FORMAT([SOCIAL SECURITY #],"@@@-@@-@@@@");

Using this expression in the Update to field:
[SOCIAL SECURITY #]=IF(InStr[SOCIAL SECURITY #],"-") >0, [SOCIAL SECURITY
#],Format([SOCIAL SECURITY #],"@@@-@@-@@@@");
I get a invalid syntack error. Also I did a report on the data and all SS#
have hyphens.

:

** snipped **

You need to use the Immediate If function IIf not If.
Also, you're missing an Opening Parenthesis in your InStr() function.

Update ... etc....
[SOCIAL SECURITY #]=IIF(InStr([SOCIAL SECURITY #],"-") >0, [SOCIAL
SECURITY #], Format([SOCIAL SECURITY #],"@@@-@@-@@@@");

 

[fredg]

THIS IS HOW MY STRING LOOKS NOW STILL GETS ERROR. IT'S MISSING A CLOSING )
SOMEWHERE
[SOCIAL SECURITY #]=IIF(InStr([SOCIAL SECURITY #],"-") >0, [SOCIAL SECURITY
#],FORMAT([SOCIAL SECURITY #],"@@@-@@-@@@@");

Using this expression in the Update to field:
[SOCIAL SECURITY #]=IF(InStr[SOCIAL SECURITY #],"-") >0, [SOCIAL SECURITY
#],Format([SOCIAL SECURITY #],"@@@-@@-@@@@");
I get a invalid syntack error. Also I did a report on the data and all SS#
have hyphens.

:

** snipped **

You need to use the Immediate If function IIf not If.
Also, you're missing an Opening Parenthesis in your InStr() function.

Update ... etc....
[SOCIAL SECURITY #]=IIF(InStr([SOCIAL SECURITY #],"-") >0, [SOCIAL
SECURITY #], Format([SOCIAL SECURITY #],"@@@-@@-@@@@");

Yes. My error.
You need to add a ) just before the ;.
....Format([SOCIAL SECURITY #],"@@@-@@-@@@@"));

I usually count opening and closing parenthesis to make sure they are
balanced, but I forgot to do that here.

 

[Guest]

STILL NO GO. STRING LOOKS LIKE: I ASSUME ) SHOULD GO BEFORE COMMA

[SOCIAL SECURITY #] =IIF(INSTR([SOCIAL SECURITY #],"-")>0,[SOCIAL SECURITY
#) ,Format([SOCIAL SECURITY #],"@@@-@@-@@@@");

THIS IS HOW MY STRING LOOKS NOW STILL GETS ERROR. IT'S MISSING A CLOSING )
SOMEWHERE
[SOCIAL SECURITY #]=IIF(InStr([SOCIAL SECURITY #],"-") >0, [SOCIAL SECURITY
#],FORMAT([SOCIAL SECURITY #],"@@@-@@-@@@@");

On Sun, 8 Jan 2006 07:57:03 -0800, Bob B wrote:

Using this expression in the Update to field:
[SOCIAL SECURITY #]=IF(InStr[SOCIAL SECURITY #],"-") >0, [SOCIAL SECURITY
#],Format([SOCIAL SECURITY #],"@@@-@@-@@@@");
I get a invalid syntack error. Also I did a report on the data and all SS#
have hyphens.

:


** snipped **

You need to use the Immediate If function IIf not If.
Also, you're missing an Opening Parenthesis in your InStr() function.

Update ... etc....
[SOCIAL SECURITY #]=IIF(InStr([SOCIAL SECURITY #],"-") >0, [SOCIAL
SECURITY #], Format([SOCIAL SECURITY #],"@@@-@@-@@@@");

Yes. My error.
You need to add a ) just before the ;.
....Format([SOCIAL SECURITY #],"@@@-@@-@@@@"));

I usually count opening and closing parenthesis to make sure they are
balanced, but I forgot to do that here.

 

[Douglas J. Steele]

You're missing a closing parenthesis. It should be:

[SOCIAL SECURITY #] =IIF(INSTR([SOCIAL SECURITY #],"-")>0,[SOCIAL SECURITY
#) ,Format([SOCIAL SECURITY #],"@@@-@@-@@@@"))


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

STILL NO GO. STRING LOOKS LIKE: I ASSUME ) SHOULD GO BEFORE COMMA

[SOCIAL SECURITY #] =IIF(INSTR([SOCIAL SECURITY #],"-")>0,[SOCIAL SECURITY
#) ,Format([SOCIAL SECURITY #],"@@@-@@-@@@@");

THIS IS HOW MY STRING LOOKS NOW STILL GETS ERROR. IT'S MISSING A
CLOSING )
SOMEWHERE
[SOCIAL SECURITY #]=IIF(InStr([SOCIAL SECURITY #],"-") >0, [SOCIAL
SECURITY
#],FORMAT([SOCIAL SECURITY #],"@@@-@@-@@@@");
:

On Sun, 8 Jan 2006 07:57:03 -0800, Bob B wrote:

Using this expression in the Update to field:
[SOCIAL SECURITY #]=IF(InStr[SOCIAL SECURITY #],"-") >0, [SOCIAL
SECURITY
#],Format([SOCIAL SECURITY #],"@@@-@@-@@@@");
I get a invalid syntack error. Also I did a report on the data and
all SS#
have hyphens.

:


** snipped **

You need to use the Immediate If function IIf not If.
Also, you're missing an Opening Parenthesis in your InStr() function.

Update ... etc....
[SOCIAL SECURITY #]=IIF(InStr([SOCIAL SECURITY #],"-") >0, [SOCIAL
SECURITY #], Format([SOCIAL SECURITY #],"@@@-@@-@@@@");

Yes. My error.
You need to add a ) just before the ;.
....Format([SOCIAL SECURITY #],"@@@-@@-@@@@"));

I usually count opening and closing parenthesis to make sure they are
balanced, but I forgot to do that here.

 

[fredg]

STILL NO GO. STRING LOOKS LIKE: I ASSUME ) SHOULD GO BEFORE COMMA

[SOCIAL SECURITY #] =IIF(INSTR([SOCIAL SECURITY #],"-")>0,[SOCIAL SECURITY
#) ,Format([SOCIAL SECURITY #],"@@@-@@-@@@@");

THIS IS HOW MY STRING LOOKS NOW STILL GETS ERROR. IT'S MISSING A CLOSING )
SOMEWHERE
[SOCIAL SECURITY #]=IIF(InStr([SOCIAL SECURITY #],"-") >0, [SOCIAL SECURITY
#],FORMAT([SOCIAL SECURITY #],"@@@-@@-@@@@");
:

On Sun, 8 Jan 2006 07:57:03 -0800, Bob B wrote:

Using this expression in the Update to field:
[SOCIAL SECURITY #]=IF(InStr[SOCIAL SECURITY #],"-") >0, [SOCIAL SECURITY
#],Format([SOCIAL SECURITY #],"@@@-@@-@@@@");
I get a invalid syntack error. Also I did a report on the data and all SS#
have hyphens.

:


** snipped **

You need to use the Immediate If function IIf not If.
Also, you're missing an Opening Parenthesis in your InStr() function.

Update ... etc....
[SOCIAL SECURITY #]=IIF(InStr([SOCIAL SECURITY #],"-") >0, [SOCIAL
SECURITY #], Format([SOCIAL SECURITY #],"@@@-@@-@@@@");

Yes. My error.
You need to add a ) just before the ;.
....Format([SOCIAL SECURITY #],"@@@-@@-@@@@"));

I usually count opening and closing parenthesis to make sure they are
balanced, but I forgot to do that here.

No! It goes where I said it goes, at the end of the expression:
..... "@@@-@@-@@@@"));

 

[Guest]

I don't think so, I count ((( and ))) your adding another ). The error is
invalid syntax you may have entered a operand with out a operator

You're missing a closing parenthesis. It should be:

[SOCIAL SECURITY #] =IIF(INSTR([SOCIAL SECURITY #],"-")>0,[SOCIAL SECURITY
#) ,Format([SOCIAL SECURITY #],"@@@-@@-@@@@"))


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

STILL NO GO. STRING LOOKS LIKE: I ASSUME ) SHOULD GO BEFORE COMMA

[SOCIAL SECURITY #] =IIF(INSTR([SOCIAL SECURITY #],"-")>0,[SOCIAL SECURITY
#) ,Format([SOCIAL SECURITY #],"@@@-@@-@@@@");

On Sun, 8 Jan 2006 10:53:02 -0800, Bob B wrote:

THIS IS HOW MY STRING LOOKS NOW STILL GETS ERROR. IT'S MISSING A
CLOSING )
SOMEWHERE
[SOCIAL SECURITY #]=IIF(InStr([SOCIAL SECURITY #],"-") >0, [SOCIAL
SECURITY
#],FORMAT([SOCIAL SECURITY #],"@@@-@@-@@@@");
:

On Sun, 8 Jan 2006 07:57:03 -0800, Bob B wrote:

Using this expression in the Update to field:
[SOCIAL SECURITY #]=IF(InStr[SOCIAL SECURITY #],"-") >0, [SOCIAL
SECURITY
#],Format([SOCIAL SECURITY #],"@@@-@@-@@@@");
I get a invalid syntack error. Also I did a report on the data and
all SS#
have hyphens.

:


** snipped **

You need to use the Immediate If function IIf not If.
Also, you're missing an Opening Parenthesis in your InStr() function.

Update ... etc....
[SOCIAL SECURITY #]=IIF(InStr([SOCIAL SECURITY #],"-") >0, [SOCIAL
SECURITY #], Format([SOCIAL SECURITY #],"@@@-@@-@@@@");
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail


Yes. My error.
You need to add a ) just before the ;.
....Format([SOCIAL SECURITY #],"@@@-@@-@@@@"));

I usually count opening and closing parenthesis to make sure they are
balanced, but I forgot to do that here.

 

[Douglas J. Steele]

You need the 2 parentheses at the end. I missed the fact that you've got a
closing parenthesis after the middle SOCIAL SECURITY # rather than a closing
square bracket. Corrected, it's:

[SOCIAL SECURITY #] =IIF(INSTR([SOCIAL SECURITY #],"-")>0,[SOCIAL SECURITY
#] ,Format([SOCIAL SECURITY #],"@@@-@@-@@@@"))

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I don't think so, I count ((( and ))) your adding another ). The error is
invalid syntax you may have entered a operand with out a operator

You're missing a closing parenthesis. It should be:

[SOCIAL SECURITY #] =IIF(INSTR([SOCIAL SECURITY #],"-")>0,[SOCIAL
SECURITY
#) ,Format([SOCIAL SECURITY #],"@@@-@@-@@@@"))


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

STILL NO GO. STRING LOOKS LIKE: I ASSUME ) SHOULD GO BEFORE COMMA

[SOCIAL SECURITY #] =IIF(INSTR([SOCIAL SECURITY #],"-")>0,[SOCIAL
SECURITY
#) ,Format([SOCIAL SECURITY #],"@@@-@@-@@@@");

:

On Sun, 8 Jan 2006 10:53:02 -0800, Bob B wrote:

THIS IS HOW MY STRING LOOKS NOW STILL GETS ERROR. IT'S MISSING A
CLOSING )
SOMEWHERE
[SOCIAL SECURITY #]=IIF(InStr([SOCIAL SECURITY #],"-") >0, [SOCIAL
SECURITY
#],FORMAT([SOCIAL SECURITY #],"@@@-@@-@@@@");
:

On Sun, 8 Jan 2006 07:57:03 -0800, Bob B wrote:

Using this expression in the Update to field:
[SOCIAL SECURITY #]=IF(InStr[SOCIAL SECURITY #],"-") >0, [SOCIAL
SECURITY
#],Format([SOCIAL SECURITY #],"@@@-@@-@@@@");
I get a invalid syntack error. Also I did a report on the data
and
all SS#
have hyphens.

:


** snipped **

You need to use the Immediate If function IIf not If.
Also, you're missing an Opening Parenthesis in your InStr()
function.

Update ... etc....
[SOCIAL SECURITY #]=IIF(InStr([SOCIAL SECURITY #],"-") >0, [SOCIAL
SECURITY #], Format([SOCIAL SECURITY #],"@@@-@@-@@@@");
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail


Yes. My error.
You need to add a ) just before the ;.
....Format([SOCIAL SECURITY #],"@@@-@@-@@@@"));

I usually count opening and closing parenthesis to make sure they are
balanced, but I forgot to do that here.

 

[Van T. Dinh]

Are you doing the UPDATE Query using the Query grid / Query Design View?

If you do:

* In the "Field" row of an empty column, select the Field [SOCIAL SECURITY
#]
* In the "Update To" row of the same column, enter:

IIF( InStr([SOCIAL SECURITY #],"-")>0,[SOCIAL SECURITY #],
Format([SOCIAL SECURITY #],"@@@-@@-@@@@") )

(type exactly as above, all on ONE line).

I think the problem was the reference to the Field and the equal sign you
have in the expression you posted. I assumed that since you posted that
way, Fred and Dounglas thought that you are doing the Update Query via the
SQL view???

Going back to your earlier question about checking whether the actual stored
values have "-" or not. one quick way to check this out is to open the
design view of the Table, select the SSN Field, note down the InputMask and
the Format Strings (so that you can put them back later). Remove both the
InputMask and the Format Strings from the Field and open the Table in
DatasheetView. What you see in the SSN Field in the DatasheetView are the
actual values store.

BTW, [SOCIAL SECURITY #] is probably not a good Field name since it contain
space and invalid character # (?). While it is possible to use these, they
only complicate things later when you do VBA code, etc ... If it is still
possible, my recommendation is to change the Field name.

 

[Guest]

Yes I was using a update query. I'm still getting a error with this string.
IIF(INSTR([SOCIAL SECURITY #],"-")>0,[SOCIAL SECURITY #] ,Format([SOCIAL
SECURITY #],"@@@-@@-@@@@"))
Could this be due to the# sign or do I have some other Mistake. If I change
[SOCIAL SECURITY #] to say SSN, will I have to change all forms,
query's,reports etc.? Removing the format mask did show the real problem
early entries with dashes in and later entries stored without the dashes

Are you doing the UPDATE Query using the Query grid / Query Design View?

If you do:

* In the "Field" row of an empty column, select the Field [SOCIAL SECURITY
#]
* In the "Update To" row of the same column, enter:

IIF( InStr([SOCIAL SECURITY #],"-")>0,[SOCIAL SECURITY #],
Format([SOCIAL SECURITY #],"@@@-@@-@@@@") )

(type exactly as above, all on ONE line).

I think the problem was the reference to the Field and the equal sign you
have in the expression you posted. I assumed that since you posted that
way, Fred and Dounglas thought that you are doing the Update Query via the
SQL view???

Going back to your earlier question about checking whether the actual stored
values have "-" or not. one quick way to check this out is to open the
design view of the Table, select the SSN Field, note down the InputMask and
the Format Strings (so that you can put them back later). Remove both the
InputMask and the Format Strings from the Field and open the Table in
DatasheetView. What you see in the SSN Field in the DatasheetView are the
actual values store.

BTW, [SOCIAL SECURITY #] is probably not a good Field name since it contain
space and invalid character # (?). While it is possible to use these, they
only complicate things later when you do VBA code, etc ... If it is still
possible, my recommendation is to change the Field name.

--
HTH
Van T. Dinh
MVP (Access)

I don't think so, I count ((( and ))) your adding another ). The error is
invalid syntax you may have entered a operand with out a operator

 

[Van T. Dinh]

The square brackets should overcome the illegal characters.

Try:

IIF(INSTR([SOCIAL SECURITY #],"-")>0,
[SOCIAL SECURITY #] ,
Left([SOCIAL SECURITY #], 3) & "-" &
Mid([SOCIAL SECURITY #], 4, 2) & "-" &
Right([SOCIAL SECURITY #], 4) )

If the above doesn't work, post the SQL String of your query.

Yes, changing Field name means that you have to change the same in Queries,
Forms, Reports, VBA ... That while I wrote "While it is still possible ..."
(I actually meant "If it is still possible ..."). The Access Auto Name
Correct helps and there are some utilities (SpeedFerret, ...) to help with
this. Unfortunately, the Access Name Auto correct has some bugs so turn it
on just before you open the Table in DesignView to change the Field name and
turn it off after you finish.

Search Google for Douglas J. Steele's post with "SpeedFerret". Doug
normally lists about 3 or 4 utilities (some free, some you have to buy) and
check them out for yourself.

 

[Guest]

YOUR RIGHT. THE QUERY DID EXECUTE, BUT DIDN'T DO WHAT I THOUGHT IT WOULD THAT
IS ADD DASHES TO THE SS# NUMBER THAT HAD NONE.

Yes I was using a update query. I'm still getting a error with this string.
IIF(INSTR([SOCIAL SECURITY #],"-")>0,[SOCIAL SECURITY #] ,Format([SOCIAL
SECURITY #],"@@@-@@-@@@@"))
Could this be due to the# sign or do I have some other Mistake. If I change
[SOCIAL SECURITY #] to say SSN, will I have to change all forms,
query's,reports etc.? Removing the format mask did show the real problem
early entries with dashes in and later entries stored without the dashes

Are you doing the UPDATE Query using the Query grid / Query Design View?

If you do:

* In the "Field" row of an empty column, select the Field [SOCIAL SECURITY
#]
* In the "Update To" row of the same column, enter:

IIF( InStr([SOCIAL SECURITY #],"-")>0,[SOCIAL SECURITY #],
Format([SOCIAL SECURITY #],"@@@-@@-@@@@") )

(type exactly as above, all on ONE line).

I think the problem was the reference to the Field and the equal sign you
have in the expression you posted. I assumed that since you posted that
way, Fred and Dounglas thought that you are doing the Update Query via the
SQL view???

Going back to your earlier question about checking whether the actual stored
values have "-" or not. one quick way to check this out is to open the
design view of the Table, select the SSN Field, note down the InputMask and
the Format Strings (so that you can put them back later). Remove both the
InputMask and the Format Strings from the Field and open the Table in
DatasheetView. What you see in the SSN Field in the DatasheetView are the
actual values store.

BTW, [SOCIAL SECURITY #] is probably not a good Field name since it contain
space and invalid character # (?). While it is possible to use these, they
only complicate things later when you do VBA code, etc ... If it is still
possible, my recommendation is to change the Field name.

--
HTH
Van T. Dinh
MVP (Access)

I don't think so, I count ((( and ))) your adding another ). The error is
invalid syntax you may have entered a operand with out a operator

 

[Guest]

May be I didn't make my self clear. The early entries have dashes, the later
ones don't. I want to add dashes to the later entries. Or if it is easier
remove dashes from the earlier entries.+

Yes I was using a update query. I'm still getting a error with this string.
IIF(INSTR([SOCIAL SECURITY #],"-")>0,[SOCIAL SECURITY #] ,Format([SOCIAL
SECURITY #],"@@@-@@-@@@@"))
Could this be due to the# sign or do I have some other Mistake. If I change
[SOCIAL SECURITY #] to say SSN, will I have to change all forms,
query's,reports etc.? Removing the format mask did show the real problem
early entries with dashes in and later entries stored without the dashes

Are you doing the UPDATE Query using the Query grid / Query Design View?

If you do:

* In the "Field" row of an empty column, select the Field [SOCIAL SECURITY
#]
* In the "Update To" row of the same column, enter:

IIF( InStr([SOCIAL SECURITY #],"-")>0,[SOCIAL SECURITY #],
Format([SOCIAL SECURITY #],"@@@-@@-@@@@") )

(type exactly as above, all on ONE line).

I think the problem was the reference to the Field and the equal sign you
have in the expression you posted. I assumed that since you posted that
way, Fred and Dounglas thought that you are doing the Update Query via the
SQL view???

Going back to your earlier question about checking whether the actual stored
values have "-" or not. one quick way to check this out is to open the
design view of the Table, select the SSN Field, note down the InputMask and
the Format Strings (so that you can put them back later). Remove both the
InputMask and the Format Strings from the Field and open the Table in
DatasheetView. What you see in the SSN Field in the DatasheetView are the
actual values store.

BTW, [SOCIAL SECURITY #] is probably not a good Field name since it contain
space and invalid character # (?). While it is possible to use these, they
only complicate things later when you do VBA code, etc ... If it is still
possible, my recommendation is to change the Field name.

--
HTH
Van T. Dinh
MVP (Access)

I don't think so, I count ((( and ))) your adding another ). The error is
invalid syntax you may have entered a operand with out a operator

 

[Van T. Dinh]

Yes, so far we all showed you how to add dashes to the later SSN entries
that don't have dashes in the stored values ... but I think we got the
criteria incorrect (Copy and Paste problem).

Try:

IIF(InStr([SOCIAL SECURITY #],"-") = 0,
[SOCIAL SECURITY #] ,
Left([SOCIAL SECURITY #], 3) & "-" &
Mid([SOCIAL SECURITY #], 4, 2) & "-" &
Right([SOCIAL SECURITY #], 4) )

Note the equal sign rather than the greater than sign in the criteria.