By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,998 Members | 2,903 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,998 IT Pros & Developers. It's quick & easy.

How do I remove a space at the beginning of the field?

Seth Schrock
Expert 2.5K+
P: 2,941
I've got a database that has something like 1000 customers. I've been noticing that every once in awhile, a name will be entered that has a space at the beginning. This obviously messes up the the alphabetical sorting. Is there a way to run a job on startup that would search through the customer table for fields that have a space at the beginning? I would also be happy with preventing the data from being entered if it start with a space. Here is the setup for the customer table:

Expand|Select|Wrap|Line Numbers
  1. Customer
  2. [CustomerID]  PK
  3. [Last Name]
  4. [First Name]
  5. [Middle Initial]
I would want to test each field to see if it started with a space. The trick is that if the customer is a business instead of an individual, the [First Name] and [Middle Initial] fields will be blank (I believe they would be null).

I'm not sure if this is possible, but it is worth a try.
Dec 23 '11 #1

✓ answered by Rabbit

So it turns out you can't use BeforeUpdate because changing the value will prevent it from updating. But you can still use AfterUpdate.

If my control is Client I could do something like:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Client_AfterUpdate()
  2.     Me.Client.Value = Trim(Me.Client.Value)
  3. End Sub

Share this Question
Share on Google+
12 Replies


Rabbit
Expert Mod 10K+
P: 12,366
If you don't mind getting rid of all spaces at the beginning and end, you can use the Trim() function.
Dec 23 '11 #2

Seth Schrock
Expert 2.5K+
P: 2,941
I looked up the Trim() function. It seems like all it does is show the value without the spaces instead of actually removing the spaces from the table. The place where I see the customer names is in a combo box that combines each part of the name to create a whole name. If I use the trim() function, would this still work and allow me to start typing in a name and have it find it for me?

The code that I use to combine the names is as follows:
Expand|Select|Wrap|Line Numbers
  1. SELECT Customer.CustomerID, 
  2. [Last Name] & (', '+[First Name]) & (' '+[Middle Initial]) AS WholeName
  3. FROM Customer
  4. ORDER BY [Last Name] & (', '+[First Name]) & (' '+[Middle Initial]);
  5.  
I'm just thinking that it would be easier to just remove the space from the table once instead of having to insert the Trim() function in each combo box that gives the customer name and in each report.

Also, would the LTrim() and RTrim() functions work the same way?

Also, would LTrim() and RTrim() work the same way?
Dec 23 '11 #3

Rabbit
Expert Mod 10K+
P: 12,366
You could certainly update the table.
Expand|Select|Wrap|Line Numbers
  1. update tableName
  2. set [name] = Trim([name])
And yes, you could use either RTrim or LTrim depending on if you want to keep leading or trailing spaces, respectively.
Dec 23 '11 #4

Seth Schrock
Expert 2.5K+
P: 2,941
I'm just thinking of when the best time would be to trigger that update query. Is there a way to test a field in an after update event to see if it starts with a space, and if true, then run the update query for that record? I'm thinking that running that kind of query every time that the program opens might not be the best option performance wise.
Dec 23 '11 #5

Rabbit
Expert Mod 10K+
P: 12,366
You could probably use the before update event to update the value in the control before it ever gets inserted into the database.
Dec 23 '11 #6

Seth Schrock
Expert 2.5K+
P: 2,941
Okay, I'm not sure how to do that.
Dec 23 '11 #7

Rabbit
Expert Mod 10K+
P: 12,366
So it turns out you can't use BeforeUpdate because changing the value will prevent it from updating. But you can still use AfterUpdate.

If my control is Client I could do something like:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Client_AfterUpdate()
  2.     Me.Client.Value = Trim(Me.Client.Value)
  3. End Sub
Dec 23 '11 #8

Seth Schrock
Expert 2.5K+
P: 2,941
Thanks! I had tried to do something like that, but it was a bunch more complicated and it didn't work.
Dec 23 '11 #9

Rabbit
Expert Mod 10K+
P: 12,366
Not a problem.
Dec 23 '11 #10

NeoPa
Expert Mod 15k+
P: 31,492
That's the solution you need, certainly. You needn't specify the .Value bit at the end, as it's the default property, but doing so is explicit and certainly does no harm.

PS. I liked your name-displaying SQL :
Expand|Select|Wrap|Line Numbers
  1. [Last Name] & (', '+[First Name]) & (' '+[Middle Initial]) AS WholeName
So few people realise you can do this the way you have, but it's perfect usage for the & and + operators (See Using "&" and "+" in WHERE Clause).
Dec 24 '11 #11

Seth Schrock
Expert 2.5K+
P: 2,941
I'm glad that you like my SQL since I think that it was you who gave it to me about a year ago. :) Thanks for the link. I never had cause to look at why each was used, but I did wonder about it.
Dec 24 '11 #12

NeoPa
Expert Mod 15k+
P: 31,492
Seth Schrok:
I'm glad that you like my SQL since I think that it was you who gave it to me about a year ago. :)
The important point is not so much that you got it from me, but that you got it. Many others have similarly seen the examples, but very few have had the sense to use them ;-)

Anyway, I'm glad the link helped you to understand how and why it does what you need.
Dec 24 '11 #13

Post your reply

Sign in to post your reply or Sign up for a free account.