Input mask problem

[Van T. Dinh]

Sorry. I got my logic twisted wrongly: The greater than > 0 is correct with
the IIf construction I posted previously with Left / Mid / Right. Let me
repeat again the correct expression (I *think*):

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

Sorry for the confusion I have caused.

 

[Bob B]

This didn't do it. I'm trying to put "-" in SS# with out the dashes in. The
query when executed lists only the SS#'s with the "-" already in place, llike
it replaced those already there.

My String: IIf(InStr([SOCIAL SECURITY #],"-")>0,[SOCIAL SECURITY
#],Left([SOCIAL SECURITY #],3) & "-" & Mid([SOCIAL SECURITY #],4,2) & "-" &
Right([SOCIAL SECURITY #],4))

Sorry. I got my logic twisted wrongly: The greater than > 0 is correct with
the IIf construction I posted previously with Left / Mid / Right. Let me
repeat again the correct expression (I *think*):

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

Sorry for the confusion I have caused.

--
HTH
Van T. Dinh
MVP (Access)

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.

 

[Douglas J Steele]

What everyone's been telling you should work.

Just to double-check things, create a new query based on the table in
question. Add a computed field to the query of InStr([SOCIAL SECURITY
#],"-").

Run the query. What value is showing up in the computed field?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

This didn't do it. I'm trying to put "-" in SS# with out the dashes in. The
query when executed lists only the SS#'s with the "-" already in place, llike
it replaced those already there.

My String: IIf(InStr([SOCIAL SECURITY #],"-")>0,[SOCIAL SECURITY
#],Left([SOCIAL SECURITY #],3) & "-" & Mid([SOCIAL SECURITY #],4,2) & "-" &
Right([SOCIAL SECURITY #],4))

Sorry. I got my logic twisted wrongly: The greater than > 0 is correct with
the IIf construction I posted previously with Left / Mid / Right. Let me
repeat again the correct expression (I *think*):

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

Sorry for the confusion I have caused.

--
HTH
Van T. Dinh
MVP (Access)

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.

 

[Bob B]

Select query with this in criteria of [SOCIAL SECURITY #]: InStr([SOCIAL
SECURITY #],"- ") returns no results

What everyone's been telling you should work.

Just to double-check things, create a new query based on the table in
question. Add a computed field to the query of InStr([SOCIAL SECURITY
#],"-").

Run the query. What value is showing up in the computed field?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

This didn't do it. I'm trying to put "-" in SS# with out the dashes in. The
query when executed lists only the SS#'s with the "-" already in place, llike
it replaced those already there.

My String: IIf(InStr([SOCIAL SECURITY #],"-")>0,[SOCIAL SECURITY
#],Left([SOCIAL SECURITY #],3) & "-" & Mid([SOCIAL SECURITY #],4,2) & "-" &
Right([SOCIAL SECURITY #],4))

Sorry. I got my logic twisted wrongly: The greater than > 0 is correct with
the IIf construction I posted previously with Left / Mid / Right. Let me
repeat again the correct expression (I *think*):

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

Sorry for the confusion I have caused.

--
HTH
Van T. Dinh
MVP (Access)



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.

 

[Douglas J Steele]

I didn't say to use that as a criteria: I said to create a computed field.

This means find an empty cell on the row marked Field in the query builder
and type the following in that cell:

InStr([SOCIAL SECURITY #],"-")

or, if you want,

FirstDash: InStr([SOCIAL SECURITY #],"-")

When you run the query, there should be a new column returned, with numbers
in it. If every row returns the same number, what number is it? Otherwise,
do you have a mix of rows, some with 4 in them and others with 0?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Select query with this in criteria of [SOCIAL SECURITY #]: InStr([SOCIAL
SECURITY #],"- ") returns no results

What everyone's been telling you should work.

Just to double-check things, create a new query based on the table in
question. Add a computed field to the query of InStr([SOCIAL SECURITY
#],"-").

Run the query. What value is showing up in the computed field?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

This didn't do it. I'm trying to put "-" in SS# with out the dashes

in.
The

query when executed lists only the SS#'s with the "-" already in

place,
llike

it replaced those already there.

My String: IIf(InStr([SOCIAL SECURITY #],"-")>0,[SOCIAL SECURITY
#],Left([SOCIAL SECURITY #],3) & "-" & Mid([SOCIAL SECURITY #],4,2) &

"-"
&

Right([SOCIAL SECURITY #],4))
:

Sorry. I got my logic twisted wrongly: The greater than > 0 is

correct
with

the IIf construction I posted previously with Left / Mid / Right.

Let
me

repeat again the correct expression (I *think*):

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

Sorry for the confusion I have caused.

--
HTH
Van T. Dinh
MVP (Access)



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.

 

[Van T. Dinh]

Something strange ...

Have you compacted and repair the database regularly?

Try compacting and repairing ...

If it still doesn't work, create a new (empty) database and import all
objects from the existing database. Open the VBE and do a Compile (just in
case there are compilation errors). Go back to the Access window, do a
Compact and Repair. Finally, try the Update Query again.

 

[Van T. Dinh]

Doug

Earlier in the thread, Bob removed the InputMask & the Format of the SSN in
the design view of the Table and opened the DatasheetView of the Table to
confirm that the SSN values have hyphen for ealier entries and no hyphen for
later entries.

But no harm in checking ... and it may show up some problems with InStr()
....

 

[Van T. Dinh]

Bob

Please post the *WHOLE* SQL String of your Query, NOT just the Update
expression, i.e. the IIf expression. I asked for this before too but I
haven't seen it yet.

 

[Bob B]

I have 4's in the in the SSN that have "_" in them and 0's in those that
don't.

I didn't say to use that as a criteria: I said to create a computed field.

This means find an empty cell on the row marked Field in the query builder
and type the following in that cell:

InStr([SOCIAL SECURITY #],"-")

or, if you want,

FirstDash: InStr([SOCIAL SECURITY #],"-")

When you run the query, there should be a new column returned, with numbers
in it. If every row returns the same number, what number is it? Otherwise,
do you have a mix of rows, some with 4 in them and others with 0?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Select query with this in criteria of [SOCIAL SECURITY #]: InStr([SOCIAL
SECURITY #],"- ") returns no results

What everyone's been telling you should work.

Just to double-check things, create a new query based on the table in
question. Add a computed field to the query of InStr([SOCIAL SECURITY
#],"-").

Run the query. What value is showing up in the computed field?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


This didn't do it. I'm trying to put "-" in SS# with out the dashes in.
The
query when executed lists only the SS#'s with the "-" already in place,
llike
it replaced those already there.

My String: IIf(InStr([SOCIAL SECURITY #],"-")>0,[SOCIAL SECURITY
#],Left([SOCIAL SECURITY #],3) & "-" & Mid([SOCIAL SECURITY #],4,2) & "-"
&
Right([SOCIAL SECURITY #],4))
:

Sorry. I got my logic twisted wrongly: The greater than > 0 is correct
with
the IIf construction I posted previously with Left / Mid / Right. Let
me
repeat again the correct expression (I *think*):

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

Sorry for the confusion I have caused.

--
HTH
Van T. Dinh
MVP (Access)



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.

 

[Bob B]

Did compact & Repair without results. Here is the SQL string. Will try the
import.

SELECT [CLIENT STATS OCT 2003 AND UP].ID, [CLIENT STATS OCT 2003 AND
UP].[SOCIAL SECURITY #]
FROM [CLIENT STATS OCT 2003 AND UP]
GROUP BY [CLIENT STATS OCT 2003 AND UP].ID, [CLIENT STATS OCT 2003 AND
UP].[SOCIAL SECURITY #]
HAVING ((([CLIENT STATS OCT 2003 AND UP].[SOCIAL SECURITY
#])=IIf(InStr([SOCIAL SECURITY #],"-")>0,[SOCIAL SECURITY #],Left([SOCIAL
SECURITY #],3) & "-" & Mid([SOCIAL SECURITY #],4,2) & "-" & Right([SOCIAL
SECURITY #],4))));

Bob

Please post the *WHOLE* SQL String of your Query, NOT just the Update
expression, i.e. the IIf expression. I asked for this before too but I
haven't seen it yet.

--
HTH
Van T. Dinh
MVP (Access)

This didn't do it. I'm trying to put "-" in SS# with out the dashes in.
The
query when executed lists only the SS#'s with the "-" already in place,
llike
it replaced those already there.

My String: IIf(InStr([SOCIAL SECURITY #],"-")>0,[SOCIAL SECURITY
#],Left([SOCIAL SECURITY #],3) & "-" & Mid([SOCIAL SECURITY #],4,2) & "-"
&
Right([SOCIAL SECURITY #],4))

 

[Van T. Dinh]

Bob

What you posted is a SELECT Query, not an UPDATE Query. Select Query simply
selects Records and displays selected data in a Datasheet. Fred advised you
earlier in the thread to do an UPDATE Query.

The UPDATE Query should have the SQL String:

UPDATE [CLIENT STATS OCT 2003 AND UP]
SET [SOCIAL SECURITY #] =
IIf( InStr([SOCIAL SECURITY #],"-")>0,
[SOCIAL SECURITY #],
Left([SOCIAL SECURITY #],3) & "-" &
Mid([SOCIAL SECURITY #],4,2) & "-" &
Right([SOCIAL SECURITY #],4) );

Alternatively, you can use:

UPDATE [CLIENT STATS OCT 2003 AND UP]
SET [SOCIAL SECURITY #] =
Left([SOCIAL SECURITY #],3) & "-" &
Mid([SOCIAL SECURITY #],4,2) & "-" &
Right([SOCIAL SECURITY #],4)
WHERE InStr([SOCIAL SECURITY #],"-")>0;

The 2nd SQL String is more efficient than the first.

 

[Van T. Dinh]

Sorry, I got my logic twisted again: If you want to use the 2nd SQL String,
change the greater than operator (>) to equal operator (=).

 

[Bob B]

I don't know what happened, this was a update query one time.
Thanks for all your efforts. I solved the problem another way. In the table
view I selected all the SS# with out dashes as a group and then did a copy
and paste.
How can I compile these forms, queries, etc. These run on old machines and
are very slow..

Bob

What you posted is a SELECT Query, not an UPDATE Query. Select Query simply
selects Records and displays selected data in a Datasheet. Fred advised you
earlier in the thread to do an UPDATE Query.

The UPDATE Query should have the SQL String:

UPDATE [CLIENT STATS OCT 2003 AND UP]
SET [SOCIAL SECURITY #] =
IIf( InStr([SOCIAL SECURITY #],"-")>0,
[SOCIAL SECURITY #],
Left([SOCIAL SECURITY #],3) & "-" &
Mid([SOCIAL SECURITY #],4,2) & "-" &
Right([SOCIAL SECURITY #],4) );

Alternatively, you can use:

UPDATE [CLIENT STATS OCT 2003 AND UP]
SET [SOCIAL SECURITY #] =
Left([SOCIAL SECURITY #],3) & "-" &
Mid([SOCIAL SECURITY #],4,2) & "-" &
Right([SOCIAL SECURITY #],4)
WHERE InStr([SOCIAL SECURITY #],"-")>0;

The 2nd SQL String is more efficient than the first.

--
HTH
Van T. Dinh
MVP (Access)

Did compact & Repair without results. Here is the SQL string. Will try
the
import.

SELECT [CLIENT STATS OCT 2003 AND UP].ID, [CLIENT STATS OCT 2003 AND
UP].[SOCIAL SECURITY #]
FROM [CLIENT STATS OCT 2003 AND UP]
GROUP BY [CLIENT STATS OCT 2003 AND UP].ID, [CLIENT STATS OCT 2003 AND
UP].[SOCIAL SECURITY #]
HAVING ((([CLIENT STATS OCT 2003 AND UP].[SOCIAL SECURITY
#])=IIf(InStr([SOCIAL SECURITY #],"-")>0,[SOCIAL SECURITY #],Left([SOCIAL
SECURITY #],3) & "-" & Mid([SOCIAL SECURITY #],4,2) & "-" & Right([SOCIAL
SECURITY #],4))));

 

[Van T. Dinh]

Suggest you create a new thread, Bob.

This thread is too deep and there will be few potential respondents for your
new question.

In addition, clarify your description and what you meant by "Compile" ...
Normally only VBA code can be compiled, NOT Queries / Forms / Reports ...