SQL - Where Clause on Sub Join

Posted on 2006-04-09
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
    LVL 39

    Accepted Solution

    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

    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

    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

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    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…

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now