Solved

MS Access SQL query inner join and where

Posted on 2010-09-13
14
507 Views
Last Modified: 2012-05-10
I'm trying to get a query to work...

SELECT tblUserSync.*, tblSyncSchoolInfo.*
FROM tblUserSync
INNER JOIN tblSyncSchoolInfo
         ON tblUserSync.szSchoolID = tblSyncSchoolInfo.szSchoolID  
WHERE tblUserSync.mntDate >= 09/12/2010;

The first part of the query works fine.  The where clause doesn't seem to eliminate any records so it doesn't look like its working.
0
Comment
Question by:sej69
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 4
  • +1
14 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 500 total points
ID: 33668830
If your date field includes Time also (?) ... try this:

SELECT tblUserSync.*, tblSyncSchoolInfo.*
FROM tblUserSync
INNER JOIN tblSyncSchoolInfo
         ON tblUserSync.szSchoolID = tblSyncSchoolInfo.szSchoolID  
WHERE DateValue(tblUserSync.mntDate) >= 09/12/2010;

mx
0
 
LVL 8

Expert Comment

by:kingjely
ID: 33668856
Hi
What if you change the date?

Could it be

1) the date format is? 'D, M, Y', not  'M,D,Y'
  Which would mean date is greater then or equil too the '9th of december 2010'

so try WHERE tblUserSync.mntDate >= 12/09/2010

Or the operator may be wrong?

<=09/12/2010 ?

The actual query is fine.. just adjust the date and operator


maybe try
SELECT tblUserSync.*, tblSyncSchoolInfo.*
FROM tblUserSync
INNER JOIN tblSyncSchoolInfo
         ON tblUserSync.szSchoolID = tblSyncSchoolInfo.szSchoolID  
WHERE tblUserSync.mntDate between '2010-12-08' and '2010-12-09' ;

0
 
LVL 8

Expert Comment

by:kingjely
ID: 33668939
By the way, the WHERE above, I ment test for a month, not literally '2010-12-08'  and '2010-12-09'

Althought that may work in mysql, not sure about access ;)

kj
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 65

Expert Comment

by:rockiroads
ID: 33669008
or one I tend to use

SELECT tblUserSync.*, tblSyncSchoolInfo.*
FROM tblUserSync
INNER JOIN tblSyncSchoolInfo
         ON tblUserSync.szSchoolID = tblSyncSchoolInfo.szSchoolID  
WHERE Format(tblUserSync.mntDate,"mm/dd/yyyy" >= #09/12/2010#

or

SELECT tblUserSync.*, tblSyncSchoolInfo.*
FROM tblUserSync
INNER JOIN tblSyncSchoolInfo
         ON tblUserSync.szSchoolID = tblSyncSchoolInfo.szSchoolID  
WHERE Format(tblUserSync.mntDate,"YYYYMMDD") >= 20100912
0
 

Author Comment

by:sej69
ID: 33669018
MX: That still didn't work...  The field is set to date/time (there is no date field in Access as far as I can see...) but the time is not in the field.  If I open the table and look at it all I see is the date.

King: That didn't work either...  I tried both variations.  However, if I turn the operator around to less than equal  <= then I do not get any records which is weird.
0
 

Author Comment

by:sej69
ID: 33669025
Oh.  If I look at the field in the table, the data is stored as D/MM/YYYY
0
 
LVL 8

Expert Comment

by:kingjely
ID: 33669033
Take out the where clause, and select * like you have.

Look in your tblUserSync.mntDate column.. what dates are in there?
0
 
LVL 75
ID: 33669044
"the data is stored as D/MM/YYYY"
Not really.  That is the Format that *only* displays the date that way.  It has *nothing* to do with how a date/time value is stored internally, which is a double precision number, date to the left of the decimal, and time to the right of the decimal.

mx
0
 
LVL 8

Expert Comment

by:kingjely
ID: 33669045

Yea so as per my comment  
09/14/10 11:59 AM, ID: 33668856

1) The date format is? 'D, M, Y', not  'M,D,Y'
  Which would mean date is greater then or equil too the '9th of december 2010'

One problem is that
It's not December yet..

0
 
LVL 75
ID: 33669055
"there is no date field in Access as far as I can see"
Well, yes there is ... Date/Time data type, can store date, time or both.

"If I open the table and look at it all I see is the date."
Open the table in design view.
Click on your date Field.
REMOVE anything in the Format property.
Save and Close, then open in data sheet view.

What do you see in the date field?

mx
0
 

Author Comment

by:sej69
ID: 33669108
mntDate  
----------
8/9/2010
8/9/2010
6/28/2010

...

etc


I've worked with dates before and know the "double" number.  However, I can't see how it's used in Access (not that it's not; I just can't see any way of seeing a numeric value)

0
 

Author Closing Comment

by:sej69
ID: 33669119
DataMX, I'm going to award you the points.  You were very close to the actual query, your track on the dateValue was accurate but missplaced.

This is the working query:

SELECT tblUserSync.*, tblSyncSchoolInfo.* FROM tblUserSync INNER JOIN tblSyncSchoolInfo ON tblUserSync.szSchoolID = tblSyncSchoolInfo.szSchoolID   WHERE tblUserSync.mntDate >= DateValue("9/12/2010");

The DateValue needed to be around the actual date.

Thanks guys, much appreciated.
0
 
LVL 75
ID: 33669135
Yes, what I *should* have posted was:


SELECT tblUserSync.*, tblSyncSchoolInfo.*
FROM tblUserSync
INNER JOIN tblSyncSchoolInfo
         ON tblUserSync.szSchoolID = tblSyncSchoolInfo.szSchoolID  
WHERE DateValue(tblUserSync.mntDate) >= #09/12/2010#

or

SELECT tblUserSync.*, tblSyncSchoolInfo.*
FROM tblUserSync
INNER JOIN tblSyncSchoolInfo
         ON tblUserSync.szSchoolID = tblSyncSchoolInfo.szSchoolID  
WHERE DateValue(tblUserSync.mntDate) >= DateValue("09/12/2010")

mx

0
 

Author Comment

by:sej69
ID: 33669174
I should put the link that datamx helped me find:
http://support.microsoft.com/kb/210276
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
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.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

622 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