• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 522
  • Last Modified:

MS Access SQL query inner join and where

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
sej69
Asked:
sej69
  • 5
  • 4
  • 4
  • +1
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
kingjelyCommented:
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
 
kingjelyCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
rockiroadsCommented:
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
 
sej69Author Commented:
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
 
sej69Author Commented:
Oh.  If I look at the field in the table, the data is stored as D/MM/YYYY
0
 
kingjelyCommented:
Take out the where clause, and select * like you have.

Look in your tblUserSync.mntDate column.. what dates are in there?
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"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
 
kingjelyCommented:

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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"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
 
sej69Author Commented:
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
 
sej69Author Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
sej69Author Commented:
I should put the link that datamx helped me find:
http://support.microsoft.com/kb/210276
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 4
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now