SQL - Where Clause on Sub Join

Hi,

Could someone help me with the following bit of SQL.

SELECT Regions.RegionName, Count(Log.SessionID) AS CountOfSessionID
FROM Regions INNER JOIN
(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:
Log:
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

Regions:
RegionShortName      RegionName
         EUR                    Europe
       SthAm               South America
       NthAm               North America

Region2Country:
ISO 3 Code            RegionCode
     GBR                      EUR
    SWE                      EUR
    USA                     NthAm


Thanks
Tim Pearson
         
grafikaAsked:
Who is Participating?
 
thenelsonCommented:
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.
0
 
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.

Thanks
Tim
0
 
thenelsonCommented:
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!

Nelson
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.