Date Format

[Chris]

Here at my shop we use peoplesoft and peoplesoft uses julian dates that look
like this: 107040. My question is if someone gave me a date in the format
of 02/09/07 how could I format it to look like 107040? Any sugestions?

Thanks,
Chris

 

[Guest]

Debug.Print "1" & Right(Year(#02/09/07#),2) & String(3 -
Len(Datepart("y",#02/09/07#)),"0") & DatePart("y",#02/09/07#)

 

[Chris]

Thanks Jerry. I'm a little confused as to where to put the code. What I
want to happen is to have the user enter a date in a text box on a form like
02/01/2007 and hit an ok button. Once they hit the okay button I want to
format the date to look like this 107032 and then call a query I've designed
and use the text box on the form with the new format as my date criteria. I
know how to call an object on a form in a query but I'm confused by where to
put your code and what the debug.print is all about. Can you clarify for me
please?

Thanks so much,
Chris

 

[John W. Vinson]

Thanks Jerry. I'm a little confused as to where to put the code. What I
want to happen is to have the user enter a date in a text box on a form like
02/01/2007 and hit an ok button. Once they hit the okay button I want to
format the date to look like this 107032 and then call a query I've designed
and use the text box on the form with the new format as my date criteria. I
know how to call an object on a form in a query but I'm confused by where to
put your code and what the debug.print is all about. Can you clarify for me
please?

I think what Jerry was assuming you would do is use the expression in
an expression in the query. Try typing

"1" & Right(Year([Forms]![yourform]![datefield]),2) & String(3 -
Len(Datepart("y",[Forms]![yourform]![datefield])),"0") &
DatePart("y",[Forms]![yourform]![datefield])

or a bit more simply

"1" & Format([Forms]![yourform]![datefield],"yy") &
Format(DatePart("y", [Forms]![yourform]![datefield]), "000")

on the Criteria line on your query.

John W. Vinson [MVP]

 

[raskew via AccessMonster.com]

Sugestions? Dump PeopleSoft! That date method is just bizarre (and BTW,
isn't a julian date).

Bob

 

[Chris]

Thanks John,
I will try this.

Thanks Jerry. I'm a little confused as to where to put the code. What I
want to happen is to have the user enter a date in a text box on a form
like
02/01/2007 and hit an ok button. Once they hit the okay button I want to
format the date to look like this 107032 and then call a query I've
designed
and use the text box on the form with the new format as my date criteria.
I
know how to call an object on a form in a query but I'm confused by where
to
put your code and what the debug.print is all about. Can you clarify for
me
please?

I think what Jerry was assuming you would do is use the expression in
an expression in the query. Try typing

"1" & Right(Year([Forms]![yourform]![datefield]),2) & String(3 -
Len(Datepart("y",[Forms]![yourform]![datefield])),"0") &
DatePart("y",[Forms]![yourform]![datefield])

or a bit more simply

"1" & Format([Forms]![yourform]![datefield],"yy") &
Format(DatePart("y", [Forms]![yourform]![datefield]), "000")

on the Criteria line on your query.

John W. Vinson [MVP]

 

[Chris]

I know this is a little delayed John but I have to thank you for your help.
Your date function was just what I needed. Thanks so much.

Chris

Thanks Jerry. I'm a little confused as to where to put the code. What I
want to happen is to have the user enter a date in a text box on a form
like
02/01/2007 and hit an ok button. Once they hit the okay button I want to
format the date to look like this 107032 and then call a query I've
designed
and use the text box on the form with the new format as my date criteria.
I
know how to call an object on a form in a query but I'm confused by where
to
put your code and what the debug.print is all about. Can you clarify for
me
please?

I think what Jerry was assuming you would do is use the expression in
an expression in the query. Try typing

"1" & Right(Year([Forms]![yourform]![datefield]),2) & String(3 -
Len(Datepart("y",[Forms]![yourform]![datefield])),"0") &
DatePart("y",[Forms]![yourform]![datefield])

or a bit more simply

"1" & Format([Forms]![yourform]![datefield],"yy") &
Format(DatePart("y", [Forms]![yourform]![datefield]), "000")

on the Criteria line on your query.

John W. Vinson [MVP]