Concatanation

[home]

I have a question,

I want to build a JobNo made up from the YY part of the date on my form then
with a "/" and then the month followed by a counter field.
Something like this

03/11698 (03 = Year, /, 11= month, 698 counter)

Any ideas please

Thanks

[Jim Allensworth]

Try this..
Format(Date(),"yy/mm") & 698

Substitute your form references for Date() and of course the counter
field.

- Jim

On Mon, 24 Nov 2003 20:15:29 +0200, "home" <re**@telkomsa.net> wrote:

I have a question,

I want to build a JobNo made up from the YY part of the date on my form then
with a "/" and then the month followed by a counter field.
Something like this

03/11698 (03 = Year, /, 11= month, 698 counter)

Any ideas please

Thanks

[Trevor Best]

On Mon, 24 Nov 2003 20:15:29 +0200 in comp.databases.ms-access, "home"
<re**@telkomsa.net> wrote:

I have a question,

I want to build a JobNo made up from the YY part of the date on my form then
with a "/" and then the month followed by a counter field.
Something like this

03/11698 (03 = Year, /, 11= month, 698 counter)

Any ideas please

The Easy bit:

Sub Form_BeforeUpdate(Cancel As Integer)
' or could be BeforeInsert, if you like
If IsNull(Me!JobNo ) Then
Me!JobNo = format(JobDate,"YY/MM") & GetCounter(JobDate)
End If
End Sub

The hard bit: working out a counter.
(warning: air code - untested)

Function GetCounter(pDate As Date)
' assumes a table called "tblJobNo" with fields:-
' YearMonth : Text 5 - Primary Key
' TheCounter: Long Integer

' also assumes DAO.

Dim rs as recordset, db as database, i as long
set db = currentdb()
' next line may wrap in newsreader
set rs = db.openrecordset("select * from tblJobNo where YearMonth =
'" & Format(pDate,"YY/MM") & "'")
with rs
if .eof then
.addnew
!YearMonth = Format(pDate,"YY/MM")
i=0
else
i = !theCounter
.Edit
end if
i = i + 1
!TheCounter = i
.update
.close
end with
set rs=nothing
set db =nothing
GetCounter = i
' maybe GetCounter = Format(i,"000")
End Function

Add error trapping to suit. Also some locking options on OpenRecordset
method (dbDenyRead or dbDenyWrite but I forget which parameter). ADO
would require a slight difference in the opening of the recordset but
the rest of the code would be the same.
--
A)bort, R)etry, I)nfluence with large hammer.