SUM query

[watz_uph]

hi all,

i have a little issue with SUM query from this table

UNIQUEID Date Production
engine1 01/12/1999 1440
engine1 01/01/2000 6061
engine1 01/02/2000 5152
engine1 01/03/2000 5347
engine1 01/04/2000 4642
engine1 01/05/2000 5264
engine1 01/06/2000 5247
engine2 01/07/1995 561
engine2 01/08/1995 3066
engine2 01/09/1995 2133
engine2 01/10/1995 2282
engine2 01/11/1995 2697
engine2 01/12/1995 3741
engine2 01/01/1996 4201
engine2 01/02/1996 4289
engine2 01/03/1996 4292
engine2 01/04/1996 4165

i want to SUM production from one UNIQUEID and date,

strQuery = "SELECT SUM(Production) FROM MONTHLYPROD WHERE UNIQUEID LIKE '"+engineName+"'";

when i use this query, i can get sum of production from engine1, but i want to make more specific, like SUM production from engine1 from date '01/12/1999' to '01,02,2000'

can anyone help me :D

[hielo]

Try:

strQuery = "
	SELECT SUM(`Production`) 
	FROM` MONTHLYPROD` 
	WHERE	(STR_TO_DATE(`DATE`,'%m/%d/%Y') 
				BETWEEN 
						STR_TO_DATE('01/12/1999','%m/%d/%Y') 
					AND 
						STR_TO_DATE('01,02,2000', '%m,%d,%Y') 
			) 
		AND (`UNIQUEID` LIKE '"+engineName+"')";

[watz_uph]

uh oh, it's not working :(
am i doing any wrong?
i am using visual studio 2008 with ms.access as the data source

and the error is :

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

Additional information: Undefined function 'STR_TO_DATE' in expression.

btw, thanks hielo for your reply

[hielo]

You posted this in MySQL. The syntax I gave you is for MySQL, NOT access.
Try:

strQuery = "
	SELECT SUM([Production]) as total
	FROM [MONTHLYPROD] 
	WHERE( 
			CDATE([DATE])
				BETWEEN 
						#01/12/1999# 
					AND 
						#01/02/2000# 
		) 
		AND ([UNIQUEID] LIKE '"+engineName+"')"

[watz_uph]

now everything works perfect :D
thanks for helping me hielo