Increment value in a field using a Qry

[chubbychap]

Hi all

My problem is two fold - the first part is the most important, the second is on my "wish list".

My database has data in a number field which I need to increment by 1 digit upon running a query.

Eg - Field1 = 8..............run qry1..........Field 1 = 9

The other part to my problem is as follows. When the digit being incremented gets to be 14, the field needs to be shown as NULL (be deleted).

Eg. - Field1 = 13...........run qry2...........Field1 = NULL

Any help on this would be greatly appreciated.

Chubbychap

 

[jobb]

I guess you could include an "Update Query" where you would update your [Field1] to IIF([Field1]=13,null,[Field1]+1)

 

[chubbychap]

Thanks Jobb - thats fantastic.

 

[dpbarry]

Hmm!!

This is exactly what one of our teachers needs to do but she can;t ge it to work. What is happening is that it is telling her that 6 ready to be updated but when it goes to do it, it says that there are 6 errors - key violations

She seem to think that the problems is somehow to do with the fact that the field 'Year' is in a one to many relationship. We checked the boxes to update related fields but it doesn't work either

So far, stumped

Declan

3 Tables - Formclass, YearGroup & Student

Formclass & YearGroup each form a one to many relationship to student table and the common field is year