[AccessD] Query error

That original SQL statement should work.
Did you try putting the between conditions in between parenthesis or
switching to >= and <= for testing purposes?


On Thu, Nov 12, 2009 at 1:30 PM, Doug Murphy <dw-murphy at cox.net> 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
>