[AccessD] Query error

The query works on the 4 different machines I tried it on here. The client
is going to try some different machines at her facility.  I'll wait until
she gets back on her test results. It would be a real b---- if this is
machine specific.

Thank you for your time David. 

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

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