using NZ() function to return ''0'' value when query is null

[Jean-Francois Gauthier]

Hi folks,

I have a form called tbldocinput that contains a subform countrack. The
form countrack is behind a query also called countrack that will provide me
with the MAX() of the value of a tracking number that I am trying to create
and autogenerate.

I want the user to input the data on the tbldocinput form which is
essentially a data entry form. When the user clicks save, the countrack is
requery as well as a unbound field on the form tbldocinput that is default to
0.

If there is a value in the countrack form field dtrack, then I want to use
that value to input in my tracking number.

Here is my SQL:

Private Sub cmdsave_Click()
On Error GoTo Err_cmdsave_Click

Me.countrack.Requery
Me.trcount.Requery
Me.trcount1.Requery


If Nz(Me.countrack.Form.dtrack, 0) = 0 Then
Me.Track = Left(Me.Dept, 1) & Format(Me.DateEntered, "yymmdd") &
Format(Me.trcount + 1, "000")
End If
If Nz(Me.countrack.Form.dtrack, 0) > 0 Then
Me.Track = Left(Me.Dept, 1) & Format(Me.DateEntered, "yymmdd") &
Format(Me.countrack.Form.dtrack + 1, "000")
End If
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_cmdsave_Click:
Exit Sub

Err_cmdsave_Click:
MsgBox Err.Description
Resume Exit_cmdsave_Click

End Sub

The goal here is to have a tracking number generated by the first letter of
the department, the date of entry (date()) and the MAX value of the last 3
digits of dtrack (which is essentially the last 3 values of the max tracking
number in the table corresponding to a date).

So the tracking number will be as such F071511000 when there is no matching
data in the query (query is null) or will be equal to the max value of dtrack
in countrack + 1 or F071511001 at the end of the day.

Basically what we want is to have a tracking number that is based on the
department, date of entry and the last few digits which would be an
increasing number that defaults back to 0 when the first record of that day
is entered.

Hope this is clear, otherwise if you have any questions, please let me know.

 

[Jean-Francois Gauthier]

Nevermind, I ended up fixing my issue.