I have a table that stores our gross sales per month. There are two fields - The first is the date that the gross sales were recorded stored as a date/time field formatted "mm/yyyy". The other field is the gross sales field (currency). The problem that I am having is with the Between...And function. I have a query that pulls gross sales values from the table to show the total sales in a given period (for tax purposes). Last year, this query worked fine with the following criteria:
Between [Forms]![frmCalculateTax]![BeginDate] And DateAdd("m",[Forms]![frmCalculateTax]![EndDate],1)
The [BeginDate] and [EndDate] fields are both unbound fields on a form ([frmCalculateTax]) that are also formatted as date/time "mm/yyyy".
It still works when I request a full calendar year (in this instance 1/2001 - 12/2001), but when I ask for a quarter (10/2001 - 12/2001) the query does not pick up the first month. No matter how I modify the [BeginDate] in the between statement, I can't seem to pick up the first month. I thought that this type of statement was supposed to be inclusive of the values it was given, but this doesn't seem to be the case.
It's probably something simple, but I can't figure it out. What's going on?
Between [Forms]![frmCalculateTax]![BeginDate] And DateAdd("m",[Forms]![frmCalculateTax]![EndDate],1)
The [BeginDate] and [EndDate] fields are both unbound fields on a form ([frmCalculateTax]) that are also formatted as date/time "mm/yyyy".
It still works when I request a full calendar year (in this instance 1/2001 - 12/2001), but when I ask for a quarter (10/2001 - 12/2001) the query does not pick up the first month. No matter how I modify the [BeginDate] in the between statement, I can't seem to pick up the first month. I thought that this type of statement was supposed to be inclusive of the values it was given, but this doesn't seem to be the case.
It's probably something simple, but I can't figure it out. What's going on?