Incrementing Invoice Id

[Guest]

Hi guys,

I am trying to create a function and query that will increment my invoice #
field. The invoice # field unfortunately can contain alphabetic characters
in it. I have created a function that strips all the alph chars out of my
invoice # field. Then I have a query that gets the max number in the invoice
table. However, its not working correctly. I have for the numbers only, the
highest value is 2019, and the highest alpha-numeric value is FC 9.

The query I have created seems to return '9' as the maximum number in this
result, it should return 2019, meaning it still returns FC 9 as the maximum
invoice #..

This is the query I have built:

SELECT max(StripAlpha(nz(RefNumber,'')) as _RefNumber
from Invoice
where Len(RefNumber ) > 0

I have also tried creating a query that just strips the alpha from the
RefNumber, and tried to use a max query on that, it returned the same results

Any suggestions would be super.

thanks

 

[Jeff Boyce]

Wouldn't that really depend on what the function does?

Jeff Boyce
<Office/Access MVP>

 

[Boyd \Hi Tech Coach\ via AccessMonster.com]

YOu did not supply the code to the StripAlpha function so we can not verifiy
that it is working properly. I would gusess that is where the problem is.

Hi guys,

I am trying to create a function and query that will increment my invoice #
field. The invoice # field unfortunately can contain alphabetic characters
in it. I have created a function that strips all the alph chars out of my
invoice # field. Then I have a query that gets the max number in the invoice
table. However, its not working correctly. I have for the numbers only, the
highest value is 2019, and the highest alpha-numeric value is FC 9.

The query I have created seems to return '9' as the maximum number in this
result, it should return 2019, meaning it still returns FC 9 as the maximum
invoice #..

This is the query I have built:

SELECT max(StripAlpha(nz(RefNumber,'')) as _RefNumber
from Invoice
where Len(RefNumber ) > 0

I have also tried creating a query that just strips the alpha from the
RefNumber, and tried to use a max query on that, it returned the same results

Any suggestions would be super.

thanks

--
Boyd
Hi Tech Coach
http://www.hitechcoach.com


Message posted via AccessMonster.com

 

[John Spencer]

Your function is probably returning a string. As a string "9" is larger
than "2019"

Try

Max(Val(StripAlpha([RefNumber])))

Additionally, you seem to be storing two types of information in one field.
You apparently have a one-up sequence number and some kind of prefix value
identifying somethig about the invoice (purchase type, customer code, ???).
You would probably be better off storing these in two fields and then
combining the two values where needed for display purposes.

Also, IF your RefNumber structure is consistently formatted, you might be
able to use something simpler than the StripAlpha Function to return the
Max.

Your alternative query may be the solution to that. Again, I suspect that
you need tro transform the string returned to a number using the Val
function or one of the conversion functions such as CLng.

 

[Guest]

Bingo, you win the prize. Thanks so much.

Your function is probably returning a string. As a string "9" is larger
than "2019"

Try

Max(Val(StripAlpha([RefNumber])))

Additionally, you seem to be storing two types of information in one field.
You apparently have a one-up sequence number and some kind of prefix value
identifying somethig about the invoice (purchase type, customer code, ???).
You would probably be better off storing these in two fields and then
combining the two values where needed for display purposes.

Also, IF your RefNumber structure is consistently formatted, you might be
able to use something simpler than the StripAlpha Function to return the
Max.

Your alternative query may be the solution to that. Again, I suspect that
you need tro transform the string returned to a number using the Val
function or one of the conversion functions such as CLng.

Hi guys,

I am trying to create a function and query that will increment my invoice
#
field. The invoice # field unfortunately can contain alphabetic
characters
in it. I have created a function that strips all the alph chars out of my
invoice # field. Then I have a query that gets the max number in the
invoice
table. However, its not working correctly. I have for the numbers only,
the
highest value is 2019, and the highest alpha-numeric value is FC 9.

The query I have created seems to return '9' as the maximum number in this
result, it should return 2019, meaning it still returns FC 9 as the
maximum
invoice #..

This is the query I have built:

SELECT max(StripAlpha(nz(RefNumber,'')) as _RefNumber
from Invoice
where Len(RefNumber ) > 0

I have also tried creating a query that just strips the alpha from the
RefNumber, and tried to use a max query on that, it returned the same
results

Any suggestions would be super.

thanks