SQL - Where Clause on Sub Join


Could someone help me with the following bit of SQL.

SELECT Regions.RegionName, Count(Log.SessionID) AS CountOfSessionID
(Region2Country INNER JOIN Log
ON Region2Country.ISO3Code = IIF(Log.isoCountry3="", "-", Log.isoCountry3))
ON Regions.RegionShortName = Region2Country.RegionCode
WHERE Log.startDate > #01/03/2006#
AND Log.startDate < #08/03/2006#
GROUP BY Regions.RegionName;

The Where statement is having no affect at all, and my statistics are staying the same. No matter what dates i stick in.

The tables look like the following:
SessionID    startDate                       endDate                       isoCountry3
      1          31/03/2006 01:11:40     31/03/2006 03:15:40     GBR
      2          02/04/2006 00:00:00     02/04/2006 06:10:30     SWE

RegionShortName      RegionName
         EUR                    Europe
       SthAm               South America
       NthAm               North America

ISO 3 Code            RegionCode
     GBR                      EUR
    SWE                      EUR
    USA                     NthAm

Tim Pearson
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Use #yyyy/mm/dd# formatting in your query.  

Access is an American product so VBA and queries are sometime expecting mm/dd/yyyy even when the regional setting is set for something else.  yyyy/mm/dd will always work for and query input.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
grafikaAuthor Commented:
Cheers, always the obvious.  I hate access for that, it shows the dates in uk format, and it is even on my local computer where the regional settings are UK and yet it still messes up on a query when using uk date format.

You're welcome.  Glad to help and thank you very much for the points with "A" grade!

The reason parts of Access recognizes regional settings and other part do not is         because Access is a collection of pieces (SQL, VBA, forms & reports, etc.) made to work together.  That is also why bangs are used in some places and dots in others and functions like Nz, IIf work differently in VBA and SQL.

Happy Computing!

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.