The following code calculates total sales, total cost price and total profit for a give period within a table. This works but I am having problems setting the range of dates.
This code, used for testing, has the dates hard coded and works perfectly.
I need an efficient way of inputting the dates but I cannot even get the dates into the query.
I have been CTODing and DTOCing but to no avail.
I am using two text boxes Format='E' but the var is being rejected as wrong format.
This is frustrating but I need to address the wider issue of allowing dates to be entered into an app.
As a general question, Is there a standard way of inputting dates for use in such queries where the previous date in one of the fields can be retained to save repeated typing over multiple queries? I am sure that this can be done with the controlsource property but some advice on the overall problem would be appreciated.
Keith
This code, used for testing, has the dates hard coded and works perfectly.
Code:
[red]START_DATE=CTOD("15/12/2006")
FINAL_DATE=CTOD("19/12/2006")
[/red]
USE IN SELECT("DDA")
USE Z:\D_DAY IN 0 ALIAS DDA
SET ANSI ON
SELECT NVL(SUM(IIF(INLIST(DDA.CODE,"PAID_OUT","PAID_IN","PAY"),00000000.00,(DDA.EACH/1.175)*DDA.QTY)),0) AS SALES,;
NVL(SUM(IIF(INLIST(DDA.CODE,"PAID_OUT","PAID_IN","PAY"),00000000.00,ASTOCK.COST*DDA.QTY)),0) AS COST;
FROM DDA INNER JOIN ASTOCK;
ON DDA.CODE=ASTOCK.CODE;
WHERE DDA.DATE>=START_DATE AND DDA.DATE<=FINAL_DATE;
INTO ARRAY TOWTULLS
TOTAL_COST=TOWTULLS[2]
TOTAL_SELL=TOWTULLS[1]
TOTAL_PROFIT=TOTAL_SELL-TOTAL_COST
THISFORM.REFRESH
Code:
START_DATE=THIS.PARENT.TEXT4.VALUE
FINAL_DATE=THIS.VALUE
I am using two text boxes Format='E' but the var is being rejected as wrong format.
This is frustrating but I need to address the wider issue of allowing dates to be entered into an app.
As a general question, Is there a standard way of inputting dates for use in such queries where the previous date in one of the fields can be retained to save repeated typing over multiple queries? I am sure that this can be done with the controlsource property but some advice on the overall problem would be appreciated.
Keith