Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MS Access SQL query inner join and where

Posted on 2010-09-13
14
Medium Priority
?
520 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
  • 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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

916 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