Calculated Field - Trying to use Chr, what can be used instead?

[ScoobyMug]

I have a calculated field where I am trying to increment the string value by 1.


Example if the field is 'B', then I want the value to be 'C'. Or if the field is 'L', then the new value would be 'M', and so forth.
If I try to use the function 'chr', access gives me the error that "The expression cannot be used in a calculated column" ?
IIf(IsNull([TestSuffixId]),'A',chr(Asc([TestSuffixId]) + 1))

[Minty]

You can't use any complex expression in a calculated table field.
I doubt that what your trying to achieve is best suited to a calculated field to be honest.

Maybe show us your data and describe what you are trying to end up with and why?

[ScoobyMug]

thank you for your reply: Let me try to explain:
I need a field in my table that starts with the letter 'A' and then increment by one letter(B,C,D,ect) according based on a condition.

[Minty]

If you can calculate it then I wouldn't store it, simply use a query to provide the result you need.
Storing it means having to update it when one of its dependant values is changed anywhere, and leads to a world of pain.

[Micron]

What are you going to do when you get to Z? You repeated what you want to achieve, but not why, and not much about what you're doing (i.e. where this fits into the scheme).
You might want to rethink this http://allenbrowne.com/casu-14.html

[June7]

Generating custom unique identifier is a common topic. Here is one discussion https://www.accessforums.net/showthread.php?t=23329

[davegri]

Below will work if testsuffixID is a textbox on a form.
Then perhaps you could run an update query to put testsuffixID into your table. (or an INSERT query if this is adding a record to the table).

Option Compare Database
Option Explicit


Private Sub Command2_Click()
    testsuffixID = IIf(IsNull(testsuffixID), "A", Chr(Asc(testsuffixID) + 1))
End Sub