[AccessD] Using Between in a query

[Ryan W]

This seems to work for me:
StatusDate Between [Enter First Day of Month] AND DateAdd('s',-1,[Enter
Last day of Month])+1

I couldn't get INT(StatusDate) to work either with user entered strings.




On Mon, Aug 14, 2017 at 1:00 PM, Gustav Brock <gustav at cactus.dk> wrote:

> 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
>