[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