DMax/DCount not incrementing

[testdba]

I know I know, another Dmax/Dcount question. I didn't want to ask it, but I just can't figure out why my Dmax/Dcount statement won't increment. Every time the string is combined I get a value of 1 from Dmax/Dcount. I have tried both Dmax and Dcount and I get the same results from them both.

Maybe I don't understand the functions, but I just need it to count up the number of records that have today's date and then add 1 to that number. If the function count's up seven records, then the number needs to be eight. I am on the right track, aren't I?

Private Sub Form_BeforeInsert(Cancel As Integer)
    If IsNull(Me!InvoiceNumber) Then
    
        Dim strNextAvailable As String
        Dim strINV As String
        
        'Count the number of records for the current date and add 1 to it
        strNextAvailable = 1 + Nz(DCount("[INVNumberForDate]", "tblInvoice", "[InvoiceDate] = 'Date'"), 0)
      
        'Format strNextAvailable to have proper number of digits. This gives proper spacing for
        'up to 9999 orders in a day
        strNextAvailable = Format(strNextAvailable, "0000")
                                    
        'Combine all to generate unique invoice number based on number of entried for a specific date
        strINV = "INV00" & Format(Me.InvoiceDate, "MMDDYY") & strNextAvailable
        
        'put the invoice number in the InvoiceNumber field
        Me!InvoiceNumber = strINV
        
    End If
End Sub

I really don't even use the INVNumberForDate field (it does have numbers in it though for testing), is it necessary?

 

[pbaldy]

I'm making a couple of assumptions here, but try:

strNextAvailable = 1 + Nz(DCount("[INVNumberForDate]", "tblInvoice", "[InvoiceDate] = Date()"), 0)

 

[WayneRyan]

Test,

I don't think that [InvoiceDate] will ever be equal to 'Date'

"[InvoiceDate] = 'Date'"

should be something like:

"[InvoiceDate] = #" & Date & "#"

Wayne

 

[testdba]

Test,

I don't think that [InvoiceDate] will ever be equal to 'Date'

Aah ha! you were right. I changed the line around a little bit and now it works.

strNextAvailable = 1 + Nz(DCount("[InvoiceDate]", "tblInvoice", "[InvoiceDate] = #" & Date & "#"), 0)

Thank you for your help!

 

[mresann]

One more thing: A Date field is actually a special type of number, where the integral value is equal to days, while the decimal value corresponds to the Time of Day. Thereforer, a Date Value of 38367.0 which corresponds to Jan 14, 2005, at midnight, is NOT the same as a Date Value of 38367.25, which corresponds to Jan 14, 2005, at 6 in the morning. This confuses a lot of programmers. In general, the following is true:

If a date field is populated through a user input, most notably through a textbox or a combo box of choices, the date entered will be exactly as intended. Actually, what is being done is the date is being entered as an integer WITHOUT a decimal, which as explained earlier constitutes the time. So when a user enters "January 14, 2005" in a textbox that populates the Date field, the actual datum being entered into the field is "38367.0".

However, if the date field is filled through a computer operation that uses the Now function, or any calculation that is derivative of the Now function, the datum that populates the Date field is the integer, 38367, PLUS the time of day, expressed as a decimal fraction of the day that the record was updated. If the record was updated at exactly 9:00 am of January 14, 2005, the actual value that populates the date field would be "38367.375". Therefore, 38367.0 does not equal 38367.375, even though they are the same date. In your case, if [Invoice Date] were entered by the Now function, it would never match the date in the variable (unless the invoice date was processed at EXACTLY 12:00 midnight, no seconds).

Now, keep in mind that you may have set the date format in the table to show just the date, BUT it doesn't mean the actual VALUE of the field matches that of other dates.

I realize what I wrote may seem a bit confusing, and in fact I've already edited my own writing three times, and hope you can understand what I wrote. NOW for the solution:

Instead of:

strNextAvailable = 1 + Nz(DCount("[INVNumberForDate]", "tblInvoice", "[InvoiceDate] = 'Date'"), 0)

Try:

strNextAvailable = 1 + Nz(DCount("[INVNumberForDate]", "tblInvoice", "((([InvoiceDate]) >= Date() And ([InvoiceDate])<Date()+1))"), 0)

Please note the format change, from 'Date' to Date() --note the elimination of the apostrophe, and the addition of the parentheses.