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

Working with input mask on MS ACCESS 2007

P: 7
hi! need help..

here's the sample data I need to mask
10653 KIMOLA WAY to
10653-Kimola-Way

here's another example:
# 1006 12069 Harris Rd to
#1006-12069-Harris-Rd

another example:
# 42 22308 124 AVE to
#42-22308-124-Ave

so the idea is
remove space between # and suite number
for example # 42 --->> #42;

remove the spaces in between the other number and characters to "-"
for example # 42 22308 124 Ave ---->> 22308-124-Ave

change uppercase to sentence case
for example 10653 KIMOLA WAY ---->> 10653-Kimola-Way


what's the best way to do this?

thanks
Feb 22 '12 #1

✓ answered by NeoPa

It won't be updated from an import with an Input Mask.

Your explanation is much better now, by the way. Now the examples help.

I would suggest you do this within a query. The basic approach is to import the data into a holding table first, then use an APPEND query to move the data across from there to the eventual table. The query would include translation of the data, probably using two Replace() calls :
  1. Expand|Select|Wrap|Line Numbers
    1. Replace([Field], '# ', '#')
  2. Expand|Select|Wrap|Line Numbers
    1. Replace([Result of first Replace], ' ', '-')

That would result in :
Expand|Select|Wrap|Line Numbers
  1. Replace(Replace([Field], '# ', '#'), ' ', '-')

Share this Question
Share on Google+
7 Replies


NeoPa
Expert Mod 15k+
P: 31,186
You've given no explanation of what you want entered, just what needs fixing in some very different circumstances. That rules out using an Input Mask at this stage.

You could use code in the AfterUpdate event procedure of the control, but you'd need to clarify your own understanding of what needs doing before you start that. Your explanations so far give next to no idea of what's required. Examples are good as additions to an explanation, but they're rarely (and certainly not in this case) adequate on their own.
Feb 22 '12 #2

P: 7
Hi neopa, Sorry I haven't clearly explained what needs to be done.
what I need to do actually is to import the address data (examples on top). It should be automatically populated during import by replacing the “spaces” in the “Address” field with “-“ (except the space between # and the unit apartment number. This space should be deleted.)
Feb 22 '12 #3

NeoPa
Expert Mod 15k+
P: 31,186
It won't be updated from an import with an Input Mask.

Your explanation is much better now, by the way. Now the examples help.

I would suggest you do this within a query. The basic approach is to import the data into a holding table first, then use an APPEND query to move the data across from there to the eventual table. The query would include translation of the data, probably using two Replace() calls :
  1. Expand|Select|Wrap|Line Numbers
    1. Replace([Field], '# ', '#')
  2. Expand|Select|Wrap|Line Numbers
    1. Replace([Result of first Replace], ' ', '-')

That would result in :
Expand|Select|Wrap|Line Numbers
  1. Replace(Replace([Field], '# ', '#'), ' ', '-')
Feb 22 '12 #4

P: 7
Thanks neopa, Ill try what you suggested..
I'm still trying to learn all this stuff so you've been a great help :)
Feb 22 '12 #5

NeoPa
Expert Mod 15k+
P: 31,186
Good to hear. I try to help where I can.
Feb 22 '12 #6

P: 7
Hi there once more neopa, I have tried what you suggested and it actually worked! thanks! The only Problem now is that some data are written in capital letters. What I would actually need to do is to change it to sentence case.

So for example,
the data 10653 KIMOLA WAY

What you have given replaces the data only to 10653-KIMOLA-WAY
when what I needed it to be like is 10653-Kimola-Way.

Thanks once more!
Feb 22 '12 #7

NeoPa
Expert Mod 15k+
P: 31,186
We'll make this the last side-question for the thread shall we. If you have any new ones then they'll need to be entered as separate questions in separate threads.

For this though, you need to use the MixCase() function. Your actual field would be done as :
Expand|Select|Wrap|Line Numbers
  1. Replace(Replace(MixCase([Field]), '# ', '#'), ' ', '-')
Feb 23 '12 #8

Post your reply

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