If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Year to date month/year
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?" |
#2
|
|||
|
|||
Year to date month/year
On Mon, 22 Jun 2009 08:30:01 -0700, Kim wrote:
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()) -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|