Increment value in lookup Field

[Rhemo]

Is it possible to run an update query to increment the value in a lookup field on all records, if the values are not numerical?
For example if the look up field contains Unit A, Unit B etc? can a query be executed so that anything with Unit A will be set to Unit B, and Unit B records will be set to Unit C?



Many thanks.

[RuralGuy]

You would probably need to create your own User Defined Function (UDF) in VBA to accomplish that. What are all of the actual values in this particular field and what is causing you to need to increment this value? You may have a design issue that needs to be reviewed.

[Rhemo]

The DB is for student lockers. The field in question contains the Year they are in, 1st Year, 2nd Year, 3rd...and so on up to 6th Year. I put these into a lookup field so it would just be a matter of using a drop down menu to select them. Being slightly hungover I just realised that these values would need to be increased once a year. I already have a delete query to remove old useless records, but require these values to just increment one position. I think it may just be easier to forget the lookup field and allow the user to manually enter this as a regular numerical character then use a query to increment?

[RuralGuy]

It sounds like it should be two different fields. One for the locker value and one for the "year they are in". Did I miss something?

[Rhemo]

The locker number never changes they are static. The Student however will move from Year 1 to Year 2 etc each year. This is the only value that needs to increment. I think I just made the mistake of making this a lookup field with defined values for ease of use. I'll remove this and leave this field to be manually entered in straight numerical values which should be easier for updating.

[RuralGuy]

Excellent!