Current Date to Julian Date?

[Hammilton]

Hello,



I'm hoping this is a simple question, I think it is, but I'm slowly remembering how to use Access properly, a lot has changed since I first learned it back in 02-03. I have a form, and when a new entry is creating a text box has by default today's date. Another text box has the Julian date, but currently that's being entered manually. How would I make it so that it would take the data that's in the today's date box and convert that to the Julian date?

Thank you for your help!
Hammilton

[alansidman]

Look at this link http://www.cpearson.com/excel/jdates.htm While this is an Excel formula, it can be easily converted to an Access Expression which would look like this; Right(CStr(Year(Date)),2) & CStr(Date()-DateSerial(Year(Date()),1,1))

[ItsMe]

Here is one example

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

https://support.microsoft.com/en-us/kb/162745

[Hammilton]

Okay, but how can I have it take the date that's in the Date Written box (which defaults to today's date) and have it fill in the Julian date in a different box? 99% of the time the default today's date will be used, but occasionally an older date may be entered.

[ItsMe]

Using Alan's example you could paste the following into the AfterUpdate event of the textbox the user is typing a date into. Let's say the name of that textbox control is, "TextBoxName".

dim dtInput as date
dtInput = me.TextBoxName.Value
me.OtherTextBoxName.Value = Right(CStr(Year(dtInput)),2) & CStr(dtInput-DateSerial(Year(dtInput),1,1))

As an alternative, using the function in the other post, you would paste the function in the form's module or in a Standard Module.

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

Then you would use something like the following in the afterupdate of TextBoxName ...

dim dtInput as date
dtInput = me.TextBoxName.Value
me.OtherTextBoxName.Value = CDate2Julian(dtInput)

ONe other thing to consider is that you are typically using the default value. So you might want to add some code in the OnCurrent event for the form ...

me.OtherTextBoxName.Value = Right(CStr(Year(Date)),2) & CStr(Date()-DateSerial(Year(Date()),1,1))

or

me.OtherTextBoxName.Value = CDate2Julian(Date)

[Hammilton]

Empty WO Log.zip

I'm also trying to make my print button print the report for only the record that's currently showing in the form. I've tried a lot of code that I've found online to make this work, but I haven't gotten it to work. I can get it to print, but it prints everything in the database, when I only want it to print the one record. I'm surprised that Microsoft hasn't addressed that problem, it seems very widespread, I see reports of people having that problem going back to the late 90's.

I've attached an emptied version of my database. The real one has over seven thousand entries and wouldn't fit on the site.

I used to be very good with VB but again, it's been a very long time since I used it. Back in high school I was able to create a fully fledged 3D game using DirectX, now I can't make a report print... It's been a dark path, lol

Edit: I just wanted to add that I'm not asking for a handout, I'm trying to figure this out myself, but it's taking me a while, it's been so long since I've done any coding I'm having trouble getting my brain back into that mode.

[Hammilton]

The code for the Julian date works, thank you! One question, though- is it possible to remove the two digit year from the output?

[alansidman]

Not sure which solution you are using. But if you are using mine, then remove this from the expression: Right(CStr(Year(Date)),2)

I used this code in the attached file to only print the visible record open in the form

    Dim stDocName As String


    stDocName = "Submission"
    DoCmd.OpenReport stDocName, acNormal, , "[ID] = Forms!frmClients!ID"
    DoCmd.PrintOut acPages, 1, 1

[ItsMe]

...is it possible to remove the two digit year from the output?

I did not test any of the code and I do not know what the output looks like. I have to guess which example you are referring to and also where the code may be providing such an output. I guess it is here ...
Right(CStr(Year(Date)),2)

The & operator is used to concatenate, so you would want to remove that in case you are not combining stuff.

Post #6 looks like a question that deserves its own thread. However, you might be able to resolve the issue by searching for
Docmd.OpenReport

The fourth argument of the method will apply criteria to the report's Filter property.

[Hammilton]

AlanSidman,

I used the code you gave there, but it doesn't work. Well, or rather, it kind of works. It's printing two pages. One page is an empty copy of the report I want to print, and the second page is the form version of the record I wanted to print.

Edit: I should also add that when I'm going to print, there are a couple boxes that are popping up reading "Enter Parameter Value" - one for ID, and one for "Forms!frmClients!ID"

I'm not sure where I'm going wrong.

Thanks

[Hammilton]

I have two additional questions that I can't find answers for, the answer to which will help me solve a number of other problems I'm having.

I have created a log-in to my database. The user types in his username and password and if it matches he'll be taken to splash screen. If the user is an admin he's taken to one splash screen, if he is not, he's taken to another. Regardless of which way it goes, I want to make it so that the splash screen displays the users name, so, something along the lines of "Welcome.... LoggedInUserName" but I can't figure out how to make that display. One of the tutorials I was watching on youtube showed a splashscreen that displayed just this information, but they didn't mention how that information was actually displayed in the tutorial.

Once I can figure that out I can then use that to do a number of other things, I think.

I have a button on that splash screen that currently does nothing, but when it's done I want it to pop up all open work orders that have been assigned to that person, so they can then go through and close them and whatever else they need to do.

On the splash screen for admins I have a button that takes the user to a form that is supposed to allow the user to reset another users password. On the form I want the admin to entry the users name and then a new password, but I can't figure out how to change that record. I don't want it to be flipping through a bunch of records and selecting the user you want to edit and then making the change that way. I want the user to enter the user name, type in the new password, and then upon clicking the Change Password button the sub to search for the user record and then, if that username is found, to modify the password for that user. This is the code that I've come up with:

Dim rs As Recordset
Dim db As Database

Set db = CurrentDb
Set rs = db.OpenRecordset("Users")

rs.FindFirst "Username = '" & Me.txtUserID & "'"

If rs.NoMatch = True Then
Me.lblIncorrectUsername.Visible = True
Me.txtUserID.SetFocus
Exit Sub
End If

rs!Password = Me.txtNewPass.Value
Me.lblIncorrectUsername.Visible = False



However It's not working. I think part of the problem is the part highlighted red. If I can get this to work I think I can figure out how to do a lot of the other things I need to do with this database.

[Hammilton]

I think I've solved part of my problem with trying to set a variable on one form and then using it on another. In my login form, on the forms close I have a macro run that sets a temp var (varUserID) equal to [txtUserName], and then on another forms opening, I have a routine that takes me.lblAdmin.Text (or caption, I forget) = [tempvar]![varUserID]

On the login button click there's a routine that works, if the user name and password are correct, it'll open the one form, and closes the login form, but then I get an error, when it runs the open routine. What am I doing wrong here???

I know I'm gonna be stuck at work all sunday trying to figure this out now. I want to really upgrade this database, expand user functionality and make it more user friendly than it had been, but it's taking me a while to remember everything, so I apologize for asking so many questions. Once I get some of these things figured out I'll be able to use that knowledge to figure out a lot of the other things I'm having trouble with. I've already worked on this thing about 8 hours today, and while I have some things to show for my time, I spent most of my time trying to figure out small things and accomplishing little.

[ssanfu]

Well, one thing....... you can't change a value in a field of a recordset using

rs!Password = Me.txtNewPass.Value

(BTW, you don't need to type ".Value".... it is the default property)
Because you are using a recordset, you have to use the EDIT and UPDATE commands.


Here are two methods:

Your code (modified)

'Sub Something_Else()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    Set db = CurrentDb
    Set rs = db.OpenRecordset("Users")

    rs.FindFirst "Username = '" & Me.txtUserID & "'"

    If rs.NoMatch = True Then
        Me.lblIncorrectUsername.Visible = True
        Me.txtUserID.SetFocus
    Else
        rs.Edit
        rs!Password = Me.txtNewPass
        rs.Update

        Me.lblIncorrectUsername.Visible = False
    End If

    rs.Close
    Set rs = Nothing
    Set db = Nothing

'End Sub

My preferred code (highly modified ):

'Sub Something()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim sSQL As String

    Set db = CurrentDb

    sSQL = "SELECT Password FROM Users WHERE  Username = '" & Me.txtUserID & "'"
    '    Debug.Print sSQL

    Set rs = db.OpenRecordset(sSQL)

    If rs.BOF And rs.EOF Then
        'no records
        Me.lblIncorrectUsername.Visible = True
        Me.txtUserID.SetFocus
    Else
        'has 1 or more records
        rs.Edit
        rs!Password = Me.txtNewPass
        rs.Update

        Me.lblIncorrectUsername.Visible = False
    End If

    rs.Close
    Set rs = Nothing
    Set db = Nothing

'End Sub