Create A Number for Daily Use

[sweetelka18]

I am trying to create a and 8 digit number that
always begins with a 46 contains a Juliandate and then the order seen that day. I would like for it to print out in the reports and in one of the fields in a subform. Usually it is written when a person visits so it would need to restart numbering everyday. The database shows the patient data in a form and in the subform is listed the information visit so each visit is saved and a new record is created everytime but the persons data stays the same.
The number would look like this for
46001001
the first 2 digits is static it does not change
the next 3 digits changes according to Julian Date
the last 3 digits is order in which person comes in.

The reports reflects this number, the person seen, visit details printed out for entire day. ie list of persons seen that day per this number.

Please help!!!

 

[Remou]

By julian date, do you mean day number? How many users do you expect to have on the system?

 

[TheAceMan1]

How are ya sweetelka18

Also, for the current date the [blue]Julian Date[/blue] is certainly longer than 3 digits! . . .

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

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[nastar1]

Julian date is only a 3 digit number starting at 001 every year and ending with 365 or 366. Heavily used in military data systems and calendars.

I think you could use something along the lines of the code below. On each new entry, you'll get the Year, then the Julian date and then a unique number that increments with each new record entered.

Private Sub Form_BeforeInsert(Cancel As Integer)

Dim myYear1, myMonth, myDate1 As Integer
Dim myYear, myDate As String

myYear1 = Format(Now(), "yy")
myDate1 = Format(Now(), "y")

myYear = CStr(myYear1)
myDate = CStr(myDate1)


If Me.NewRecord Then
Me.Yr = myYear
Me.txtJulDt = Format(myDate, "000")
Me.SeqID = Nz(DMax("SeqID", "Tickets", "[Yr]='" & myYear & "'"), 0) + 1

End If


End Sub

 

[nastar1]

Oops, forgot that you were looking to begin the field with a static text entry rather than the Year.

Should be a snap to make that correction to my code sample.

 

[sweetelka18]

Ok I have the Julian Day and the Constta 48 however I am not able to increment 001 daily.

There is a table called visits that is a subform of a table called individuals. Each individual has a different visit. In the subform only the last visit is shown. Each visit the information is added for the visit including the control number. My code is in the visit forms VB.

I am having trouble with the 'get next days visit number order .

Option Compare Database
Dim CurrentDate As Date
Dim LastDate As Date
Dim VisitOrder As Integer

Private Sub ControlNumber_Click()

Dim rst As DAO.Recordset
Dim JulianDate, myDate As Integer
Dim CurrentDate, LastDate, myDate1 As Date
Dim ControlNumber, JulianDate1, OutPtNbr1, VisitOrder1 As String

OutPtNbr = "48"
VisitOrder = Format(1, "000")

On Error GoTo Err_Execute

Set rst = Me.Recordset

'set current date to today
CurrentDate = DateValue(Now())

'Retrieve JulianDate
JulianDate = Format(CurrentDate - DateSerial(Year(CurrentDate) - 1, 12, 31), "000")

'get next days visit number order
If Me.NewRecord Then 'if this a new record then
VisitOrder = Nz(DMax(VisitOrder, "ControlNumber", [CurrentDate NOT LastDate]), 0) + 1 'and current date equals last date increment
Else
VisitOrder = DMax(VisitOrder, "ControlNumber", [CurrentDate=LastDate]) +1
End If

JulianDate1 = CStr(Format(JulianDate, "000"))
VisitOrder1 = CStr(Format(VisitOrder, "000"))


ControlNumber = OutPtNbr & JulianDate1 & VisitOrder1
Me!ControlNumber = ControlNumber


Err_Execute:
'An error occurred, return blank string
ControlNumber = ""
MsgBox "An error occurred while trying to determine the next ControlNumber to assign."

End Sub

 

[HarleyQuinn]

Julian date is only a 3 digit number starting at 001 every year and ending with 365 or 366

Just to clear things up (and as Remou and AceMan eluded to), that is not a Julian Date, that's an Ordinal Date (often incorrectly and confusingly called a Julian Date), a true Julian Date is generally represented as a floating point number (for example the Julian Date for now is roughly 2454532.96701).

While I'm in an informative mood, sweetelka18 your variable dimensioning probably isn't working as expected. For example:

Dim CurrentDate, LastDate, myDate1 As Date

Only myDate1 will be dimensioned as a Date, the other two will be Variants. You can declare multiple variables on one line but to have them as you wish you need to qualify them e.g.

Dim CurrentDate As Date, LastDate As Date, myDate1 As Date

Also (wow! this is going on longer than I thought ), using an integer for your 'JulianDate' (as you appear to be trying to do will never return a leading 0 (for example today's Ordinal Date would return as 67), your code is enterpreting JulianDate as a String (as this is what Format() returns) you might as well use a String. Seems you're using a String there's no need to mess about with adding and subtracting Dates, you can get the Ordinal Date using a few methods

Format(Format(Now, "y"), "000")

Right(String$(3, "0") & Format(Now, "y"), 3)

Back to my main point, what problems are you having with 'get next days visit number order?

Sorry this is a bit of a long post but once I got going I couldn't stop myself

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[sweetelka18]

Thank you for clarifying. I copied the code

'Retrieve JulianDate
JulianDate = Format(CurrentDate - DateSerial(Year(CurrentDate) - 1, 12, 31), "000")

from the Microsoft Knowledge articles. What you are telling me makes more sense.


I've only programmed in College using Java, C++, and in VB.Net but I never learned how to program in Microsoft Office VB. So now I'm teaching myself since I think it could be a quite useful tool.

I needed help with the last this part.

'get next days visit number order
If Me.NewRecord Then 'if this a new record then
VisitOrder = Nz(DMax(VisitOrder, "ControlNumber", [CurrentDate NOT LastDate]), 0) + 1 'and current date equals last date increment
Else
VisitOrder = DMax(VisitOrder, "ControlNumber", [CurrentDate=LastDate]) +1
End If

I want the if statement to see if this is a new day and to reset the increment for the day if it is

if is not a new day to continue incrementing for each visit of the day and add to the new concetated ControlNumber to the form field (and saved in a table to later be printed in a report).


Or is there an easier way to reset incrementing every day.

IE Day 1
001
002
003
004
005
...

Day 2
001
002
003
004
005
...

 

[SuePee]

I am trying to do the same thing with a database. I want the "Julian" date to be used in a format:yy-000-01
EX: 08-185-01

I copied the following code and all I get is a blank field. I don't even get the error message.

I never had any programming training and so I am really challenged. I even added a field called ControlNumber so that there would not be a conflict with names.
1. Where do I add the code? In the OnClick Event?
2. What else did I do wrong? I saw there were a couple of corrections after this code, but I didn't know how or where to apply them.
Here is what I copied:
Option Compare Database
Dim CurrentDate As Date
Dim LastDate As Date
Dim VisitOrder As Integer

Private Sub ControlNumber_Click()

Dim rst As DAO.Recordset
Dim JulianDate, myDate As Integer
Dim CurrentDate, LastDate, myDate1 As Date
Dim ControlNumber, JulianDate1, OutPtNbr1,
VisitOrder1 As String

OutPtNbr = "48"
VisitOrder = Format(1, "000")

On Error GoTo Err_Execute

Set rst = Me.Recordset

'set current date to today
CurrentDate = DateValue(Now())

'Retrieve JulianDate
JulianDate = Format(CurrentDate -
DateSerial(Year(CurrentDate) - 1, 12, 31), "000")

'get next days visit number order
If Me.NewRecord Then 'if this a new record then
VisitOrder = Nz(DMax(VisitOrder, "ControlNumber",
[CurrentDate NOT LastDate]), 0) + 1 'and current date
equals last date increment
Else
VisitOrder = DMax(VisitOrder, "ControlNumber",
[CurrentDate=LastDate]) +1
End If

JulianDate1 = CStr(Format(JulianDate, "000"))
VisitOrder1 = CStr(Format(VisitOrder, "000"))


ControlNumber = OutPtNbr & JulianDate1 & VisitOrder1
Me!ControlNumber = ControlNumber


Err_Execute:
'An error occurred, return blank string
ControlNumber = ""
MsgBox "An error occurred while trying to determine
the next ControlNumber to assign."

End Sub

Thanks for any help you can give.


Same Circus, Different Clowns