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

Normalize data during data entry?

P: 1
I'm a FileMaker user trying to help a client with an Access data entry problem:

When text is copied from another application and pasted into an Access form, the pasted text sometimes includes unwanted characters (e.g., leading or trailing whitespace, embedded carriage returns, punctuation).

We'd like to automatically normalize the pasted text. I know how I'd do this in FileMaker - perform an Auto-Enter calculation that replaces the newly entered/modified value with a computed (normalized) value.

Does Access provide a hook for arbitrary normalization during data entry?
Dec 28 '06 #1
Share this Question
Share on Google+
13 Replies


Expert 100+
P: 1,892
You could use trim() to trim all the spaces before and after the content. Or InStr() if there are other characters to get rid of.
Dec 28 '06 #2

NeoPa
Expert Mod 15k+
P: 31,602
Replace() may work better for you but I don't think there's a standard setting or function to do the job for you.
Dec 30 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
I'm a FileMaker user trying to help a client with an Access data entry problem:

When text is copied from another application and pasted into an Access form, the pasted text sometimes includes unwanted characters (e.g., leading or trailing whitespace, embedded carriage returns, punctuation).

We'd like to automatically normalize the pasted text. I know how I'd do this in FileMaker - perform an Auto-Enter calculation that replaces the newly entered/modified value with a computed (normalized) value.

Does Access provide a hook for arbitrary normalization during data entry?
Simple answer is not when data is being copied and pasted. However the data can be normalised using the after update event. I would use the trim function first to eliminate whitespace and follow this with repace functions which would look for carriage returns, tab characters, etc and replace them with "" a null character.

Mary
Jan 1 '07 #4

NeoPa
Expert Mod 15k+
P: 31,602
Strictly speaking - "" is an empty string rather than a null character.
Be that as it may, Mary's solution is good and will give you what you're after.
Jan 1 '07 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
Strictly speaking - "" is an empty string rather than a null character.
Be that as it may, Mary's solution is good and will give you what you're after.
Get lost Ade :)
Jan 1 '07 #6

NeoPa
Expert Mod 15k+
P: 31,602
Get lost Ade :(
This is what I get for trying to be helpful :innocent expression:
Jan 1 '07 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
This is what I get for trying to be helpful :innocent expression:
You wouldn't know an innocent expression if it jumped up and bit you :)
Jan 2 '07 #8

NeoPa
Expert Mod 15k+
P: 31,602
You wouldn't know an innocent expression if it jumped up and bit you :)
Nonsense, I couldn't get away with half of what I do if I didn't understand about innocent expressions :D
Jan 2 '07 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
Nonsense, I couldn't get away with half of what I do if I didn't understand about innocent expressions :D
Substitute Cheeky for Innocent ...
Jan 2 '07 #10

missinglinq
Expert 2.5K+
P: 3,532
This is driving me nutz, Mary (yeah, I know, it being a short trip!) but what the Devil is your new avatar?
Jan 3 '07 #11

MMcCarthy
Expert Mod 10K+
P: 14,534
This is driving me nutz, Mary (yeah, I know, it being a short trip!) but what the Devil is your new avatar?
Hi Linq,

It's supposed to be a fire breathing dragon. I think I'm going to have to change it.

Mary
Jan 3 '07 #12

missinglinq
Expert 2.5K+
P: 3,532
That was my third guess...behind an Irish Mond and a malformed sheep!

We're keeping late hours tonight, aren't we, Mary!
Jan 3 '07 #13

MMcCarthy
Expert Mod 10K+
P: 14,534
That was my third guess...behind an Irish Mond and a malformed sheep!

We're keeping late hours tonight, aren't we, Mary!
This is normal for me. I don't sleep much.

Mary
Jan 3 '07 #14

Post your reply

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