March 25, 2003 at 1:27 pm
Hello,
I have a situation in which database users input data into a SQL Server 2000 database table via an Access 2000 Project form. Per the users request, I set up something called 'AutoTab' to make it easier for them to navigate through the form as they input records.
60 Of the fields can take one of six static values, one of which the user puts in as 'N'. This 'N' value represents 'Not Applicable'. The convention used for this kind of input might usually be 'NA', but I had to use 'N' to accomodate the AutoTab feature in the Access Project form.
The users want an 'NA' result to appear on the Project Reports when they enter 'N' on the form. What the form might show after they enter 'N' does not matter, so long as they can just enter an 'N' on the form and have AutoTab still work for them.
In order to grant this request, could I set up a CHECK Constraint at the table level that would somehow capture any attempt to enter 'N' and store it into any given field as 'NA', or would this be better suited for a Trigger? In any event, am I going to have to address all 60 fields?
What kind of code would be best to use in the CHECK Constraint/Trigger? An UPDATE statement, or maybe CASE statement?
I attempted to create a table level CHECK Constraint, just to try something, and got a syntax error. The following code attempts to apply a table level check constraint, and addresses two of the 60 fields:
******************************************
ALTER TABLE tblRCStudentGrades
ADD CONSTRAINT CN_tblRCStudentGrades_for_N_input
CHECK
(CASE
When ST1Q1 = 'N' then 'NA'
When ST1Q2 = 'N' then 'NA'
END)
*******************************************
Do you have any ideas on how I might approach this?
Thanks.
CSDunn
March 25, 2003 at 1:57 pm
you could use a trigger with the CASE sentence.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply