display date on form as mm/yyyy

[pnasrallah]

I need to have forms and dates to display as mm/yyyy. The field in the table is set to "date/time". The field needs to be able to sort by date. The user does not need the 'day'. Having it will only confuse the end result. Any help would be appreciated

 

[wemeier]

Displaying a date as mm/yyyy is no big deal. However, the date stored on the table must be a real date. I've gotten around this by taking whatever the user enters and adding the 1st of the month to it before storing it.

For example, if the user enters 04/2003, I strip of the 04 (using the Left function) and the 2003 (using the Right function) then create a date of 04/01/2003. Store this date on the table, but use a display format of mm/yyyy so the user never sees the 01.

 

[pnasrallah]

I'm new at all this, so could you please be more specific as to how and strip using the left and right. I'm pretty much lost as to your explanation.

 

[dakota81]

If you have the value as a complete date stored in your table, here's a way you can display it how you want:

Have two text fields in your form. One of them is bounded to the date field like it already is. Let's call this field fldFullDate, and make it invisible. The other text box, say it's called fldDisplayDate, and have in it's control source:

=Month([fldFullDate]) & "/" & Year([fldFullDate])

If you want to go even further and allow the user to enter date values from that field, this will not work, but there are more involved solutions.

 

[pnasrallah]

The user will need to input date data back. So I guess I need the more involved answer also.

 

[danvlas]

Set the Format property to:
mm/yyyy

You will enter a full date, Access will display the month and year...

Good luck


Daniel Vlas
Systems Consultant

 

[wemeier]

If the user enters a date as mm/yyyy you can break it up like this:

Dim intSlash as Integer
Dim strMonth as String
Dim strYear as String
Dim dtmFullDate as Date

intSlash = InStr(Me.UserField,"/&quot
dtmFullDate = CDate("#" & Left(Me.UserField, intSlash - 1) & "/1/" & Mid(Me.UserField, intSlash + 1) & "#&quot

The InStr command returns what position in Me.Userfield (replace this with the name of your real form field) of the slash that the user entered.

The Left function returns x number of characters starting at the left and the Mid function, when used with only 2 parameters, returns all characters from the starting point specified to the end of the string.

The CDate function converts a string in the format #mm/dd/yy# into a real Access date variable.