Nz and DMax

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to find the Max number in a table txtGlucometer# field and then add 1
to it the new number is entered in the txtGlucometer# for the person. I am
using this code

Private Sub txtNumberNeeded_AfterUpdate()
Dim NewID As String
Dim OldID As String
If txtNumberNeeded = True Then
OldID = Nz(DMax("txtGlucometer#", "tblGlucometer"), "0")
NewID = Format(OldID + 1)
Me.txtGlucometer# = NewID
ElseIf txtNumberNeeded = False Then
Me.txtGlucometer# = "0"
End If
End Sub
But it is getting hung up on the OldID line. Can someone help me out, I
don't have access to my books and need to get this done. Thanks Fay
 
One problem is that NewID and OldID are strings. You can't add 1 to a
string and have it add like math. Also, if txtGlucometer# is a text
datatype, finding the DMax may not return the right number. Sorting text is
different that sorting numbers. For instance, the numbers 1-10 as text
would sort: 1, 10, 2, 3, 4, 5, 6, 7, 8, 9.

Since you are doing math on these numbers, they should be of the Long
Integer datatype, rather than text. This will likely solve most of the
problems.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
1. Beware of using DMax on a text column. You should consider changing it s
datatype to a long integer.
2. Beware of using # in column names. If you must use them, the column
name must be enclosed in [].

Hope THis Helps
Gerald Stanley MCSD
 
I am getting closer but everytime I click the chkNumberNeeded check box I
just get a one and the number doesn't progress when the next persons checkbox
is checked. But at least I'm not getting any debug errors. Here is the
current code. Should there be another argument after the "tblGlucometer" in
the OldID line?

Private Sub chkNumberNeeded_AfterUpdate()
Dim NewID As Integer
Dim OldID As Integer
If chkNumberNeeded = True Then
OldID = Nz(DMax("txtGlucometerNu", "tblGlucometer"), "0000")
NewID = Format(OldID + 1)
Me.txtGlucometerNu = NewID
ElseIf chkNumberNeeded = False Then
IsNull (Me.txtGlucometerNu)
End If
End Sub

Thank you. Fay
 
Back
Top