How do you format dates within MS Access Queries to prevent the US/UK issue

Question

How do I ensure that I pick up the right number of records when filtering for dates within an Access Query:

SELECT ID, REF, SalesDate, DCount("ID","tblRecords"," Ref='" & [Ref] & "' AND [SalesDate]=#" & format([SalesDate],"yyyy/mm/dd") & "#") as EXPR1 from tblCurrent 

It picks up the date ok if it cannot be misconstrued such as 28-04-12, but if it is 04-06-12 it doesn't pick it up as it's assuming it's the wrong way around.

Note that this query is not created on the fly or generated from a form etc...

Answer 1

I either use yyyy/mm/dd for dates in VBA:

#" & Format([SalesDate],"yyyy/mm/dd") & "#"

Or parameters, for building queries.

EDIT re additional information

Seeing you are using SQL server, I suggest you use a derived table, which you may find faster, for example:

SELECT dbo_Table_1.ADate, ACount FROM dbo_Table_1
LEFT JOIN (SELECT a.ADate,Count(*) As ACount 
           FROM  dbo_Table_1 As a GROUP BY a.ADate) b
ON dbo_Table_1.Adate=b.ADate

EDIT re discussion

SELECT * FROM dbo_vwRecordsCurrent As t 
LEFT JOIN (
   SELECT a.OpptyIncentiveModifiedDate, a.DataSetID, Count(*) AS ACount 
   FROM dbo_vwRecordsHistorical AS a 
   WHERE a.OpportunityIgnored = True 
   GROUP BY a.OpptyIncentiveModifiedDate, a.DataSetID) AS h 
ON t.OpptyIncentiveModifiedDate = h.OpptyIncentiveModifiedDate 
AND t.DataSetID = h.DataSetID

I have aliased your tables as the names are very long, so to me, it is more readable to use aliases on the outer sql. They are essential in the inner sql. It is not a good idea to alias a derived table with the name of an existing table.