Thread: How do I display module results in a form text box

Hello all,
I have played around with many variations and this what I have come up with so far. The below code as you can see is a function and I have it in a module called JulianDateConvert since I have not been able to combine it with the Sub (probably cant combine them).

Code:

 
Public Function CDate2Julian(ReqDate As Date) As String
CDate2Julian = Format(ReqDate - DateSerial(Year(ReqDate) - 1, 12, 31), "000")
  
End Function

The below code should call the function 'CDate2Julian' above, retrieve the user entered date from the text box called ReqDate and place the converted date into the JulianDate text box.

Code:

Private Sub JulianDate_AfterUpdate()
   
'RequestForm is the name of the form, JulianDate is the name of the cell to display the julian date, 'and ReqDate is the date entered on the form by the user

Forms!RequestForm!JulianDate = CDate2Julian(Year(Forms!RequestForm!ReqDate)) Mod 10 & CDate2Julian(Forms!RequestForm!ReqDate)

End Sub

Testing

Code:

Print Year(DATE()) Mod 100 & CDate2Julian(DATE())

in immediate mode returns the correct date for 8-11-2009 which is 9223.

Using

Code:

Forms!RequestForm!JulianDate = CDate2Julian(Year(Forms!RequestForm!ReqDate)) Mod 10 & CDate2Julian(Forms!RequestForm!ReqDate)

in immediate mode for the same date above displays 2223 in immediate mode in which the first digit should be a 9.

Can someone please assist me in getting the correct julian date to display on the form either after the date is entered on the form or when a button is pressed.

Thanks,
Andrew

I have a couple of questions.

First off is this form open?

Do you want this date stored in a table, or is it a one time date?

My answers change based on these questions.

However I do have some answers.

I am gonna save you some time and try and answer it anyway.

Enter the following in the "Control Source" field in the properties of your field in your form called : JulianDate

Code:

=Year([ReqDate]) Mod 10 & Format([ReqDate]-DateSerial(Year([ReqDate])-1,12,31),"000")

I tested this off a date field that I have in my database.

You shouldn't need any vba for this, if this is what you were thinking.

Hope this helps,

Dan

Thanks for the code Dan. I will answer your questions then try the code.

First off is this form open? Yes. The user has the form open selecting items. Once the user enters the date, I want the julian date to be calculated.

Do you want this date stored in a table, or is it a one time date? I need this stored in a table as part of the key to identify individual orders.

Andrew

Glad I could help, please mark your post as solved if you are satisfied with the answer.

Thanks,

Dan