Change record number per day to always start over?

[valkyry]

Ok, I have the following in my report:
CustomQuoteNumber: Format([EntryDate],"yymmdd") & recordnumber & [EnteredBy]

It's a logic based off of:
1. Entry Date in the yymmdd format then by
2. Record number of the day which needs to always begin with 01. This gives 99 orders per day per user.
3. user id

So it would be 060720 (EntryDate) followed by 01 for the 1st order of the day and ending with the AE user's initials (EnteredBy).

the result is 06072001AE

the next order would be 06072002AE
continues until 99.

Ideally it'll restart per change in the user's initials (EnteredBy).

Meaning, if another person enters a quote, the quote for that person is 06072001@@ (@@ for the other user's initials).

Hope this makes sense.

I don't know how to tell it to calculate the 01 through 99 repeating per user and per day.

 

[Remou]

You can use DMax, say:

DMax("Mid(ID,7,2)","tblTable","Right(ID,2)='" & Me.UserID & "'")

 

[valkyry]

sorry, how do you mean?
how does this work exactly with the current
Format([EntryDate],"yymmdd") & recordnumber & [EnteredBy]

Would your code go in place of the above "recordnumber" place holder?

 

[dhookom]

I would set up sorting and grouping levels by date and user. Then add a text box (could be hidden) in the detail section:
Name: txtCountDateUser
Control Source: =1
Running Sum: Over Group

Then create your text box with a control source of:
=Format([EntryDate],"yymmdd") & txtCountDateUser & [EnteredBy]


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Remou]

You can set the default value of the relevant textbox to:

Format([EntryDate],"yymmdd") & DMax("Mid(ID,7,2)","tblTable","Right(ID,2)='" & [EnteredBy] & "'") + 1 & [EnteredBy]

 

[valkyry]

dhookom
this is close
but it's counting to the next.

I have two records and both are showing 1

I also need the counter to be two digits

so:
01
02
03 up to 99

 

[valkyry]

Remou,
yours did not work...

??

 

[valkyry]

dhookom,
the RunningSum is the thing but I can't figure it out on a Form.

 

[dhookom]

valkyry,
This is a reports forum. My solution works for reports.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Remou]

Try this, it is for a form:

Private Sub EnteredBy_AfterUpdate()
Me.txtID = Format(Date, "yymmdd") _
    & Format(Nz(DMax("Mid(ID,7,2)", "tblTable", "Right(ID,2)='" _
    & Me.[EnteredBy] & "'")) + 1, "00") & Me.[EnteredBy]
End Sub

I have assumed that EnteredBy, which you mentioned as the name of a field, is also a control on a form.
I have use the After Update event of EnteredBy to update the 'ID' control (field), as it seems the most suitable.
'txtID' is the name of the textbox to hold the result of the calculation, it should be bound to the 'ID' field in 'tblTable'
'txtID', 'ID' and 'tblTable', above, need to be changed to the correct names for your application.
If this "does not work", it is best to post in the forms forum, referencing this thread, and including a note on why it did not work or why it was not suitable.