[AccessD] Using Between in a query

[McGillivray, Don]

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