VBA code to find the max number after /

[aman]

The reference automatically gets generated when the user press Save button. It is in the format as below:

Initials (2 letters)
Date (ddmmyy)
/
UniqueNo

e.g CK140416/121

I am looking for VBA code that will check the field "DocCode" values and let me know the next number after "/". It will be max number after / in the field in the table. so if it is 121 in the table then it should give me 122 so that for the next record it should save CK140416/122

Any help will be much appreciated.

Thanks

 

[plog]

Sounds like you aren't storing your data correctly. You need to store discrete pieces of data, discretely. Each value should have its own field. So if a reference is made up of 3 different part, each part should have its own field.

You do that, and this becomes a lot easier--justa DMax call (http://www.techonthenet.com/access/functions/domain/dmax.php).

 

[Ranman256]

in a query, Q1, pull the values and a calculated field:
Num:mid([DocCode],instr([DocCode],"/")+1)

then get the max from that query: select max(Num) from Q1
or
in vba code, vNum = DMax("[num],"Q1")

 

[aman]

Will try it today. thanks

 

[arnelgp]

are there special meanings on those numbers in the DocCode field? ie:

CK-meaning

140416
| | +---year
| +------month
+-----------day

 

[aman]

1. The first two letters are initials that will be captures automatically using VBA.
2. The next two digits for Day
3. The two digits after are for Month
4. The last two digits before "/" sign are for year
5. After "/" sign is the next number from the field DocCode in the table.

I hope its clear now. Can anyone help me with the VBA code? I will try Ranman suggestion now. Many Thanks

 

[CJ_London]

Ranmans solution is the way to go, but to get the right max you might need to convert the returned value to a number because a string is returned

in numbers
1
2
12

12 is the max value, but in text, 2 is the max value

so

Num:val(mid([DocCode],instr([DocCode],"/")+1))



not tried it but you might be able to combine into one line and not require the query

mycontrol=dmax("val(mid([DocCode],instr([DocCode],'/'))","myTable")+1

 

[arnelgp]

you should have a flexible query since each day the sequence is changing, like today the sequence is 150616/xxx, next day 160616/xxx.

ok good luck.

the condition should be:

Where DocCode Like "'" & [initial] & Format(Date(),"ddmmyy") & "*'"

 

[aman]

CJ_London, I am getting the runtime error "3075"
Missing ),], or item in query expression

 

[aman]

getting Runtime error "2465" Can't find the field '|1' referred to in your expression at red font line.

 Private Sub Form_Load()
 Dim i As Integer
Dim j As Integer
Dim a As String
 i = DMax("ID", "tbl_Mas")
 MsgBox "Max ID=" & i
 a = DLookup("DocCode", "tbl_Mas", "ID=" & i)
 MsgBox "Max DocCode=" & a
 [COLOR=red]j = Val(Mid([DocCode], InStr([DocCode], "/") + 1))[/COLOR]
 MsgBox "Last digits code=" & j
 End Sub

 

[aman]

If I change the red font line of code with below then it gives me "Last digits code=0"

 j = Val(Mid("[DocCode]", InStr("[DocCode]", "/") + 1))

 

[aman]

Thanks guys for your help. All working.