Griffiths, Richard
R.Griffiths at bury.gov.uk
Thu Jul 7 10:38:16 CDT 2005
I have now managed (I think) to get the correct SQL. If D1 is the date to check then I need to supply D2 as a parameter as well with D2= D1 + 1 day (so we get 07/07/2005 00:00 and 08/07/2005 00:00 for D1,D2) So we check if (UnavailableFrom between D1 and D2) or (UnavailableTo between D1 and D2) or (UnavailableFrom<=D1<= UnavailableTo) If any is true then we know the date (D1) falls between the date range UnavailableFrom and UnavailableTo Richard -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte Foust Sent: 07 July 2005 16:23 To: Access Developers discussion and problem solving Subject: RE: [AccessD] Re: A2K and .Net No, it wouldn't be. YourDate is the equivalent of 07/07/2005 00:00, since dates always have a time component and the default is midnight. So YourDate would be less than UnavailableFrom. Charlotte Foust -----Original Message----- From: Griffiths, Richard [mailto:R.Griffiths at bury.gov.uk] Sent: Thursday, July 07, 2005 1:13 AM To: Access Developers discussion and problem solving Subject: RE: [AccessD] Re: A2K and .Net Hi YourDate >= UnavailableFrom AND YourDate <= UnavailableTo does work for example UnavailableFrom=07/07/2005 09:30 UnavailableTo=07/07/2005 19:30 Yourdate=07/07/2005 YourDate >= UnavailableFrom is not satisfied Richard -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Robert L. Stewart Sent: 06 July 2005 19:11 To: accessd at databaseadvisors.com Subject: [AccessD] Re: A2K and .Net YourDate >= UnavailableFrom AND YourDate <= UnavailableTo That should work the same as BETWEEN...AND At 12:00 PM 7/6/2005, you wrote: >Date: Wed, 6 Jul 2005 10:06:21 +0100 >From: "Griffiths, Richard" <R.Griffiths at bury.gov.uk> >Subject: RE: [AccessD] A2K and .Net >To: "Access Developers discussion and problem solving" > <accessd at databaseadvisors.com> >Message-ID: <200507060857.j668vJr19891 at smarthost.yourcomms.net> >Content-Type: text/plain; charset="iso-8859-1" > >Ken >thanks, as I thought. I accept that you would need to install all the >VBA dlls, but are you sure that you would need to instantiate an Access >session. I'm sure most apps would use left, mid etc in queries and this >would mean say for all the many 1000's of VB apps that have an Access BE >they would need to install Access (runtime or full) and load an instance >each time a query was used. Have you tried this? > >I have tried to use native JetSQL but for this query have struggled , >maybe someone can offer a solution....... > > >I have two datetime fields UnavailableFrom and UnavailableTo (e.g. >01/01/2005 08:30 and 01/01/2005 18:30) > >Can anyone suggest any SQL (and also native JetSQL without function >calls [or with permitted function calls]) to find whether a date falls between >these two datetimes - so a parameter of say 01/01/2005 would return this >record. > >Thanks >Richard -- 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