Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Efficient date entry 2

Status
Not open for further replies.

audiopro

Programmer
Apr 1, 2004
3,165
GB
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.
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
I need an efficient way of inputting the dates but I cannot even get the dates into the query.
Code:
	START_DATE=THIS.PARENT.TEXT4.VALUE
	FINAL_DATE=THIS.VALUE
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
 
Keith,

When you're designing the form, did you put anything in the Value property of the text boxes? I have:
Code:
=DATE()
in that property when I want to use a textbox for dates.

I've also put
Code:
DATE = BRITISH
in the config file for my app, which means you don't need to format the date in the textbox.

I can't see anything wrong in the way you are going about it. I too would have a textbox for one (or both) dates that I wanted to use in a SQL query. You don't in fact need to assign the textbox values to variables - you can refer directly to the Value property in the SQL:
Code:
    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>=THIS.PARENT.TEXT4.VALUE AND DDA.DATE<=THIS.VALUE;
        INTO ARRAY TOWTULLS

A slight aside - I wouldn't use DTOC or CTOD because, as the help for strictdate says:
Because the values returned by CTOD( ) and CTOT( ) rely on SET DATE and SET CENTURY to interpret the date string they contain, they are prone to year 2000 noncompliance errors. Use DATE( ) and DATETIME( ) with the optional numeric arguments to create Date and DateTime constants and expressions.

Hope that helps,

Stewart
PS If you want to get the best response to a question, please check out FAQ184-2483 first.
 
Code:
Use DATE( ) and DATETIME( ) with the optional numeric arguments
Does that mean?
Code:
DATE()+20
DATE()-35

I am using vars during testing but will tidy up once working. If I enter the date without ctod, I get an operator type mismatch error.
What is the physical difference between
Code:
"12/12/2006"
and
ctod("12/12/2006")
What is the best way to test the fields for valid dates?

Keith
 
DATE() / DATETIME() - no this refers to the optional arguments inside the brackets. For example DATE(2006,1,19).

"12/12/2006" - this is a character data type

ctod("12/12/2006") - returns a date data type

The operator type mismatch error happens when you try to compare 2 different data types.

I suggest that, in your test you quote in your first post, you change the first 2 lines to read
Code:
START_DATE=DATE(2006,12,15)
FINAL_DATE=DATE(2006,12,19)
Stewart
 
Thanks for the explanation.
I am not ignoring your advice, just developing what I have so far.
Code:
CHECKR=0
IF(DAY(CTOD(THIS.PARENT.TEXT4.VALUE)))>0
	IF(MONTH(CTOD(THIS.PARENT.TEXT4.VALUE)))>0
		IF(YEAR(CTOD(THIS.PARENT.TEXT4.VALUE)))>0
			CHECKR=1
		ENDIF
	ENDIF
ENDIF
IF(DAY(CTOD(THIS.VALUE)))>0
	IF(MONTH(CTOD(THIS.VALUE)))>0
		IF(YEAR(CTOD(THIS.VALUE)))>0
			CHECKR=CHECKR+2
		ENDIF
	ENDIF
ENDIF

DO CASE
	CASE CHECKR=0
		m=messagebox("Both Dates Invalid")
	CASE CHECKR=1
		m=messagebox("End Date Invalid")
	CASE CHECKR=2
		m=messagebox("Start Date Invalid")
	CASE CHECKR=3
*!*	Dates tested and passed - run query
		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>=CTOD(THIS.PARENT.TEXT4.VALUE) AND DDA.DATE<=CTOD(THIS.VALUE);
			INTO ARRAY TOWTULLS

		TOTAL_COST=TOWTULLS[2]
		TOTAL_SELL=TOWTULLS[1]
		TOTAL_PROFIT=TOTAL_SELL-TOTAL_COST
		THIS.PARENT.TEXT4.SETFOCUS
		THIS.BACKCOLOR=RGB(255,255,255)
ENDCASE
Using the args in DATE() directly works ok but when I try
Code:
IF(DAY(DATE(THIS.PARENT.TEXT4.VALUE)))>0
I get a 'too few arguments' error.
I could split the incoming string to get the components but is there a better way?


Keith
 
Keith,

Ah yes, I would expect that.

I think the root issue is that
Code:
THIS.PARENT.TEXT4.VALUE
is character type rather than date type.

When designing the form, what have you got in THIS.PARENT.TEXT4.VALUE? If you put {} in there, then the data type of the textbox becomes DATE not character and you then avoid having to use the CTOD function.

Stewart
 
Thanks Mike
Code:
 [i]Don't ever run a query and reference objects and/or properties[/i]
I usually assing vars but I have been changing all sorts of things during development

Code:
[i]
I seldom use arrays 
[/i]
I had reverted it back to a cursor as the array got lost when date not found.

The table I am using is not up to date so is now populated with the last date in the table. I was keeping it simple until the basics were in place. Putting a date in, on start up, still gave me a bad data type error. Stewart's suggestion of putting {} into the value did the trick though.





Keith
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top