max value of text string

[rodrigoturbina]

Dear all, I have a text field with receipt numbers in the format 0001-00000### and I would like my data entry form to default the max existing value + 1.

If max value is 0001-00000201, then the new record should suggest 0001-00000202.

The problem is that if I use the "max" function, it does not work (I think because that function is intended for numbers, not text strings).

Table: RECEIPTS
Field: receiptnum

Does anybody know how to solve it?

I also have a query with just one field that lists only the receiptnum unique values so that I can use them in comboboxes in other forms... it may be useful I guess...

Query: unique-receiptnum
Field name: receiptnum

Thanks so much, regards
Rod

 

[plog]

Discrete data should be stored seperately, and with the correct datatype for what it is for. That means you need 2 fields for this value and they probably both need to be numeric. You want to work with it as a number and to do that, you need to store it as a number. Do that and your issue resolves itself (you can use MAX()).

Now, to jump in front of your objections--you can format any field in any manner you want. Just because its stored as a number doesn't mean when you display it that you can't present it as you have it now. You simply pad it with 0's and concatenate the two fields together. Functionality first, then form.

 

[rodrigoturbina]

Thanks for the prompt answer, however considering that I have it already stored in such a way, and due to the fact that when I sort it Access seems to recognize a min and a max value, I was wondering how does Access do that.

I think I can also try calling the "receiptnum" higher value and "picking" the last 3 digits from the right... and then concatenate it with "0001-0000" so that my expression becomes: "0001-00000XXX". Can I do that?

 

[plog]

Every field has a min and a max value, its just the type that determines how that is determined. In a query the MAX function will work for strings, it just compares them as such:

1
10
2
236
4
411
7

Give your hack a shot, it might work. Just be careful about when they fall outside that range (999 or when the first four characters should become 0002). Again, you should probably store this data correctly from the beginning.

 

[rodrigoturbina]

Unfortunately neither max nor dmax worked... also tried with the right digits from the string without being successful.

I know you are right but I would need to change not only this table, but also 3 more tables and the problem is that I use that number as a key many times. I can not change it now...

Any other ideas??
Thanks