DMax to Increase Number by One

[Thomas [PBD]]

I have the following code that I did borrow from one of the other posts on
the message board, but I had amended it to change to include a function of
creating a Quote number.

Me.Quote_Number = "ORFMC" & Format(Now(), "mmddyy") & "-" &
Format(Right(DMax("[Quote Number]", "[tblQuotes]", Mid([Quote Number], 6, 6)
= Format(Now(), "mmddyy")), 2) + 1, "00")

What I wanted this to do, it does in theory, but doesnt complete the step.
The final two digits at the end of the code (i.e. 01, 02, 03 ...) designate
the number of quotes that were handled in the day. Therefore, the number
needs to reset to 01 for the first quote of the day. I figured that the
Criteria section that I filled in on the DMax would do that, but I was
mistaken.

Any help here to reset the quote number to 01 each day would be great.

 

[Douglas J. Steele]

Try:

Me.Quote_Number = "ORFMC" & Format(Now(), "mmddyy") & "-" &
Format(Right(DMax("[Quote Number]", "[tblQuotes]", "Mid([Quote Number], 6,
6)
= Format(Now(), "mmddyy")), 2) + 1", "00")

Hopefully the difficulty in getting this working explains why it's usually
recommended that you NOT use so-called "intelligent keys"

Store the date and quote number as separate fields. Create a query that
concatenates them together as for display purposes and use the query
wherever you would otherwise have used the table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have the following code that I did borrow from one of the other posts on
the message board, but I had amended it to change to include a function of
creating a Quote number.

Me.Quote_Number = "ORFMC" & Format(Now(), "mmddyy") & "-" &
Format(Right(DMax("[Quote Number]", "[tblQuotes]", Mid([Quote Number], 6,
6)
= Format(Now(), "mmddyy")), 2) + 1, "00")

What I wanted this to do, it does in theory, but doesnt complete the step.
The final two digits at the end of the code (i.e. 01, 02, 03 ...)
designate
the number of quotes that were handled in the day. Therefore, the number
needs to reset to 01 for the first quote of the day. I figured that the
Criteria section that I filled in on the DMax would do that, but I was
mistaken.

Any help here to reset the quote number to 01 each day would be great.

 

[Thomas [PBD]]

Almost worked, it kept giving me a compile error of "Expected: list seperator
or )", so I reworked the code to show:

Me.Quote_Number = "ORFMC" & Format(Now(), "mmddyy") & "-" &
Format(Nz(Right(DMax("[Quote Number]", "[tblQuotes]", "Mid([Quote
Number],6,6)=Format(Now(),'mmddyy')"), 2), 0) + 1, "00")

Also included the Nz function to account for the null value of the start of
a new date.

Try:

Me.Quote_Number = "ORFMC" & Format(Now(), "mmddyy") & "-" &
Format(Right(DMax("[Quote Number]", "[tblQuotes]", "Mid([Quote Number], 6,
6)
= Format(Now(), "mmddyy")), 2) + 1", "00")

Hopefully the difficulty in getting this working explains why it's usually
recommended that you NOT use so-called "intelligent keys"

Store the date and quote number as separate fields. Create a query that
concatenates them together as for display purposes and use the query
wherever you would otherwise have used the table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have the following code that I did borrow from one of the other posts on
the message board, but I had amended it to change to include a function of
creating a Quote number.

Me.Quote_Number = "ORFMC" & Format(Now(), "mmddyy") & "-" &
Format(Right(DMax("[Quote Number]", "[tblQuotes]", Mid([Quote Number], 6,
6)
= Format(Now(), "mmddyy")), 2) + 1, "00")

What I wanted this to do, it does in theory, but doesnt complete the step.
The final two digits at the end of the code (i.e. 01, 02, 03 ...)
designate
the number of quotes that were handled in the day. Therefore, the number
needs to reset to 01 for the first quote of the day. I figured that the
Criteria section that I filled in on the DMax would do that, but I was
mistaken.

Any help here to reset the quote number to 01 each day would be great.