Generate a unique number 3

[scsuflyboy72]

I have a table called WorkOrder. I have fields called WODate, and Work Order # (among others that aren't related). I have a field named ID that is an autonumber, set as the Primary Key.

Here's what I would like to do that I can't seem to manage:

I want to do one of two things.

On my form I have command button, that when clicked creates a blank word order form. Is there a way to have this command button also generate a unique work order #? I would like to have the work order number in the format mmddyy##, where the ## is the work order number for that job, starting at 01 and incrementing by 1. On the next day, the increment would start over at 01, instead of a continual increment.

If this can't be done, could someone assist me in creating a command button that when clicked would generate a work order # based on the format stated above, that would place this # in the Work Order # field.

Please help...I'm stuck and need to get this going ASAP.

 

[gwinn7]

The autonumber generates the unique number you would need to create your work order #. Is there a reason why you can't use that number?

Gary
gwinn7

 

[gwinn7]

Oh, I see, sorry missed that it needs to start over every day.

In which case, create a function to pull the COUNT of all records on your current date, then just increment that number.

Gary
gwinn7

 

[scsuflyboy72]

Let me start off by saying I'm not an expert or even advanced programmer. I know the basics, and as such don't know how to create a funciton to pull the count of records.

Second, just by doing this how would I format the work order in the format "mmddyy##" to include the pulled counts?

 

[TheAceMan1]

How are ya scsuflyboy72 . . .

First a little on dates & sorting . . . A date (set to the Date data type in the table), is basically an integer. For all the formatting that can be applied to a date, [blue]remember a format affects display . . . not the actual value.[/blue]

Since a date is an integer, herein lies the reason why its easily sorted . . . easy to find the min or max in a group of dates.

Your format of [blue]mmddyy##[/blue] changes things, particularly in the post appended ##! The only way to do this in access is with an alpha-numeric value. In doing so the date looses its integer value as its now represented by a string! As such, sorting & finding min,max are done by access on a character by character basis. The result dependent on the ASCII values of each character.

The point of all this has to do with sorting (I can't see you getting around the need to sort by date!) & finding the max or min among a group of dates. To maintain this functionalty your format needs to change to [blue]YYMMDD##[/blue].

With the above in mind, the following function will return the proper date with the proper index on a day by day basis. Just copy/paste the function to a module in the modules window and call the function as needed. Note: [blue]you![/blue] substitute proper names in [purple]purple[/purple].

[blue]Public Function AutoDate() As String
   Dim maxDate As String, curDate As Date
   Dim Yr As Integer, Mo As Integer, Dy As Integer, idx As Integer
   
   maxDate = DMax("[[purple][b]DateFieldName[/b][/purple]]", "[purple][b]TableName[/b][/purple]")
   Yr = Val(Left(maxDate, 2))
   Mo = Val(Mid(maxDate, 3, 2))
   Dy = Val(Mid(maxDate, 5, 2))
   idx = Val(Right(maxDate, 2))
   curDate = Int(Now())
   
   If DateSerial(Yr, Mo, Dy) <> curDate Then
      AutoDate = Format(curDate, "yymmdd") & "01"
   Else
      AutoDate = Left(maxDate, 6) & Format(idx + 1, "00")
   End If
   
End Function[/blue]

Note: The function only returns the next index for that day and doesn't update the table. I'm leaving that to you . . . at least for now.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[randy700]

Or, you could store the mmdd value in one field, the ## value in a separate field, and concatenate them to create the work order. By doing so, you CAN sort by date.

Randy

 

[gwinn7]

Thanks Randy, this is exactly how it should be done.

You definitely want to avoid storing a concatenated value. AceMan was right about losing the sorting ability which is why you would store the date and the incremented number separately.

Once the components of the value are in the system, you can display it any way you like, including your work order number format.

If you don't know how to code the function to increment, I can give you a leg up on that....

public function getneworderid(mydate as date) as long
Dim rs as dao.recordset

on error resume next

set rs = currentdb.openrecordset("SELECT count(orderinc) FROM workorders WHERE orderdate = #" & mydate & "#", dbopensnapshot)

if not rs.bof then
getneworderid = rs(0) + 1
else
getneworderid = 1
end if

end function

The above is a quick and dirty way of getting your new incremented work order number. It is off the cuff so you may need to tweak and test it out a bit.

Best of luck,

Gary
gwinn7

 

[gwinn7]

For generating the date, you can make it really easy on yourself. Just make the orderdate field required and default to now(). It will automatically place today's date time in the system when the new order is created. The best part about this is there is NO code to write! All you have to do then is to call the function I mentioned previously and voila, work order number.

Gary
gwinn7

 

[scsuflyboy72]

Is there any way you could create a dummy db and send it to me so I can see how this is done. As I stated, I am a novice (to say the least) and am, how do I put it, Lost...?

 

[gwinn7]

If you are this "lost", you should probably hire someone to help you.

Many people who post solutions to this site are paid professionals of varying skill levels. I did my best to help you solve your situation, but you should give your self the benefit of doing some research and effort to get this to work yourself.

Just create a table with your fields...

"ID" long integer Primary Key
"ORDERDATE" timestamp DEFAULT now() Required to yes.
"ORDERINC " integer Required to Yes.

Create a sample bound form with those fields on it and in the before_update of your form, call function I mentioned and assign the result to the ORDERINC column(ie field).

From there you can see and try out what works best for you.

If you want to hire me to give you a sample working database, let me know.

Gary
gwinn7