datatype in the table should be Date/time or short tgext?

Question

I am desiging dataentryform in access . I made tables and the datatye of one field is Date/time but in property field in format I put mm.\yyyy to get month and year . how should I define input mask for it? I want to see --.---- when I enter data in form I can see calender beside this field but I have to choose day as well I dont want to choose day however the day that I chose doesnt save in table but I have to choose day aw well.

more explaination: I have 2 fields that should define as mm.yyyy .one field is (start of project example 01.2010) and the next field is end of project which also has the same format mm.yyyy (03.2015). and later I want to get a query which shows duration of project ( end of project - start of project) . so I should also do calculation according to my data. but I dont know what kind of data type should I define in the table for these two fields. at first I tried Date/Time . but I couldnt find suitable format. then I changed it to short text and I entered 00.0000 in input mask. what is the best solution because I have to calculate duration of project according to these two fields later? (in queries)

Answer 1

There are many ways around this. One is simply to allow the user to type freely in an unbound textbox, then validate/correct before updating.

Another is to have two/three juxtaposed textboxes for year-month(-day), then build the date value with DateSerial(y, m, d).

in VBA, mm/yyyy alone can never form a date value, so must silently add a day somehow.

Input masks should be avoided as they are a prime hate object for users.

If you insist, you can get an idea how to handle this from my article:

Entering ISO formatted date with input mask and full validation in Microsoft Access

It assumes the ISO sequence because year-month determines the possible day values.

Addendum:

Though decimal months is not an exact measure due to the varying day counts of the months, you will get pretty close with this function:

Public Function DecimalMonths( _
    ByVal Date1 As Date, _
    ByVal Date2 As Date) _
    As Double

    Dim Part1   As Double
    Dim Part2   As Double
    Dim Months  As Double
    Dim Days1   As Integer
    Dim Days2   As Integer

    Days1 = Day(DateSerial(Year(Date1), Month(Date1) + 1, 0))
    Days2 = Day(DateSerial(Year(Date2), Month(Date2) + 1, 0))

    Months = DateDiff("m", Date1, Date2)
    Part1 = (Day(Date1) - 1) / Days1
    Part2 = (Day(Date2) - 1) / Days2

    Months = Months - Part1 + Part2

    DecimalMonths = Months

End Function

Please note, that the count from 2017-04-15 to 2017-06-01 is 1.53

while it would be 1.5 from 2017-04-16 to 2017-06-01.