SQL - Where Clause on Sub Join

Posted on 2006-04-09
Medium Priority
Last Modified: 2012-06-22

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
Question by:grafika
  • 2
LVL 39

Accepted Solution

thenelson earned 1000 total points
ID: 16411462
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.

Author Comment

ID: 16411518
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.

LVL 39

Expert Comment

ID: 16411737
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!


Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

750 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question