[AccessD] Query error

How about getting rid of the jexplicit join?

SELECT 
tblEventTime.ID, tblEventTime.EventTime, 
qryTimeSpan.compTimeID, qryTimeSpan.CompTime, qryTimeSpan.low, 
qryTimeSpan.high 
FROM 
tblEventTime, qryTimeSpan
WHERE 
tblEventTime.EventTime BETWEEN qryTimeSpan.low AND qryTimeSpan.high;

or avoid the BETWEEN as well

SELECT 
tblEventTime.ID, tblEventTime.EventTime, qryTimeSpan.compTimeID,
qryTimeSpan.CompTime, qryTimeSpan.low, qryTimeSpan.high 
FROM 
tblEventTime, qryTimeSpan
WHERE  
tblEventTime.EventTime >= qryTimeSpan.low AND
tblEventTime.EventTime  <=qryTimeSpan.high;



On 12 Nov 2009 at 13:30, Doug Murphy wrote:

> Hi David,
> 
> Not really. That is the point of the non-equi join, i.e., give the events
> that occur within the time spans defined in qryTimeSpan. There is not a
> specific field to join. I can't really test variants because the query works
> on all my machines. I was hoping someone on this list might have run into
> this and have a solution. 
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David McAfee
> Sent: Thursday, November 12, 2009 12:06 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Query error
> 
> WOW, I didn't have my coffee yet.
> 
> Is there anything in qryTimeSpan that can join to tblEventTime other that
> the timeSpan high & low range?
> 
> Can you change your BETWEEN to >= and <= for testing purposes?
> 
> 
> SELECT tblEventTime.ID, tblEventTime.EventTime, qryTimeSpan.compTimeID,
> qryTimeSpan.CompTime, qryTimeSpan.low, qryTimeSpan.high FROM tblEventTime
> INNER JOIN qryTimeSpan ON (tblEventTime.EventTime >= qryTimeSpan.low AND
> tblEventTime.EventTime <=qryTimeSpan.high);
> 
> 
> 
> On Thu, Nov 12, 2009 at 11:38 AM, Doug Murphy <dw-murphy at cox.net> wrote:
> > There are no nulls in the data. The strange thing is that it runs on 
> > all my machines, but not the clients. She is using my test mdb so we 
> > are using the same data. The other strange thing is that the result 
> > table from the query shows on her machine, but closes as soon as she
> closes the error window.
> > When I look this error message up on my Access help, GOGGLE, it seems 
> > that most of the instances where it is reported have to do with date/time
> fields.
> >
> > -----Original Message-----
> > From: accessd-bounces at databaseadvisors.com
> > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte 
> > Foust
> > Sent: Thursday, November 12, 2009 10:24 AM
> > To: Access Developers discussion and problem solving
> > Subject: Re: [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
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> 
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>