Year to date month/year

[Kim]

I am creating a database table and query for billing that uses the date form
month/year, or mm/yy. I want to be able to filter to "year-to-date" in order
to show all billing from the past to the current term. I do have some billing
entered that is projected with a projected date, so I want the ability to
filter out these projections. First, does anyone know of an input mask that
would recognize this format as a month/year rather than just numbers? I was
only able to find the Short date which also includes the day. Also does
anyone know a function to filter to just "year-to-date?"

 

[John W. Vinson]

I am creating a database table and query for billing that uses the date form
month/year, or mm/yy. I want to be able to filter to "year-to-date" in order
to show all billing from the past to the current term. I do have some billing
entered that is projected with a projected date, so I want the ability to
filter out these projections. First, does anyone know of an input mask that
would recognize this format as a month/year rather than just numbers? I was
only able to find the Short date which also includes the day. Also does
anyone know a function to filter to just "year-to-date?"

A Date/Time field is stored as a precise point in time (it's actually stored
as the number of days since midnight, December 30, 1899). As such, May 2009
isn't a date - it's 31 of them, and/or billions of possible specific instants
of time. You can *DISPLAY* any date in that month as mm/yy by simply using the
mm/yy format, but if you want to sort or search chronlogically you really
should use a Date/Time value. With that, year to date is really easy:

BETWEEN DateSerial(Year(Date()), 1, 1) AND Date()

will get all records so far this year.

Alternatively you might use two integer fields for the year and month
respectivelly (don't use the reserved words Year and Month for the fieldnames
though), and combine them for display purposes. Year To Date would need a
criterion like

[Yearfield] = Year(Date()) AND [Monthfield] <= Month(Date())