silly date question

[jasn_78]

ok guys i know this is really stupid and silly but i cant see for the life of me what i am doing wrong i have a query where i want to only show records between to dates (including first and last)

when i do both or less than one date nothing shows using the code below

SELECT Sum(HTRXTBL.HTRX_QTY_1) AS QTY1, 
	Sum(HTRXTBL.HTRX_VALUE) AS VALUE1, 
	DateDiff("d",[Forms]![frmCOMPARISON]![txtDATEFROM1],[HTRXTBL]![HTRX_TRX_DATE]) AS [DAY]
FROM ITEMTBL INNER JOIN HTRXTBL ON ITEMTBL.ITEM_NUMBER = HTRXTBL.HTRX_ITEM_NUMBER
WHERE (((HTRXTBL.HTRX_REC_TYPE)="ITMSALE") 
	AND ((HTRXTBL.HTRX_TRX_DATE)>=[Forms]![frmCOMPARISON]![txtDATEFROM1] 
	And (HTRXTBL.HTRX_TRX_DATE)<=[Forms]![frmCOMPARISON]![txtDATETO1]))
GROUP BY DateDiff("d",[Forms]![frmCOMPARISON]![txtDATEFROM1],[HTRXTBL]![HTRX_TRX_DATE]);

when i do greater than the from date it this code below works

SELECT Sum(HTRXTBL.HTRX_QTY_1) AS QTY, 
	Sum(HTRXTBL.HTRX_VALUE) AS [VALUE], 
	DateDiff("d",[Forms]![frmCOMPARISON]![txtDATEFROM1],[HTRXTBL]![HTRX_TRX_DATE]) AS [DAY]
FROM ITEMTBL 
	INNER JOIN HTRXTBL ON ITEMTBL.ITEM_NUMBER = HTRXTBL.HTRX_ITEM_NUMBER
WHERE (((HTRXTBL.HTRX_TRX_DATE)>=[Forms]![frmCOMPARISON]![txtDATEFROM1]) 
	AND ((HTRXTBL.HTRX_REC_TYPE)="ITMSALE"))
GROUP BY DateDiff("d",[Forms]![frmCOMPARISON]![txtDATEFROM1],[HTRXTBL]![HTRX_TRX_DATE]);

i cant see what is different or missing help please

 

[boblarson]

Use the Between operator instead:

SELECT Sum(HTRXTBL.HTRX_QTY_1) AS QTY1, 
	Sum(HTRXTBL.HTRX_VALUE) AS VALUE1, 
	DateDiff("d",[Forms]![frmCOMPARISON]![txtDATEFROM1],[HTRXTBL]![HTRX_TRX_DATE]) AS [DAY]
FROM ITEMTBL INNER JOIN HTRXTBL ON ITEMTBL.ITEM_NUMBER = HTRXTBL.HTRX_ITEM_NUMBER
WHERE (((HTRXTBL.HTRX_REC_TYPE)="ITMSALE") 
	AND ((HTRXTBL.HTRX_TRX_DATE) BETWEEN [Forms]![frmCOMPARISON]![txtDATEFROM1] 
	And [Forms]![frmCOMPARISON]![txtDATETO1]))
GROUP BY DateDiff("d",[Forms]![frmCOMPARISON]![txtDATEFROM1],[HTRXTBL]![HTRX_TRX_DATE]);

 

[jasn_78]

bob still no luck on that one

 

[boblarson]

Where are you getting the SQL from and where are you using it?

 

[jasn_78]

just query designer in access bob