Date Format

[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.

 

[John W. Vinson]

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.

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.

 

[SAC]

Excellent!!

Thanks!

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.

 

[SAC]

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

 

[John W. Vinson]

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.

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.

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.

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.

 

[SAC]

WOW!! That does work very well.

Thanks a bunch!!

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.

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.

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.

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.

 

[SAC]

John,

Thanks again. That's awesome!

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.

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.

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.

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.