Date Format Problem - MS Access 97

[salisha]

Hey all,

I have this date field and I need to put an input mask on it in the format of YYYYMM. I do not need the day part of the date.
> >My question is, in the design view for the table for the data type can I still use date/time as the datatype even though I am not using the format of short date or medium date.
Also would i format this field in the inputmask field or format field. I hope this question makes sense!

 

[psemianonymous]

Access won't let you input a "Date/Time" value unless you include day/month/year. If you have your data in YYYYMM format, and that's all you need, you're going to have to make some sort of workaround whereby it adds some arbitrary day part to your data after you enter YYYYMM. This gets messy, though, so you might just want to switch to Text data type and work from there.

Let me just recap:
1. Viewing a Date/Time field in YYYYMM format is as easy as typed: just put "YYYYMM" in the Format property of the table design (as well as query design, control properties on any applicable form/report)
2. Setting the input mask to ###### is possible, but not for Date/Time fields. You will have to either use two controls (a hidden one that stores the data, and the intermidiary that you type data into) - but again this is problematic.
3. Setting up your field as Text will add the problem of data validation (you have to ensure that the last two digits fall in between 01-12, and the year value is reasonable), and automatic formatting ability in reports and forms disappears when you change the type to Text, but such is life.

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[TonyJollans]

A slight correction to foolio12's answer. Access WILL let you input a date without a day. Using a format of "yyyy-mm" or "yyyy mm" and entering dates as, say, "2003-05" or "2003 05" works fine, defaulting the day to 1. However it does not, as stated, allow "yyyymm" - I don't know why.

Enjoy,
Tony

 

[salisha]

Thank you guys very much for your help. Now that I know this, it seems simple enough.

Salisha

 

[salisha]

Actually, let me see if i have this correct. I can make my date field a Text Data Type, and in the format property for the field I am going to enter YYYYMM? Is that how it is going to work? But if i make it a text field, I am going to have to put in some sort of validation for the month?????