?
Solved

MS Access SQL query inner join and where

Posted on 2010-09-13
14
Medium Priority
?
513 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 1500 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
In this article, we’ll look at how to deploy ProxySQL.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

719 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