Griffiths, Richard
R.Griffiths at bury.gov.uk
Fri Jul 8 04:47:40 CDT 2005
Doris As per intial email, I can't use DateAdd as this is a VBA function and my vb.net FE/A2K BE will bomb out (not havinf MS Access/VBA etc installed) -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mike & Doris Manning Sent: 07 July 2005 16:59 To: 'Access Developers discussion and problem solving' Subject: RE: [AccessD] Re: A2K and .Net You don't need to supply D2 as a parameter, you could let the sproc calculate it for you. DECLARE D2 datetime SET D2 = DateAdd(d, 1, D1) Doris Manning Database Administrator Hargrove Inc. www.hargroveinc.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Griffiths, Richard Sent: Thursday, July 07, 2005 11:38 AM To: Access Developers discussion and problem solving Subject: RE: [AccessD] Re: A2K and .Net 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 -- 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