Specific numbering

[NightFeeder]

I have a tblcall, the primary key (keyCallID) is autonumber, which I do not want to change. For faster search purpose.

I have fields CallDate and CallNumber, which consists of today's date and the n-th number of todays call. E.g. 01-01-01 up to 01-01-80 (there where 80 calls on the first of Jan.) On the 2nd of Jan the the counter must reset to 1 thus 01-02-01...01-02-90 (90 calls on the second of Jan.)
How will I go about to set the CallNumber (only display 1-80)for CallDate(01-01-2002)? This process should be automatic each time a new call is loged.
Thanx.

 

[scriverb]

Use this code in to generate the next CallNumber. It will generate a complete number mm-dd-xx(xx = 01 thru ??). It also generates just the Call number as an integer. You will have to adapt it to your form but the essence of what you need is there. For testing/demo I created a small form with a button for the code and two text boxes. One called NewCallnumber and CallNumberOnly. Put the following code behind the button in the OnClick procedure for testing:

Dim db As Database
Dim rs As Recordset
Dim vNewCallNumber As String
Dim vCallNumberOnly As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("tblCalls", dbOpenDynaset)
rs.MoveLast
If rs("CallDate") = Date Then
  vNewCallNumber = Format(DatePart("m", Date), "00") & "-"  & Format(DatePart("d", Date), "00") & "-" & Format(CInt(Right$(rs("CallNumber"), 2)) + 1, "00")
Else
  vNewCallNumber = Format(DatePart("m", Date), "00") & "-" & Format(DatePart("d", Date), "00") & "-" & "01"
End If
vCallNumberOnly = CInt(Right$(vNewCallNumber, 2))
Me![NewCallNumber] = vNewCallNumber
Me![CallNumberOnly] = vCallNumberOnly

I think this will help you in your situation.

Bob Scriver