Doug Murphy
dw-murphy at cox.net
Fri Nov 13 09:43:02 CST 2009
Hi Bob, I am sure there is something there that causes the problem. Hope it isn't a service pack thing. This was supposed to be a quick response to how can I do this one time task to check a huge data file. Don't have the time to try and fix the problem with that machine. We got it working on another machine so forward to the next challenge. Thanks for your thoughts. Doug -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bob Heygood Sent: Friday, November 13, 2009 7:18 AM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Query error 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 -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com