Need a Julian Date for a Document No

[SuePee]

REF: thread701-1454746
I am trying to create a smart number for a user. Currently they are using this system manually and have been for years. They want the "Julian" (Ordinal)date to be used in this format:Year-Julian Date-sequential number. EX: 08-185-01
This would signify that the report was created in 08 on day 185 and that was the first report for the day.

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

 

[Golom]

There are a few problems here

- "LastDate" is declared globally but it is also declared in the Sub which will hide the global instance. In any event ... it doesn't seem to get set anywhere.

- You have no continuations (_) for some lines so the code does not compile correctly.

- The format of DMAX is

DMax ( "FieldName", "TableName", "Criteria" )

Your use of DMax will resolve to

DMax ("001", "ControlNumber", True )
OR
DMax ("001", "ControlNumber", False )

You probably need quotes ... not brackets ... around the third argument

- You can get the first two parts of the number with

Format(Date,"yy") & "-" & Format(Date,"y")

 

[TheAceMan1]

How are ya SuePee . . .

Using part of [blue]Goloms[/blue] post, copy/paste the following function to the code module of the form:

[blue]Public Function NextSmartNum() As String
   Dim Yr As String, JD As String, idx As String, Cri As String
   
   Yr = Format(Year(Date), "yy")
   JD = Format(Date, "y")
   
   Cri = "Left([ControlNumber],2) = '" & Yr & "'" [green]'for DLookup below
   
   'Test if current year not previuosly logged to start over from 001[/green]
   If Not IsNull(DLookup("[VisitOrder]", "ControlNumber", Cri)) Then
      idx = Format(Nz(DMax("[VisitOrder]", "ControlNumber")) + 1, "000")
   Else
      idx = "001"
   End If
   
   NextSmartNum = Yr & "-" & JD & "-" & idx[/blue]

Finally in the forms [blue]AfterInsert[/blue] event, copy/paste the following:

[blue]   Me!ControlNumber.DefaultValue = """" & NextSmartNum() & """"[/blue]

Give it a whirl and let us know.

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[SuePee]

Good Morning,

I must be dumber than a box of rocks! I cannot get this to work. I am certain it is something I am doing wrong.
Here is what and where I copied in the code:
Option Compare Database


Private Sub Form_AfterInsert()
Me!ControlNumber.DefaultValue = """" & NextSmartNum() & """"
End Sub

Public Function NextSmartNum() As String
Dim Yr As String, JD As String, idx As String, Cri As String

Yr = Format(Year(Date), "yy")
JD = Format(Date, "y")

Cri = "Left([ControlNumber],2) = '" & Yr & "'" 'for DLookup below

'Test if current year not previuosly logged to start over from 001
If Not IsNull(DLookup("[VisitOrder]", "ControlNumber", Cri)) Then
idx = Format(Nz(DMax("[VisitOrder]", "ControlNumber")) + 1, "000")
Else
idx = "001"
End If

NextSmartNum = Yr & "-" & JD & "-" & idx
End Function

In the Lab Tech Table, where I want the smart number to be, here are the fields.
Task No (This is brought over from another table)
All the other fields and then:
ControlNumber (Set as a text field)

Then I made a Control Number table with one field
DefaultValue (Set as a text field)

This is where I am sure the problem is, but I don't know how to fix it.



Same Circus, Different Clowns

 

[PHV]

What are the actual values of ControlNumber in the [Lab Tech] table ?
Like this ?
48-08-185-01

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SuePee]

No, I want it to be in the format 08-185-01. My mistake on that one,I copied the code from another post where he wanted the first digit to be 48.

Also on the code from Golom, it is telling me ther has to be an "End Function" line in the code.

I was calling this number "Lab No". Do I need to reference it somewhere in the code.

I am so taking a VBA course this fall! Old Engineers should not be subjected to this sort of demoralizing stuff.



Same Circus, Different Clowns

 

[Golom]

From one old engineer to another ...

I think you mean the TheAceMan1's code where he started with a function. Just add an "End Function" line after the last line in his code.

Can you post the code you are using?

We can probably figure out where you are having problems if we see the code.

 

[PHV]

So, post the EXACT names of the relevant fields in the table, the table name and the name of the controls in the form.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SuePee]

Thanks a lot for the help. We seem to be having a bit of trouble with the network today. I sent all the info earlier, but now it does not show up. So here we go again.

The relevant number is the Lab No. That is where I want the new control number to be inserted into the form and consequently the table.

Controls?, Controls? What do you mean by Controls. I have sent the code before, but here goes:


Public Function NextSmartNum() As String
Dim Yr As String, JD As String, idx As String, Cri As String

Yr = Format(Year(Date), "yy")
JD = Format(Date, "y")

Cri = "Left([ControlNumber],2) = '" & Yr & "'" 'for DLookup below

'Test if current year not previuosly logged to start over from 001
If Not IsNull(DLookup("[VisitOrder]", "ControlNumber", Cri)) Then
idx = Format(Nz(DMax("[VisitOrder]", "ControlNumber")) + 1, "000")
Else
idx = "001"
End If

NextSmartNum = Yr & "-" & JD & "-" & idx

End Function

Then in the AfterInsert function I put the following
Me!ControlNumber.DefaultValue = """" & NextSmartNum() & """"

Thanks again for you patience and for your help. I have been making databases for a while now, and this stuff boggles my mind. Most of the VB coding I have used has been from this site and I am eternally grateful for you guys making me look so good. LOL

PS Come on over to my house and I'll cook you a good dinner sometime.

Same Circus, Different Clowns