how to enforce a particular rule for data entry

[vinayak]

I am building a Production scheduling database in which all the
workorders have to be entered. I want to ensure that all the people who
are entering the workorders follow a similar system of entering. The
workordernumber should be in the form of "316\12\05". How do I ensure
that people use the backslash instead of forwardslash when entering.
The main problem I face when people use the forward slash is when the
data gets exported to excel all the workorder numbers get transferred
into date format in excel. Can anybody help me.

 

[TC]

An easy way to do what you've asked for, would be to set the
ValidationRule property of the relevant field in the table, to this:

Like "[0-9][0-9][0-9][\][0-9][0-9][\][0-9][0-9]"

That means: "The value must be 3 decimal digits, a backslash, two more
decimal digits, another backslash, and two final decimal digits".

You should also set the ValidationText property to the error message
that you want the users to see. For example: "Please enter this field
in the following format: 999\99\99".

However: personally, I say it is unwise to force the users to enter the
data like that. If you're having trouble with an Excel export, the
sensible approach would be to focus on fixing the problem with the exel
export.

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[vinayak]

Since my database is going to be multiuser I feel it will be better that one
rule is enforced otherwise problems are occuring when doing data entry. The
workordernumber is a primary key field and when two people type in the same
number but with different slashes it stores the data which I do not want it
to happen.

 

[Tim Ferguson]

The
workordernumber should be in the form of "316\12\05". How do I ensure
that people use the backslash instead of forwardslash when entering.

An alternative approach would be to avoid the problem altogether and put
the thing into first normal form at the same time...

Use three separate fields called something like SerialNumber, MonthNum,
and YearNum. Each of these can be validated very easily. When you need to
present the things on a form or report you can just set a controlsource
to

= format(SerialNumber,"000") & _
format(MonthNum, "\\00") & _
format(YearNum, "\\00")

For user input, you can set the text boxes next to each other, with
labels for the backslashes so that it looks right. This was absolutely
standard practice in the old green-screen days, but it seems that in the
age of the GUI we have lost the ability to use the input screen to
control user input intelligently.

For an excel export, one way to prevent it being interpreted as a date
would be to prefix it with an apostrophe:

SELECT format(SerialNumber,"\'000") &
format(MonthNum, "\\00") &
format(YearNum, "\\00") AS MyPrimaryKey,
OtherField,
OtherOtherField,
etc
FROM etc


Hope that helps


Tim F

 

[John Vinson]

I am building a Production scheduling database in which all the
workorders have to be entered. I want to ensure that all the people who
are entering the workorders follow a similar system of entering. The
workordernumber should be in the form of "316\12\05". How do I ensure
that people use the backslash instead of forwardslash when entering.
The main problem I face when people use the forward slash is when the
data gets exported to excel all the workorder numbers get transferred
into date format in excel. Can anybody help me.

This may be one of the cases where an Input Mask would be appropriate.
If you were to use an input mask

"000\\00\\00;0;_"

it would fill in the backslashes automatically (though if the user
typed them it wouldn't hurt), store them in the field, and prohibit
entry of forward slashes or any other characters.

John W. Vinson[MVP]

 

[vinayak]

Thanks for all the response it really helped.

This may be one of the cases where an Input Mask would be appropriate.
If you were to use an input mask

"000\\00\\00;0;_"

it would fill in the backslashes automatically (though if the user
typed them it wouldn't hurt), store them in the field, and prohibit
entry of forward slashes or any other characters.

John W. Vinson[MVP]

 

[Nancy H.]

I also have a question, sorry if this is the wrong area to post.

I am looking for an answer to a MS Access question.



Field Heading name is CertNo. (First Heading). I need to add an expression
builder, in the design view that would continue the certification number.
Example: 061247601, 061247602, 061247603.

I have a total of 500 certificates.


If the expression builder is not the best way to complete this problem,
please let me know of an easier way.

Thank you.