Validation rules

[crisp]

i have field containing data in this format:
LL-000-00
So any data inputted whoud look similar to this:
AA-123-45

To validate the data that he user inputs i whoud like to use the last two digits to check the first 2 sets of data.

for example:

Take 'AA' and get the ASCII code take the first digit from the code and compare it to the first validation number.

And add '123' and take the first digit to be compared to the last digit of the validation code.

Please help its for my a-level coursework.
Or if u can think of a better way to validate it tell me.

Thanks for reading this.

 

[crisp]

If i cant get this done im gonna have to spend days re designing the forms, tables and qrys. Is it actually posable is there any point in me continuing to try?

 

[neileg]

You can't do this with table level validation.

If you use a form to enter your data then you can do the validation in code, perhaps in the Before Update event.

Alternatively, you could allow the record to be saved, and then run a query that checks the validation and deletes the record if it fails.

 

[crisp]

i got that far the other day after i spent a few hours looking round access help. anybody got any ideas on the code id have to write to impliment this kind of validation?

Thanks Crisp

 

[crisp]

Ok ive got this as a condition to be entered into a macro that checks the field when the user exits it.

=(MOD(CODE(LEFT( [Stock_TBL]![Stock_ID] ,1))+CODE(MID( [Stock_TBL]![Stock_ID] ,2,1)),10)=VALUE(MID( [Stock_TBL]![Stock_ID] ,8,1)))

This shoud check the two letters against the second last number of the input, however i get a message saying that access cannot parse the expression.

i want it so that if the two parts dont match a diolouge box opens with an error mesage.

 

[neileg]

Sorry, I don't use macros, just code. However, your statement has two equals signs in it. That makes no sense in any environment, so that has to be the place to start. The condition itself looks OK.

I would have been using an IF construction. IF validation OK THEN save record ELSE show warning and request re-input.

 

[crisp]

i did it!

once i figure out how i did it ill post it here incase anyone else ever needs to us it.

 

[Mile-O]

=(MOD(CODE(LEFT( [Stock_TBL]![Stock_ID] ,1))+CODE(MID( [Stock_TBL]![Stock_ID] ,2,1)),10)=VALUE(MID( [Stock_TBL]![Stock_ID] ,8,1)))

That looks very Excel in nature; with, however, database fields.

 

[crisp]

Yeh i built it in excell nd then i swapped the cells for fields in the database and changed the comands for the access equivalents. instead of code, asc (or sumthin like that). nd there was no access function for mod so it had to be calculated manualy.

I did have one other problem, im guessin that theres a 256 chr limit on expressions coz i ran out of room and had to split the expression in the end.

 

[Mile-O]

there was no access function for mod

There isn't a function as you would just calculate the modulus mathematically by using the Mod operator.

i.e. =5 Mod 2

 

[crisp]

i never even thaught of lookin for mod in operators, i guessed it wud be a function if it was there.

Thanks! I can fit it in in less that 256 chrs now!

 

[crisp]

Im really geting sick of trying to get this working!

if i type "130 mod 10" i get 0 which is waht i expect to get.

if i type "Asc(Left([Forms]![Stock_TBL]![Stock_ID],1))+Asc(Mid([Forms]![Stock_TBL]![Stock_ID],2,1))" i get 130 which is waht i expected.

if i combine the two to get "Asc(Left([Forms]![Stock_TBL]![Stock_ID],1))+Asc(Mid([Forms]![Stock_TBL]![Stock_ID],2,1)) Mod 10" i get 70 WHY?

whats going on, im completly lost now.

(the expression is operating using this data - "AA-111-111" and the ascii of A+A = 130)