attempting to clean up a free form text box (1 Viewer)

[penfold1992]

When we are managing incidents, users have decided on their own method of recording the incident... here are some examples

INC002546
INCIDENT002546
2546
INC# 2546
Inc# 2546
inc# 2546
Inc # 2546

and more... you get the point anyway :banghead:

The correct way is the following:
Inc# 2546
^ capital I, no space between "c" and "#" but a space AFTER the "#"
Then the 4 digit long number.

can anyone give me some ideas on how to tackle this problem?
I was thinking along the lines of trying to identify the number, removing everything else and then placing "Inc# " before it...

there are some issues though,
currently we allow notes, which should be made like such:
Inc# 2546 - Notes
however, people have their own method for this too (however most of them are at least after then incident number so that makes things much easier)
another issue is on the same database, some manual requests also appear which have different entry format:
RQ# GK034LW2052
but that contains a mixture of letters and numbers... so when identifying the request number... as long as I make sure that it is a number and not a string of numbers and letters, I should be ok... (fingers crossed)

FINALLY, I think there should be some kind of backup system in place which just spits the entry out as it was entered in, if the coding has any sort of issue with sorting it.

 

[CJ_London]

Not sure what your question is - do you want to clean up existing entries or are you looking for a way to enforce correct input?

If the latter your proposal is pretty much there - have a number field and prefix with 'Inc #' when reporting. With regards the notes suffix I would have a tickbox which if ticked adds ' - notes' as a suffix - but does make me wonder if your data is normalised.

Also how do you currently ensure that two users don't use the same incident number?

 

[penfold1992]

Not sure what your question is - do you want to clean up existing entries or are you looking for a way to enforce correct input?

If the latter your proposal is pretty much there - have a number field and prefix with 'Inc #' when reporting. With regards the notes suffix I would have a tickbox which if ticked adds ' - notes' as a suffix - but does make me wonder if your data is normalised.

Also how do you currently ensure that two users don't use the same incident number?

yes its the latter.
Again, the problem with a number field with a prefix is that the RQ# incidents will not be allowed to be entered there.

what do you mean "if your data is normalised"

also, good question... how do i ensure that two users dont use the same incident number.... difficult to answer because in the database, there can be multiple incidents of the same number and this is OK. some incidents require action weekly and we use the same request number for that... or they require an action to be done 4 or 5 times... again we use the same number. so ensuring uniqueness is not a problem.

 

[jdraw]

I suggest you do some research on database design to get familiar with some fundamental database principles. Anyone working on support or maintenance of a database application should be familiar with Normalization.
If you want data to be stored in some standard manner, then the user interface, form design and edit/validation procedures have to be designed accordingly.
You can not (and should not) expect that some decree "thou shalt record information in the following format.." will be effective. It may work if you are the only one doing input, but even then I'd say your effective data entry will move toward 75%.
If you want consistency in data entry, then you must design it 'foolproof" (since there are many more fools - either explicitly trying to sabotage your effort, or implicitly by not doing exactly what you told them, or skipping fields, or typing garbage....).

Here are a few links that will help.
http://forums.aspfree.com/attachment.php?attachmentid=4712&d=1201055452
http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip

Good luck.

 

[penfold1992]

ahhh sorry I thought you were asking if my free form text box was normalized.

yea i didnt design the database nor do I have the ability to change the tables within, only apply rules to the data.
I realise it would be good to make it restrictive but unfortunately I cant =(

is there anyway to test if the string would fit into an input mask?
ie? if I have a string... can i test that its in the format:
00L0LLAA00

 

[jdraw]

Do you have a list of rules you're planning on implementing? The reason I ask is, if you can't change some things, then the errors(that you're trying to fix) may continue once you leave/finish.

You can use an input mask on a field. If the entry doesn't match the mask you will get a message. You could also create a user defined function to check for numeric/alpha/lowercase etc.

 

[David R]

Do you absolutely HAVE to store the "Inc #" part in the field itself? Because what I would do, for sanity's sake, is:

1. Strip all the existing records down to the "4526" level.
2. Change your field LABEL to Inc #
3. Add a calculated field to your query: IncidentNumber: "Inc # " & [IncNum]
4. Maybe put an input mask on like jdraw suggested. At the least you can change the field to a Number value, unless leading 0's are important (which you can probably get around with Format()).

Users are inherently lazy. If you can train them that the system will do the text part for them (and then force them to do numeric only, as well), your compliance rates will go way up.

 

[Jon123]

I cant change the table propery to number only so is there an afterupdate code I could run on a field and check for alphanumartic charters ?

jon

 

[WayneRyan]

Jon,

Even if you can't change the properties of the existing table you can clean this up to
make it easier.

1) Keep the text attribute of the field, but strip the data down to just the number --> "4526"

   This will at least get everything consistent.

2) Change the label as David says: --> "INC #"

3) After entry, check for:

   a) a valid number (strip out extraneous "leading stuff").
   b) Use DCount to check for an existing "number"

4) When reporting/displaying:

   "Inc #" & [YourField]

Any attempt at trying to "live with" the user's text cohabitating with your ID field
will surely drive you crazy.

Wayne