Date Format

  • Thread starter Thread starter SAC
  • Start date Start date
S

SAC

I'm using 2003.

I have a control on a form that I'd like to end up with a date formatted as
YYMMDD.

I currently use this function in the oncurrent event of the form:

Function fLastDayOfMonth()
Dim strmonth As String
Dim strDay As String
Dim strYear As String
Dim dteLastDayOfMonth As Date
strmonth = DLookup("BillingMo", "tblSystem") + 1
strYear = DLookup("BillingYr", "tblSystem")
dteLastDayOfMonth = strmonth & "/" & "01" & "/" & strYear
dteLastDayOfMonth = dteLastDayOfMonth - 1
End Function

At the end of this function, dteLastDayOfMonth is right, 6/30/2009. Now I'd
like to convert it to 090630 with each part having 2 digits.

Thanks for your help.
 
I'm using 2003.

I have a control on a form that I'd like to end up with a date formatted as
YYMMDD.

I currently use this function in the oncurrent event of the form:

Function fLastDayOfMonth()
Dim strmonth As String
Dim strDay As String
Dim strYear As String
Dim dteLastDayOfMonth As Date
strmonth = DLookup("BillingMo", "tblSystem") + 1
strYear = DLookup("BillingYr", "tblSystem")
dteLastDayOfMonth = strmonth & "/" & "01" & "/" & strYear
dteLastDayOfMonth = dteLastDayOfMonth - 1
End Function

At the end of this function, dteLastDayOfMonth is right, 6/30/2009. Now I'd
like to convert it to 090630 with each part having 2 digits.

Thanks for your help.
Click to expand...

No VBA code is needed AT ALL.

Set the Control Source property of the textbox to

=DateSerial(Year(Date()), Month(Date()) + 1, 0)

and set its Format property to

"yymmdd"

Instead of Date() you can use DLookUp to look up the billing date instead.
 
Excellent!!

Thanks!

John W. Vinson said:
No VBA code is needed AT ALL.

Set the Control Source property of the textbox to

=DateSerial(Year(Date()), Month(Date()) + 1, 0)

and set its Format property to

"yymmdd"

Instead of Date() you can use DLookUp to look up the billing date instead.
Click to expand...
 
Sorry...there's more.

Prior to this form, the user selects the month and Year and what I want is
the last date of the month to show in this control. If they select May 09,
then I want 090531 to show.

Sorry I didn't make this clear.

How would I do this?

That's why I take the month and add 1 to it and then later subtract a day.

So I can get the last day of 05 09 as 5/31/2009 but I haven't been able to
format it to 090531.

What can I do?

Thanks a lot for your help
 
Sorry...there's more.

Prior to this form, the user selects the month and Year and what I want is
the last date of the month to show in this control. If they select May 09,
then I want 090531 to show.
Click to expand...

Where do they select it? In a combo box on a form? someplace else?
Sorry I didn't make this clear.

How would I do this?

That's why I take the month and add 1 to it and then later subtract a day.
Click to expand...

The DateSerial() expression I posted does the same thing. The zeroth day of
next month is the last day of this month.
So I can get the last day of 05 09 as 5/31/2009 but I haven't been able to
format it to 090531.
Click to expand...

Again:

Put a Textbox on your form (or report).

Set its Format property to

"yymmdd"

This will format a date/time value the way you request. The Format property of
a control is different than its control source.

Set its control source to

=DateSerial([yearvalue], [monthvalue] + 1, 0)

where yearvalue and monthvalue are the names of controls, or fields,
containing the desired year and month.
 
WOW!! That does work very well.

Thanks a bunch!!

John W. Vinson said:
Sorry...there's more.

Prior to this form, the user selects the month and Year and what I want is
the last date of the month to show in this control. If they select May
09,
then I want 090531 to show.
Click to expand...

Where do they select it? In a combo box on a form? someplace else?
Sorry I didn't make this clear.

How would I do this?

That's why I take the month and add 1 to it and then later subtract a day.
Click to expand...

The DateSerial() expression I posted does the same thing. The zeroth day
of
next month is the last day of this month.
So I can get the last day of 05 09 as 5/31/2009 but I haven't been able to
format it to 090531.
Click to expand...

Again:

Put a Textbox on your form (or report).

Set its Format property to

"yymmdd"

This will format a date/time value the way you request. The Format
property of
a control is different than its control source.

Set its control source to

=DateSerial([yearvalue], [monthvalue] + 1, 0)

where yearvalue and monthvalue are the names of controls, or fields,
containing the desired year and month.
Click to expand...
 
John,

Thanks again. That's awesome!


John W. Vinson said:
Sorry...there's more.

Prior to this form, the user selects the month and Year and what I want is
the last date of the month to show in this control. If they select May
09,
then I want 090531 to show.
Click to expand...

Where do they select it? In a combo box on a form? someplace else?
Sorry I didn't make this clear.

How would I do this?

That's why I take the month and add 1 to it and then later subtract a day.
Click to expand...

The DateSerial() expression I posted does the same thing. The zeroth day
of
next month is the last day of this month.
So I can get the last day of 05 09 as 5/31/2009 but I haven't been able to
format it to 090531.
Click to expand...

Again:

Put a Textbox on your form (or report).

Set its Format property to

"yymmdd"

This will format a date/time value the way you request. The Format
property of
a control is different than its control source.

Set its control source to

=DateSerial([yearvalue], [monthvalue] + 1, 0)

where yearvalue and monthvalue are the names of controls, or fields,
containing the desired year and month.
Click to expand...
 
Back
Top