You are here:

Using MS Access/DMAX for sequential numbering

Advertisement


Question
QUESTION: Hi Scott,

Getting VB error messages.  Used both examples by cutting and pasting directly into VB between  Private Sub Form_BeforeInsert(Cancel As Integer) and Me.Dirty=False
End Sub.

CODE 1:  Me.txtOrd_Seq = Nz(DMax("[Ord_Seq]", "tblSF120", "[DateSubmitted] = #" & Me.txtDateSubmitted & "#"), 0) + 1

Microsoft Visual Basic winodow says: Run-time error '3075': Syntax error in date in query expression '[DateSubmitted]= #'.
Running Debug button highlights whole line of above code #1 in yellow.

Code 2:  Microsoft Visual Basic window says: Run-time error '94': Invalid use of Null
Debug highglights line of code in yellow
Me.txtOrd_Seq = Nz(DMax("[Ord_Seq]", "tblorders", "DateValue([DateSubmitted]) = #" & DateValue(Me.txtDateSubmitted) & "#"), 0) + 1

I have suspicions that using the built in microsoft calendar control date picker is producing the errors.  Would it be better to change DateSubmitted to a default value of =Date() since the record should reflect insertion of the today's date [not past or future date]. Also simpler for user since today's date is autofilled for them.

ANSWER: Yes, using a default would be better. But using the Date Picker should still only return the date.

Do you know how to use the immediate window?

Try seeing first if This works:

? DMax("[Ord_Seq]", "tblSF120")

If that works try these:

? DMax("[Ord_Seq]", "tblSF120", "[DateSubmitted] = xx/xx/xxxx")
? DMax("[Ord_Seq]", "tblSF120", "[DateSubmitted] = #xx/xx/xxxx#")

Where xx/xx/xxxx is a date that you know is in the table.

let me know

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: Hi Scott,

Copied the table over with structure only.  Entered 4 records from table with dates:
DateSubmitted 9/20/2013  ==> Ord_Seq 001  >> ID=1
9/16/2013  ==> 002  >> ID=2
9/16/2013  ==> 003  >> ID=3
9/19/2013  ==> 004  >> ID=4


Ord_Seq was assigned sequentially 001, 002, 003, and 004 irregardless of DateSubmitted.

I ran the DMax statements in the immediate window with this DMax code:
Me.txtOrd_Seq = Nz(DMax("[Ord_Seq]", "tblSF120", "[DateSubmitted] = #" & Me.txtDateSubmitted & "#"), 0) + 1

? DMax("[Ord_Seq]", "tblSF120")
4
Yes, 4 was highest number.

? DMax("[Ord_Seq]", "tblSF120", "[DateSubmitted] = 9/16/2013")
Null

? DMax("[Ord_Seq]", "tblSF120", "[DateSubmitted] = 9/19/2013")
Null

? DMax("[Ord_Seq]", "tblSF120", "[DateSubmitted] = 9/20/2013")
Null

Null for all 3 dates.

? DMax("[Ord_Seq]", "tblSF120", "[DateSubmitted] = #9/16/2013#")
3

? DMax("[Ord_Seq]", "tblSF120", "[DateSubmitted] = #9/16/2013#")
3

? DMax("[Ord_Seq]", "tblSF120", "[DateSubmitted] = #9/19/2013#")
4

? DMax("[Ord_Seq]", "tblSF120", "[DateSubmitted] = #9/20/2013#")
1

2 never showed up for the second 9/16/2013.

Hopefully these results help with a diagnosis.  Thank you for your help & patience.

ANSWER: OK, The results are as expected.

This works:
DMax("[Ord_Seq]", "tblSF120", "[DateSubmitted] = #9/16/2013#")

because You have identified the literal date string as a date by using the #.

This doesn't work:
DMax("[Ord_Seq]", "tblSF120", "[DateSubmitted] = 9/19/2013")

because Access doesn't know that that the literal is a date, so returns no records.

So this:
Nz(DMax("[Ord_Seq]", "tblSF120", "[DateSubmitted] = #" & Me.txtDateSubmitted & "#"), 0) + 1

should work. It should return the next sequential number if the txtDateSubmitted contains a valid date value for a record that already exists. or a 1, if there are no matching dates or its an invalid date value.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

RunTimeError3075andCode
RunTimeError3075andCod  

RunTImeErrorShowsNull
RunTImeErrorShowsNull  
QUESTION: Hello Scott,

I copied and use the line of code exactly:  Nz(DMax("[Ord_Seq]", "tblSF120", "[DateSubmitted] = #" & Me.txtDateSubmitted & "#"), 0) + 1

When I open form and pick DateSubmitted for today's date,immediately Error 3075 appears. Attached are 2 screen shots so you can see. Been looking at code so long I must not be seeing something like a typo or ??? in the VB screen.

No data is sent to table when entered into form.  Thanks for your help!!!

Answer
Something is wrong with Me.txtDateSubmitted. So first are you sure that is the correct name for the control where the date is entered? Second, If it is. Then before the DMax line put:

MsgBox "You entered " & Me.txtDateSubmitted

This will show you what's actually in the control.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.