Solved: Forcing correct data entry

[Aussiebear]

I have a workbook that when data is entered, it needs to be entered in a correct manner. Is there a way to ensure that this can occur?

Example 1. When entering names, it is preferred that surnmanes be entered before initials.

DB Smith should be Smith DB
DB & AS Smith should be Smith DB & AS
DB & AS Smith & Son should be Smith DB & AS & Son

But just to complicate the matter there will be times when entering business names that a business name may appear to be a surname

PK Enterprises
AGB Farms

Is it therefore possible for an options box ( with a suggested alternative)to appear as against the option of a error message?

I realise you can find pre existing names by referring to those already in use, but what about if the name was new?

Similarily one could adapt this to help when addresses are also being entered where the range of possibilities of incorrect data entry that occur when operators enter, say;

P.O. Box rather than PO Box, or M.S or M/S rather than MS for mail service.

Then there's the issue of entering property names, with some operators entering the name of the property enclosed in single quotation marks, when all that is required is just the name.

[Brandtrock]

Your data is only going to be as good or clean as your operators are, without having to police it yourself.

Having very specific rules for entering asset names and descriptions in a large corporate database didn't prevent the six or so individuals who did the entry in the leasing department from cobbling things up on a routine basis at one of my former employers. Using lower case l for 1 and upper case O for zero in a Vehicle Identification Number were two of my personal favorites.

Running a reporting software (Crystal Reports) on all assets initially, then only on new additions once the existing errors were corrected solved the problem for us.

We loaded assets into the FAS database by importing an Excel spreadsheet whereas the other department (leased assets) keyed them by hand. You be the judge on where the errors kept showing up. My department (purchased assets) was able to identify any keying errors we made PRIOR to importing to the FAS database, the leased assets had to be identified post entry.

Once the assets were all entered per the standard, we would depreciate them. The leasing department couldn't seem to grasp that keeping things in a standard format made the job of reporting much easier.

Didn't mean to get on the soap box for so long, but even 5 years removed from the situation, it just confounds me that even after several explanations, this concept was not to be understood.

Regarding your specific situation, automatically checking some of the fields you want presents a degree of difficulty which you seem to grasp given your description of business names and suffixes.

My opinion on the whole issue would be to keep a master list with someone in charge of it, then check names against the list and correct existing ones. Prompt the "list police person" to check new names that appear to be names but are businesses. Doing this a couple times a day would allow for relatively easy mopping up.

Regarding the preferred standard for PO box etc, simply running a find and replace in the BeforeSave event could handle those types of issues.

HTH,

[Aussiebear]

I hear where you are coming from. The GSRT workbook that I use to mange the QA requirements for incomming commodities came about because the origonal program to do the job lack any validation on the data entry. I found a number on instances where the same trading entity had been entered up to 20 times because someone added a dot, an apostrophe, single quotes around a property name, a slash between M & S , added a State name or put initials first etc.

When I approach the local IT manager or the Group IT manager re the lack of validation, and or the lack of supervising the data entry, or the lack of clearly defined rules regarding data entry, and or the lack of training available, they basicily said " Too bad, so sad".

So I built one of my own to overcome this situation. At the moment I and at odd occassions, someone else, predominately enter the data. I understand the rules regarding the data entry, but there will come a time when I'm not here in control of the data entry, and I would like to see some form of validation applied.

[malik641]

I have a workbook that when data is entered, it needs to be entered in a correct manner.

Good luck!
Just kidding

I have no trust for humans to enter data...I even question myself sometimes! And I'm pretty picky about consistency when it comes to data entry...or anything really.

My opinion on the whole issue would be to keep a master list with someone in charge of it, then check names against the list and correct existing ones. Prompt the "list police person" to check new names that appear to be names but are businesses. Doing this a couple times a day would allow for relatively easy mopping up.

I'm with Brandtrock on this one. Except, I would have the "list police person" be the one to enter all new entries into the list workbook (which would be seperate from the regular end user workbook) for the Users to find in the lists. So there would be less of a chance of an error.

[Aussiebear]

Hmmm.... that "one" for and two "sort of against". Does that mean I'm still winning?

[malik641]

Hmm...I'm pretty sure that would beeeeeeeeeeee...no.


On a side note have you seen those business card scanners? They have a pretty good algorithm to store business information as it's seen on the card and it stores the data and I believe it can be exported to a databse. Maybe you could integrate that with what you're doing....but then again, by what you said about your lazy IT department (sorry, but to me that's a pretty crummy reaction you got outta them...and you can tell them I said that ) it doesn't sound like you'd be getting one of those if you don't already have one.

[Brandtrock]

I'm with Brandtrock on this one. Except, I would have the "list police person" be the one to enter all new entries into the list workbook (which would be seperate from the regular end user workbook) for the Users to find in the lists. So there would be less of a chance of an error.

Mayhap it is the accountant in me, but the list police person and the data entry person being different leads to better segregation of duties, which from an auditing standpoint is stronger.

While I agree that the IT department demonstrated a poor attitude toward your request, the IT function is generally concerned with keeping the hardware and software secure and running. How that software and hardware are implemented to achieve any particular end result is left to the end users discretion. That is, of course, until a perfectly good solution is implemented without IT's input. Then, and only then, does it become a security issue that will have to be reworked.


Regards,

[malik641]

Mayhap it is the accountant in me, but the list police person and the data entry person being different leads to better segregation of duties, which from an auditing standpoint is stronger.

You're probably right, as I'm sure you have more experience with this sort of thing.

How that software and hardware are implemented to achieve any particular end result is left to the end users discretion. That is, of course, until a perfectly good solution is implemented without IT's input. Then, and only then, does it become a security issue that will have to be reworked.

I know. What a load of crap.

[Aussiebear]

Hmm...I'm pretty sure that would beeeeeeeeeeee...no.

Really... Well I, Me & Myself thinks that you two.... are one short.

[Bob Phillips]

You're on a loser here, as far as names and addresses go. The format on such things is so variable that an algorithm would be very difficult (consider Baron von Richtofen, Ian St. John, Peter Clarke Jr.. Peter Smith III, Lady Sarah Jane Grey de Montfort, etc. etc.), and then there is the possiblility that they entered as you wanted, or tried to but got it wrong. What do you do with Paul James, change it to James Paul or leave it alone.

My experience tells me that the only chance that you have here is strong guidlenes on how to enter the data, and hope they conform. If it is imperative that you do better, you have to have a central unit that reviews and amends the data. Not ideal, but life isn't ideal.

[Aussiebear]

Ouch, down & out for the count.

[Aussiebear]

consider Baron von Richtofen, Ian St. John, Peter Clarke Jr.. Peter Smith III, Lady Sarah Jane Grey de Montfort, etc. etc.

Bob, this is Australia, we do not allow people to settle here with names like Baron, P Smith III, Lady Sarah Jane ( Draw breath) Grey de Monfort.....(draw another breath).

[mdmackillop]

An alternative approach that I've used in database situations, which could be applied here. Use data validation on the first entered name. If it fails to match an existing name, then pop up a UserForm with labelled fields for a new entry. If they still get it wrong, maybe you could exile them.

Regarding unwanted punctuation, this function in one of DRJ's KB items will help remove such (intended for cleaning up file names), maybe needs a bit of tweaking.

[vba]Function StripIllegalChar(StrInput)

Dim RegX As Object

Set RegX = CreateObject("vbscript.regexp")

RegX.Pattern = "[\" & Chr(34) & "\!\@\#\$\%\^\&\*\(\)\=\+\|\[\]\{\}\`\'\;\:\<\>\?\/\,]"
RegX.IgnoreCase = True
RegX.Global = True

StripIllegalChar = RegX.Replace(StrInput, "")

ExitFunction:

Set RegX = Nothing

End Function [/vba]

[Aussiebear]

If they still get it wrong, maybe you could exile them.

You little beauty!!!!


Righto, heads up people...... Firstly 2 IT guys, slightly used, but never dented. Experienced with Server 2000, straggly beards, unkept clothes etc. Will consider seperating but not known if they survive on their own.

Secondly, one "only driven on Sundays" middle management type, tainted with Head Office business speak, unknown last oil change date, only runs when pays are being handed out.

BTW. Must take "bride" aka "Minister for Warfare & Finance" too if considering second option.

Open all reasonable offers considered.

[YellowLabPro]

Ted,
How is the data entered initially, meaning in what program and from what data source?