Alpha Incrementing

[DS]

I'm using the code below to generate a new salesID number this works
fine except I need to add a letter after the number. How do I get this
to increment automatically as such...
1A
1B
1C
....etc.
Thanks
DS




Me.TxtPatSalesID = Nz(DMax("[SalesID]", "Sales"), 0) + 1

 

[Guest]

Capital letters "A-Z" have ASCII values from 65 to 90. So the easy part is
getting the ASCII value of the letter, adding one, and converting it back to
a character. The harder question is what happens when your existing Max
already ends with "Z"?
So, that part of the formula will be:
Chr(Asc(strTheLetter) + 1)

 

[Stefan Hoffmann]

hi,

I'm using the code below to generate a new salesID number this works
fine except I need to add a letter after the number. How do I get this
to increment automatically as such...
1A
1B
1C
Me.TxtPatSalesID = Nz(DMax("[SalesID]", "Sales"), 0) + 1

TxtPatSalesID \ 26 & Chr((TxtPatSalesID mod 26) + 64)


mfG
--> stefan <--

 

[DS]

hi,

I'm using the code below to generate a new salesID number this works
fine except I need to add a letter after the number. How do I get
this to increment automatically as such...
1A
1B
1C
Me.TxtPatSalesID = Nz(DMax("[SalesID]", "Sales"), 0) + 1

TxtPatSalesID \ 26 & Chr((TxtPatSalesID mod 26) + 64)


mfG
--> stefan <--

Thanks but how or where do I put the second line?
DS

 

[stefan hoffmann]

hi,

Thanks but how or where do I put the second line?

You can use it directly as ControlSource or you can assign it in an
event (VBA, e.g. on current) to a field or control as value.


=[TxtPatSalesID] \ 26 & Chr(([TxtPatSalesID Mod 26) + 64)

or

Me.TxtAlphaField.Value = Me.TxtPatSalesID.Value \ 26 & _
Chr((Me.TxtPatSalesID.Value Mod 26) + 64)

mfG
--> stefan <--

 

[DS]

hi,

Thanks but how or where do I put the second line?

You can use it directly as ControlSource or you can assign it in an
event (VBA, e.g. on current) to a field or control as value.


=[TxtPatSalesID] \ 26 & Chr(([TxtPatSalesID Mod 26) + 64)

or

Me.TxtAlphaField.Value = Me.TxtPatSalesID.Value \ 26 & _
Chr((Me.TxtPatSalesID.Value Mod 26) + 64)

mfG
--> stefan <--

Great, but sorry how does that integrate with the DMax statement?
DS

 

[DS]

hi,

TxtPatSalesID \ 26 & Chr((TxtPatSalesID mod 26) + 64)


Thanks but how or where do I put the second line?

You can use it directly as ControlSource or you can assign it in an
event (VBA, e.g. on current) to a field or control as value.


=[TxtPatSalesID] \ 26 & Chr(([TxtPatSalesID Mod 26) + 64)

or

Me.TxtAlphaField.Value = Me.TxtPatSalesID.Value \ 26 & _
Chr((Me.TxtPatSalesID.Value Mod 26) + 64)

mfG
--> stefan <--

Great, but sorry how does that integrate with the DMax statement?
DS

Ok I kind of have it working. The problem is that, well better yet
heres where I need to end up... Lets say I open a Table, Table Number 2,
I need to add a check to that making it Table 2A if I add another I need
to make it 2C...so on. The way the code works now if its one the code
returns A, if its 3, the code returns C..... I hope this is clearer?
Thanks for the help.
DS

 

[Stefan Hoffmann]

hi,

=[TxtPatSalesID] \ 26 & Chr(([TxtPatSalesID Mod 26) + 64)
Me.TxtAlphaField.Value = Me.TxtPatSalesID.Value \ 26 & _
Chr((Me.TxtPatSalesID.Value Mod 26) + 64)

Great, but sorry how does that integrate with the DMax statement?

Ok I kind of have it working. The problem is that, well better yet
heres where I need to end up... Lets say I open a Table, Table Number 2,
I need to add a check to that making it Table 2A if I add another I need
to make it 2C...so on. The way the code works now if its one the code
returns A, if its 3, the code returns C..... I hope this is clearer?

Can you post your code to clarify your purpose?


mfG
--> stefan <--