Working with input mask on MS ACCESS 2007

[JM Noval]

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

[NeoPa]

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.

[JM Noval]

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.)

[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. Replace([Field], '# ', '#')

2. Replace([Result of first Replace], ' ', '-')

That would result in :

Replace(Replace([Field], '# ', '#'), ' ', '-')

[JM Noval]

Thanks neopa, Ill try what you suggested..
I'm still trying to learn all this stuff so you've been a great help :)

[NeoPa]

Good to hear. I try to help where I can.

[JM Noval]

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!

[NeoPa]

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 :

Replace(Replace(MixCase([Field]), '# ', '#'), ' ', '-')