[AccessD] Query error

The message seems to be saying that the AND portion of the SQL statement
is missing.  Is there any way that qryTimeSpan.high could be returning a
null or does it always return at least midnight?

Charlotte Foust 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Doug Murphy
Sent: Thursday, November 12, 2009 10:11 AM
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