Problem?

  • Thread starter Thread starter David Whitaker
  • Start date Start date
D

David Whitaker

I have got a form that I am populating a textbox with the following when a
new record is started.

Forms!cpurchase.po = DMax("[po]", "clist") + 1

basicly what it is doing is generating an automatic p.o. number. Right now I
am trying it and it does fine on p.o.'s 1-9, but when it gets to 10 it will
keep using the same number over and over "10".
I can go into the table and see 10 over and over in each record, I can then
delete the records back to whatever number below 10 and open the form and
the same thing happens when I get to 10.
Any Solutions Appreciated!!!
 
David Whitaker said:
I have got a form that I am populating a textbox with the following
when a new record is started.

Forms!cpurchase.po = DMax("[po]", "clist") + 1

basicly what it is doing is generating an automatic p.o. number.
Right now I am trying it and it does fine on p.o.'s 1-9, but when it
gets to 10 it will keep using the same number over and over "10".
I can go into the table and see 10 over and over in each record, I
can then delete the records back to whatever number below 10 and open
the form and the same thing happens when I get to 10.
Any Solutions Appreciated!!!
Click to expand...

I suspect that the [po] field in the table is defined as text, not a
number. In comparing text field, "9" is greater than "10", because the
first character is lexicographically greater. If you want to keep using
a text field, you can do one of two things:

(A) Make sure you zero-fill the PO numbers as you create them so
that they are all the same length. That way DMax() won't be comparing
"9" to "10", it'll be comparing (maybe) "0000009" to "0000010".

OR

(B) Use an expression in the DMax call to convert the text value of
PO. This will not be nearly as efficient, but you could do it like
this:

DMax("CLng(po)", "clist") + 1

or like this

DMax("po * 1", "clist") + 1
 
Back
Top