increment numbers with text

[Guest]

Need to know how to increment numbers with text in a query. My numbers are
stored as text b/c I have numbers beginning with zeros, such as 0003. This
number is in a particular field stored as text so that I don't loose the
zeros. The following expression gives me errors:
Expr1: DMax("Voucher ID","new Voucher Info","Voucher Type"="E")+1

 

[Rick Brandt]

Need to know how to increment numbers with text in a query. My
numbers are stored as text b/c I have numbers beginning with zeros,
such as 0003. This number is in a particular field stored as text so
that I don't loose the zeros. The following expression gives me
errors:
Expr1: DMax("Voucher ID","new Voucher Info","Voucher Type"="E")+1

You can store those as numbers and *display* leading zeros with a format
property or by using the format function. Otherwise you would have to use
something like...

Expr1: DMax("Val([Voucher ID])","new Voucher Info","Voucher Type"="E")+1

....but that will not be nearly as efficient as if the column were a number field
to begin with. It will force the Val() to be evaluated for every row in the
table to find the Max() value whereas if you used a number field with an index
on it it would be a LOT faster.