Remove Time from a Date/Time field

[Vergy39]

I have read several threads related to this problem and have tried them all. From DateValue to CDate, to others. None have worked. I need to create a query that will count the number of items that meet certain criteria. This is what I currently have:


SELECT Count(*) AS [#Closed Debt Consolidation]
FROM dbo_SU_Data
WHERE (((dbo_SU_Data.AcctSaved)="No") And ((dbo_SU_Data.ClosedAcct)="Debt Consolidation") And ((dbo_SU_Data.rcdCreateDate) Between 6/27/2011 12:01am And 6/27/2011 11:59pm));

But it only returns the number. I need a column in front of this number that specifies the date only. Everything that I have tried has failed. Any assistance is greatly appreciated. The column name that holds the date/time data is called rcdCreatDate and is a Date/Time field.

Thanks
David V

 

[Galaxiom]

SELECT Format(dbo_SU_Data.rcdCreateDate,"dd/mm/yy") AS somename, Count(*) AS [#Closed Debt Consolidation]
FROM dbo_SU_Data
WHERE (((dbo_SU_Data.AcctSaved)="No") And ((dbo_SU_Data.ClosedAcct)="Debt Consolidation") And ((dbo_SU_Data.rcdCreateDate) Between #6/27/2011 00:00:00# And #6/27/2011 23:59:59#))
GROUP BY dbo_SU_Data.rcdCreateDate;

 

[Galaxiom]

I would also recommend you change the ClosedAcct field to a integer datatype and use a lookup to disply the text. It is much faster to query that structure.

Also AcctSaved should probably be a Boolean rather than text.

 

[Vergy39]

Thanks Galaxiom, this is perfect. I have to create another query to sum the numbers, but this will do fine. I thought that I had tried this once before but may have done something wrong.

Again, thank you so much.

David V