Searching for the next number

[sven2]

Hello,

in the table item_stan I have a field "isocode" where some kind of number is stored like:

document isocode
document 1 QW3A1M0001
document 2 QW3A1M0002
document 3 QD3A6T0001

Now I want to search the next number behind the code.

MakeCode = QW3A1M
Searchnumber = ... this code should result in 0003

Another example

makecode = QD3A6T0001
Searchcode = ... the result should be 0002

Who can help me with the code for searchnumber? I am working with access 2003 FE and MS SQL server 2005 BE (adp project)

Thanks in advance,
Sven.

 

[jal]

I don't understand.

 

[jwhite]

Can you better space out the field header row and the records? Add | between them for clarification. The three examples have no consistency in format, except the last four positions which appear to be an incrementing number but repeats?

More information about your format is needed... If you had a [DateAdded] field which is always stamped with Now() when added, you could go by that to find the latest entry and go from there.

 

[sven2]

Hello,

for each stored document there must create a unique number.
First of all we have the text before the number and this text is depending who has made the document.

For example:

Person A has textcode QW3AS
Person B has textcode QD3A6T

Now when person a makes his first document the code should be QW3AS0001. The second document should be QW3AS0002.
And all of this is stored in the table item_stam.

The makecode = the person code.
Now my question is how can I search the maximum number for this person (last 4 digits) and add 1 to this number?

Thanks in advance,
Sven.

 

[Rabbie]

What you need to do is to split the makecode into two parts. First part with the text code like "QW3AS" and the second part is the 4-digit sequence number. Then you can use the DMAX function to find the current highest sequence number for the textcode you are interested in and add 1 to it.

 

[sven2]

Hello,

I have split the code into 2 parts and I have made a SQL that returns the highest value of a particular code.

How can I add 1 to this number?

Dim strSQL as string

strSQL = ... the highest nmner

strSQL = strSQL + 1 isn't working because it is a string ...

How do I solve this.

Thanks in advance,
Sven.

 

[raskew]

Hi -

Don't quite understand why it's not working for you. Here's an example that seems to work without problem:

x = "0010"
y = x + 1
? y
 11 
? format(y, "0000")
0011

However, if that's not working, you can use the val() function to return a numerical value.

x = "0010"
y = val(x) + 1
? y
 11 
? format(y, "0000")
0011

HTH - Bob

 

[Atomic Shrimp]

What you need to do is to split the makecode into two parts. First part with the text code like "QW3AS" and the second part is the 4-digit sequence number. Then you can use the DMAX function to find the current highest sequence number for the textcode you are interested in and add 1 to it.

I agree - I would keep the two parts in two separate fields in the table - saves all this jiggery-pokery every time you want to do something with one half of it. The two separate fields can always be concatenated back together any time they are required that way.

 

[sven2]

Hallo,

if I do it like that:

strSQL = " SELECT TOP 1 PERCENT IsocodeTelling " & _
" FROM Item_stam " & _
" WHERE (NOT (IsocodeTelling IS NULL)) AND (Isocodes = '" & Me.txtcode & "') " & _
" ORDER BY IsocodeTelling DESC "

zoekgetal = Val(strSQL) + 1

The result is always 1 ??

Is there something wrong with the code?

Sven.

 

[gemma-the-husky]

document isocode
document 1 QW3A1M0001
document 2 QW3A1M0002
document 3 QD3A6T0001

what you are tring to do is find the highest isocode number given the first siz characters

so what you need is

searchfor = "QD3A6T"
maxisocode = nz(dmax("isocode","tbldocuments","left(isocde,6) = " & chr(34) & searchfor & chr(34)),vbnullstring)

this will set maxisocode to eg QD3A6T004, if it finds a match, or to blank if it doesnt

then you have to split the number part of the isocode which will be

nextval = clng(right(maxisocde,3))+1 - ie convert the last 3 characters in the string into a number, and add one

then put it all back together

nextisocode = searchfor & format(nextval,"000") 'I think!

so put it all together its

'dim appropriate vartiables then

searchfor = "QD3A6T"
maxisocode = nz(dmax("isocode","tbldocuments","left(isocde,6) = " & chr(34) & searchfor & chr(34)),vbnullstring)
if maxisocode = vbnullstring then
'no previous numbers
 nextisocode = searchfor & "001"
else
 nextval = clng(right(maxisocde,3))+1 
 nextisocode = searchfor & format(nextval,"000") 
end if

now you have a new value for nextisocode ready for you to use
note that this is all dependent on 6 character prefixes, and 3 figure number suffixes.

 

[Atomic Shrimp]

Hallo,

if I do it like that:

strSQL = " SELECT TOP 1 PERCENT IsocodeTelling " & _
" FROM Item_stam " & _
" WHERE (NOT (IsocodeTelling IS NULL)) AND (Isocodes = '" & Me.txtcode & "') " & _
" ORDER BY IsocodeTelling DESC "

zoekgetal = Val(strSQL) + 1

The result is always 1 ??

Is there something wrong with the code?

Sven.

Yes - the query isn't even being run in this code - you're putting the query instructions into a string, then converting that string to a number (result:0 - because it's non-numeric text), then adding one to the result.