Bob Heygood
bheygood at abestsystems.com
Fri Nov 13 09:18:04 CST 2009
Hey Doug, I would look at something like how that one particular machine is set up in Windows in regards to Time/Date. If in fact your data types are truly time/dates. Hope this helps, Bob Heygood -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Doug Murphy Sent: Friday, November 13, 2009 6:49 AM To: 'Access Developers discussion and problem solving' Subject: Re: [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 -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com