[AccessD] Query error

Hello Stuart,

Your version worked for me and the client, even on the macine that was
giving the error message. I guess that machine doesn't like the join.

Thank you again for your help.

Doug 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
Sent: Thursday, November 12, 2009 2:14 PM
To: Access Developers discussion and problem solving
Subject: Re: [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
> 



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