?
Solved

Delete Data SQL

Posted on 2011-10-25
13
Medium Priority
?
201 Views
Last Modified: 2012-05-12
Hi Experts!

Hope all is well?
OK I have a text field called "Team Leaders" and a table called Performance, normally the field is populated with names like Pirates and Winners. My problem is for the first few months of this project the field was populated with the team leaders company number "9023"

Is there anyway to simply delete all the numbers from this field without losing anything else?
Having them there is really messing up the reports. I would rather have nulls or empty if possible

Thank you in advance
0
Comment
Question by:GrahamSA
  • 3
  • 3
  • 3
  • +3
13 Comments
 
LVL 49

Expert Comment

by:Dale Fye
ID: 37026314
Backup your table first, in case you make a typo.

UPDATE Performance SET [Team Leaders] = NULL WHERE [Team Leaders] = "9023"
0
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 37026346
Create a an Update query and using the SQL View copy the syntax below

UPDATE Performance SET Performance.[Team Leaders] = " "
WHERE (((Performance.[Team Leaders])="9023"));

Then, run the query.

Sincerely,
Ed
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37026348

UPDATE Performance SET [Team Leaders] = NULL WHERE Isnumeric([Team Leaders]) = -1
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:GrahamSA
ID: 37026359
Thanks fyed

All together the are 26 different numbers for the 26 teams.
Would I have to do this for each one or is there a way to do this one shot?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37026375
GrahamSA,

see my post at http:#a37026348

are you updating SQL table or Access table, for SQL try this

UPDATE Performance SET [Team Leaders] = NULL WHERE Isnumeric([Team Leaders]) = True

or

UPDATE Performance SET [Team Leaders] = NULL WHERE Not Isnumeric([Team Leaders]) = 0
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 37026378
Use the SQL that capricorn1 provided to accomodate multiple different numeric values.
0
 
LVL 6

Expert Comment

by:mcp111
ID: 37026392
Try

update performance
set [Team Leaders]=""
where isnumeric([Team Leaders])

This will just set all numbers to blank in the team leaders field

you can also not mess with the database and simply display blank in the report if it is numeric by using client side reporting functions.

Your choice.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37026407
<No Points wanted>

you may want to add visual confirmation for the command (in case a use clicks the button by mistake) anytime you run a "destructive" (and no Undo available) command.

Something "Roughly" like this:

Dim bytAnswer As Byte
bytAnswer = MsgBox("Proceed with bulk delete?", vbYesNo + vbQuestion)
    'If user clicks No
    If bytAnswer = vbNo Then
        Exit Sub
    End If

'User clicks yes
currentDB.runSQL "UPDATE Performance SET [Team Leaders] = NULL WHERE [Team Leaders] = '9023'",dbfailonerror


JeffCoachman
0
 
LVL 6

Expert Comment

by:mcp111
ID: 37026454
boag2000:

What's the guarantee that the only number is "9023". I would rather check for all numbers.
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 37026505
mcp111,

The original post said:  field was populated with the team leaders company number "9023"

It didn't say (as the subsequent post did) that there were 26 other versions of company number as well.
0
 

Author Comment

by:GrahamSA
ID: 37026571
Hi capricorn1:

I used this code

UPDATE Performance SET [Team Leader] = NULL WHERE Isnumeric([Team Leader]) = True

Took the s out of leaders (sorry for the typo)

The following error occured while executing the query:
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'True'.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 37026602
try this

UPDATE Performance SET [Team Leader] = NULL WHERE Isnumeric([Team Leader]) = 1

or

UPDATE Performance SET [Team Leaders] = NULL WHERE Not Isnumeric([Team Leaders]) = 0
0
 

Author Closing Comment

by:GrahamSA
ID: 37026658
Thank you so much everyone!

This did the trick Prefectly

UPDATE Performance SET [Team Leader] = NULL WHERE Isnumeric([Team Leader]) = 1
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question