[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
>