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.
sej69Asked:
Who is Participating?
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for 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 Access MVP)Database ArchitectCommented:
"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 Access MVP)Database ArchitectCommented:
"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 Access MVP)Database ArchitectCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.