[AccessD] Using Between in a query

Mon Aug 14 14:38:30 CDT 2017

You're right.  I was thinking about the comparison (BETWEEN) and not what
was being compared against.

On Mon, Aug 14, 2017 at 2:30 PM, McGillivray, Don <DMcGillivray at ctc.ca.gov>
wrote:

> Well, my proposal was to apply that conversion to the date in the table.
> So if the user specifies 8/1/2017 through 8/31/2017, and that range is
> compared to the date from the database with the time portion stripped off,
> any record whose date is on 8/31 - regardless of the time - will be
> included.  I think that's what Chester was aimed at. Or maybe I'm missing
> something . . .
>
>
>
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> Ryan W
> Sent: Monday, August 14, 2017 11:44 AM
> To: Access Developers discussion and problem solving <
> accessd at databaseadvisors.com>
> Subject: Re: [AccessD] Using Between in a query
>
> Don,
>   Testing that on my SQL Back end:
>
> SELECT DATEADD(dd,DATEDIFF(dd,0,'7/31/17 18:30'),0)
>
>
> Gives me this result:
>
> 2017-07-31 00:00:00.000
>
>
> Which, unfortunately doesn't help.. since that's the same issue Chester is
> having with dates to start with it seems.
>
> On Mon, Aug 14, 2017 at 1:37 PM, McGillivray, Don <DMcGillivray at ctc.ca.gov
> >
> wrote:
>
> > Is this a pass-through query?  If so, you will have to use a date
> > trimming function that the source database understands.  The Int()
> > function is Access-specific.
> >
> > Your table names suggest maybe a SQL server back end.  I found this at
> > Stack Overflow as one way to remove the time portion of a date/time in
> > SQL
> > server:
> >
> > DATEADD(dd, DATEDIFF(dd, 0, <YourDate>), 0)
> >
> > Gustav's suggestion may work too as a pass-through, but beware that
> > DateAdd in SQL server is a different flavor and will require some
> tweaking.
> >
> > -----Original Message-----
> > From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf
> > Of Gustav Brock
> > Sent: Monday, August 14, 2017 11:00 AM
> > To: Access Developers discussion and problem solving <
> > accessd at databaseadvisors.com>
> > Subject: Re: [AccessD] Using Between in a query
> >
> > To preserve a use of on index on dbo_DSS_StatusChanges.StatusDate you
> > could also use:
> >
> > WHERE dbo_DSS_StatusChanges.StatusDate >= [Enter First Day of Month]
> > And dbo_DSS_StatusChanges.StatusDate < DateAdd("d",[Enter Last Day of
> > Month])
> >
> > /gustav
> > ________________________________________
> > Fra: AccessD <accessd-bounces at databaseadvisors.com> på vegne af Kaup,
> > Chester <Chester_Kaup at kindermorgan.com>
> > Sendt: 14. august 2017 19:49:20
> > Til: Access Developers discussion and problem solving
> > Emne: Re: [AccessD] Using Between in a query
> >
> > Sounds like a good idea but I cannot get the Int function to work.
> > Here is the original SQL statement. Thanks.
> >
> > INSERT INTO [tbl Statuses During Prior Month] ( PID, Well_Number,
> > Status, StatusDate, RecordDate, StatusDate ) SELECT
> > dbo_DSS_StatusChanges.PID, dbo_DSS_StatusChanges.Completion_Name,
> > dbo_DSS_StatusChanges.Status, dbo_DSS_StatusChanges.StatusDate,
> > DateSerial(Year(Date()),Month(Date())-1,1)
> > AS Expr1, dbo_DSS_StatusChanges.StatusDate FROM dbo_DSS_StatusChanges
> >
> > WHERE ((dbo_DSS_StatusChanges.StatusDate) Between [Enter First Day of
> > Month] And [Enter Last Day of Month]))
> >
> > GROUP BY dbo_DSS_StatusChanges.PID,
> > dbo_DSS_StatusChanges.Completion_Name,
> > dbo_DSS_StatusChanges.Status, dbo_DSS_StatusChanges.StatusDate HAVING
> > (((dbo_DSS_StatusChanges.PID) Not In ("42415348690000","
> > 42415351160000")));
> >
> > -----Original Message-----
> > From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf
> > Of McGillivray, Don
> > Sent: Monday, August 14, 2017 12:04 PM
> > To: Access Developers discussion and problem solving
> > Subject: Re: [AccessD] Using Between in a query
> >
> > [This email message was received from the Internet and came from
> > outside of Kinder Morgan]
> >
> >
> > It's probably better to change the comparison so that the value from
> > the table doesn't include the time, rather than adding a day to the
> > end of the range.
> >
> > Something like :
> >
> > Int(TheDateFromTheTable) between StartDate and EndDate
> >
> > The Int() function removes the time portion from the date, allowing
> > you to use the actual range for the comparison.
> >
> > If you add a day to the end of the range, any records dated at
> > midnight of that additional day will be included in the results - not
> > exactly what you want.
> >
> >
> >
> > -----Original Message-----
> > From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf
> > Of Ryan W
> > Sent: Monday, August 14, 2017 9:35 AM
> > To: Access Developers discussion and problem solving <
> > accessd at databaseadvisors.com>
> > Subject: Re: [AccessD] Using Between in a query
> >
> > I should mention your interactive query could be "smart" and do [Enter
> > your end date]+1 to add a full day to the logic.
> >
> > On Mon, Aug 14, 2017 at 11:33 AM, Ryan W <wrwehler at gmail.com> wrote:
> >
> > > When you do not enter a time, 00:00 is assume.  So "BETWEEN" 7/30/17
> > > AND 7/31/17" means from 7/30/17 00:00 (midnight) until 7/31/17 00:00
> > > (midnight) so it does not account for the full day ahead.  They
> > > would need to enter
> > > 7/31/17 23:59 or 8/1/17
> > >
> > >
> > >
> > > On Mon, Aug 14, 2017 at 11:27 AM, Kaup, Chester <
> > > Chester_Kaup at kindermorgan.com> wrote:
> > >
> > >> I have an interactive query where the user is asked to put in a
> > >> start date and an end date. The query then uses the criteria of
> > >> Between start date and end date. I am having a problem of some
> > >> records not being retrieved. If the user puts in and end date of
> > >> 7/31/2017 a record with a date of 7/31/2017 1:30PM is not
> > >> retrieved. Does an end date of 7/31/2017 mean ending at 12:00 PM on
> > >> 7/30/2017? I guess I may not be fully understanding the use of
> Between.
> > --
> > 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
>