Code Generator

[fpmsi]

Looking to be pushed in the right direction for creating a code generator for a file after import.

The code is made up of 3 parts: (900-000000-0099)
900 = Client Code - would be taken from a lookup table
000000 = month and year of import (082011)
0099 = numerical sequence (needs to be the next sequence number in line from the table.



Thanks


Ryan.

[Bob McClellan]

[IMG]Y:\Temp\immediate.jpg[/IMG]Function test() As String
'The code is made up of 3 parts: (900-000000-0099)
'900 = Client Code - would be taken from a lookup table
'000000 = month and year of import (082011)
'0099 = numerical sequence (needs to be the next sequence number in line from the table.



Dim p1 As String, p2 As String, p3 As String

p1 = CStr(900)

test = p1

p2 = CStr(Month(Date))
If Len(p2) = 1 Then
p2 = "0" & p2
End If

p2 = RTrim(p2) & CStr(Year(Date))

test = RTrim(test) & RTrim(p2)


'p3 = 'here, create a function that gets the max(PK) number from your table & set p3 = that number + 1
'I will use the 0099 to finish the string...

p3 = CStr(99)

test = RTrim(test) & RTrim(p3)


End Function
'----------------
from immediate window...
this is returned..
? test
90009201199

hth,
..bob