[AccessD] Query error

This is a non-equi join, no "=". As I said it worked on all the computers I
tried it on and another the client has. I guess the problem she ran into was
specific to that one machine, which is bothersome. 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jack and Pat
Sent: Friday, November 13, 2009 4:47 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Query error

Doug,

I would have expected the syntax to include an "="  between matching fields
in the 2 tables and a Where clause  which would include the BETWEEN...
AND....
  - along the following (from my own example A2003)

SELECT CaseAssign.[Case ID], CaseAssign.[Assigned To], CustList.[Date 1st
Draft], CustList.[Date Closed] FROM CaseAssign INNER JOIN CustList ON
CaseAssign.CustNo = CustList.CustNo WHERE (((CustList.[Date 1st Draft])
Between #9/23/2009# And #10/16/2009#));

Jack

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Doug Murphy
Sent: Thursday, November 12, 2009 1:11 PM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Query error

 
Folks,

Hopefully someone here can provide some insite in why a client gets an error
on a query I created and I don't. The client wanted a way to search a large
table of event times and find those events that occure within a plus or
minus time of times in another table. I don't have the source tables so
created an example database and a set of sample tables.

The query is as follows.

SELECT tblEventTime.ID, tblEventTime.EventTime, qryTimeSpan.compTimeID,
qryTimeSpan.CompTime, qryTimeSpan.low, qryTimeSpan.high FROM tblEventTime
INNER JOIN qryTimeSpan ON tblEventTime.EventTime BETWEEN qryTimeSpan.low AND
qryTimeSpan.high;

This runs fine for me in Access 2003 and 2007 on my systems. The client runs
the sample on her computer with Access 2007 and gets an error message saying
"Between operator without And in query expression 'tblEventTime.EventTime
BETWEEN qryTimeSpan.low"

I have tried playing with trust levels in my copy of Access 2007 and this
doesn't make any difference. Any suggestions on what might be going on?  

Thanks in advance.

Doug

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com No virus found in this incoming
message.
Checked by AVG - www.avg.com
Version: 9.0.707 / Virus Database: 270.14.61/2498 - Release Date: 11/12/09
02:38:00


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com