Question about formatting a date in Access

[hrbsh97]

Hello,

I have a database for organizing magazines .. one column has the name of the
magazine .. one column has the expiration date .. I want the date to just be
the month/year .. for example 0/09 .. when I type in 02/09 it formats the
text to 2/09/2009 .. is there a way to format this so it will display only
the month and year? ..

With many thanks ..

Brenda

 

[Damon Heron]

Yes, first put an input mask on an unbound textbox set to:
99/00;0; This allows the user to type mm/yy.
Your textbox bound to the table should be INVISIBLE, since it will always be
the month/day/year.
I named the bound textbox Text1, and the unbound, Text2.

Next,on the unbound textbox event:

Private Sub Text2_BeforeUpdate(Cancel As Integer)
Dim mydate As Date
mydate = Left(Text2, 2) & "/01/" & Right(Text2, 2)
mydate = DateSerial(Year(mydate), Month(mydate), 1)
Text1 = mydate ' this assigns the date -- example : 02/01/09 -- to the
bound textbox
'the above could all be squeezed into one line
'if you're not big on clarity
End Sub

To make the current records format properly, add this to
the current event of the form:

Private Sub Form_Current()
Dim myPos As Integer
If Not Me.NewRecord Then
myPos = nz(InStr(1, Text1, "/", 1) ,0)
End If
If myPos = 1 Then
Me.Text2 = "0" & Left(Me.Text1, myPos) & right(Text1, 2)
Else
Me.Text2 = Left(Me.Text1, myPos) & right(Text1, 2)
End If

End Sub

HTH
Damon

 

[fredg]

Hello,

I have a database for organizing magazines .. one column has the name of the
magazine .. one column has the expiration date .. I want the date to just be
the month/year .. for example 0/09 .. when I type in 02/09 it formats the
text to 2/09/2009 .. is there a way to format this so it will display only
the month and year? ..

With many thanks ..

Brenda

Well, you could change the field's datatype to Text. Then you could
enter anything you wanted to. Of course, the field will no longer sort
as a date, but ....

Even though you don't 'need' a day in the field, if it is a date
datatype field it must include a day value.
Instead of entering 02/09 for the month and year, enter 02/2009 as the
month and year. Access will automatically change the value to
02/01/2009 (the first day of the month). Simply set the control's
Format property to
mm/yyyy
and all you will see is 02/2009, yet the field will be a valid date
field.

 

[karl dewey]

Open the form in design view and click on properties.
Click on the text box and in Format property type m/yy and save.

 

[Graham Mandeno]

Hi Brenda

Is this a single form or a continuous form? The approach is similar, but
slightly different for the two.

Basically, you need an unbound textbox to enter the data. Then you use the
BeforeUpdate event of that textbox to validate the data entered, split the
input into month and year, and create a date which you assign to the real
date field.

Private Sub txtEditExpDate_BeforeUpdate(Cancel As Integer)
Dim iSlash as integer, iMonth as integer, iYear as integer
On Error Goto ProcErr
With Me!txtEditExpDate
iSlash = Instr(.Value, "/")
If iSlash = 0 then
Cancel = True
Goto ProcEnd
End If
iMonth = CInt( Left( .Value, iSlash-1 )
iYear = CInt(Mid( .Value, iSlash + 1 )
End If
' we now have a month and a year
If iMonth<1 or iMonth>12 then
Cancel = true
Goto ProcEnd
End If
' for first of month:
Me!ExpDateField = DateSerial( iYear, iMonth, 1 )
' or for last of month:
Me!ExpDateField = DateSerial( iYear, iMonth + 1, 0 )
ProcEnd:
If Cancel then MsgBox "Invalid expiry month - enter as mm/yy"
Exit Sub
ProcErr:
Cancel = True
Resume ProcEnd
End Sub

Your Form_Current event procedure needs to set the value of the unbound
textbox:

txtEditExpDate = Format( ExpDateField, "mm/yy" )

Now, for a single form, the real date field doesn't even need its own
textbox - the job is done.

For a continuous form, add a bound textbox for the expiry date, with a
format "mm/yy". Make this bound textbox enabled and locked.

Place your unbound editing textbox exactly over the top of it, then do
Format>Send to back. This will ensure it is always behind the real field
unless it has the focus.

Finally, add a GotFocus event procedure for the real (bound) textbox:

txtEditExpDate.SetFocus

 

[hrbsh97]

Thanks much .. I actually took the simplest answer on this question .. and
that was yours and it worked like a charm!!! .. so very simple. I thank
everyone who responded. I appreciate each of you. Thank you for the
simplicity of your answer .. it was just what I needed and it got the job
done!!

Many thanks Mr. Dewey!!

 

[Damon Heron]

Karl,
Maybe you can help me understand why when, using your format, I type in
02/04 and get a date saved as 02/04/09 rather than 02/01/04? I am using
Access 2007, and I have nothing but a bound textbox to a date field with no
input mask, and no other code.

Damon

 

[KARL DEWEY]

Access defaults to current year - 2009 - not 2004.

 

[Graham Mandeno]

Hi Karl

I think that's the point Damon is making. We understood Brenda (the OP)
wanted to not only *display* the date as mm/yy, but to *input* the date as
mm/yy.

Specifying a format of mm/yy for the textbox will achieve the first
objective, but not the second.