I am new to MS Access Forms so forgive me if this is a simple problem with a simple fix.
But anyway here goes: I have a form with a startdate and enddate fields. and a command button that will run a query based on the dates entered.
The query references these date fields with the following code in the criteria field:
Field: InvoiceDate
Table: qselProductSales
Total: Where
Criteria: Between ([forms]![frmProductManagersReport]![txtStart]) And ([forms]![frmProductManagersReport]![txtEnd])
The form pulls the data up fine when I input a date range (ie Startdate: 04/01/2006 EndDate 04/30/2006)
What I need it also to do with a another query based the same as the one above is to pull previous year from the dates entered above (i.e. so if I enter StartDate: 04/01/2006 EndDate: 04/30/2006 it should return data from
04/01/2005 and 04/30/2005)
I tried to use the criteria code:
Criteria: Between ([forms]![frmProductManagersReport]![txtStart]-365)) And ([forms]![frmProductManagersReport]![txtEnd]-365)
but I get the error : This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables. (Error 3071)
So I tried using a DateSerial(Year(([forms]![frmProductManagersReport]![txtStart],-1)) And DateSerial(Year([forms]![frmProductManagersReport]![txtEnd],-1)
Still get an error although it is different.
Does anyone have any ideas how I can pull the previous years data using current year dates..
Thanks for any held you can provide.
lpbruce
But anyway here goes: I have a form with a startdate and enddate fields. and a command button that will run a query based on the dates entered.
The query references these date fields with the following code in the criteria field:
Field: InvoiceDate
Table: qselProductSales
Total: Where
Criteria: Between ([forms]![frmProductManagersReport]![txtStart]) And ([forms]![frmProductManagersReport]![txtEnd])
The form pulls the data up fine when I input a date range (ie Startdate: 04/01/2006 EndDate 04/30/2006)
What I need it also to do with a another query based the same as the one above is to pull previous year from the dates entered above (i.e. so if I enter StartDate: 04/01/2006 EndDate: 04/30/2006 it should return data from
04/01/2005 and 04/30/2005)
I tried to use the criteria code:
Criteria: Between ([forms]![frmProductManagersReport]![txtStart]-365)) And ([forms]![frmProductManagersReport]![txtEnd]-365)
but I get the error : This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables. (Error 3071)
So I tried using a DateSerial(Year(([forms]![frmProductManagersReport]![txtStart],-1)) And DateSerial(Year([forms]![frmProductManagersReport]![txtEnd],-1)
Still get an error although it is different.
Does anyone have any ideas how I can pull the previous years data using current year dates..
Thanks for any held you can provide.
lpbruce