[AccessD] Query error

Between is merely another way of saying >= and <=.  You can always substitute one for the other.

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 1:30 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Query error

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
>