none
Date/Time Query Problem RRS feed

  • Question

  • I wish I was better at this date/time stuff, I can never get it to work right!

    I have a access 2013 DB that has data entered into a date/Time field. The following Query works:

    SELECT IPMeasurementsChild.GageID, IPMeasurementsChild.DateTimeEntered, Int(CDate([DateTimeEntered])) AS TomsDate
    FROM IPMeasurementsChild
    WHERE (((IPMeasurementsChild.GageID) Is Not Null))
    ORDER BY IPMeasurementsChild.GageID DESC;

    The following query does not work, what am I doing wrong?

    SELECT IPMeasurementsChild.GageID, IPMeasurementsChild.DateTimeEntered, Int(CDate([DateTimeEntered])) AS TomsDate
    FROM IPMeasurementsChild
    WHERE (((IPMeasurementsChild.GageID) Is Not Null) AND ((Int(CDate([DateTimeEntered])))=#9/10/2014#))
    ORDER BY IPMeasurementsChild.GageID DESC;

    Wednesday, May 8, 2019 2:23 PM

Answers

  • A BETWEEN.....AND operation will not be reliable with values which include a non-zero time of day.  The way to do it is to return rows where the value is greater than or equal to the start date parameter, and less than the date following end date parameter.  With date/time parameters it is also particularly important that they be declared as such to avoid the possibility of the parameter values being misinterpreted as arithmetical expressions:

    PARAMETERS Forms!ReportMenu!StartDate DATETIME,
    Forms!ReportMenu!EndDate DATETIME;
    SELECT GageID, DateTimeEntered, DateValue(DateTimeEntered) AS TomsDate,
    GageID, DateTimeEntered
    FROM IPMeasurementsChild
    WHERE GageID IS NOT NULL
    AND DateTimeEntered >= Forms!ReportMenu!StartDate
    AND DateTimeEntered < Forms!ReportMenu!EndDate+1
    ORDER BY GageID DESC;

    Ken Sheridan, Stafford, England

    • Marked as answer by tkosel Friday, May 10, 2019 8:21 PM
    Wednesday, May 8, 2019 9:31 PM

All replies

  • There is no need to call the CDate function; the column is already a date/time data type:

    SELECT GageID, DateTimeEntered,
    INT(DateTimeEntered) AS TomsDate
    FROM IPMeasurementsChild
    WHERE GageID IS NOT NULL
    AND INT(DateTimeEntered) = #09/10/2014#
    ORDER BY GageID DESC;

    Or more intuitively:

    SELECT GageID, DateTimeEntered,
    DATEVALUE(DateTimeEntered) AS TomsDate
    FROM IPMeasurementsChild
    WHERE GageID IS NOT NULL
    AND DATEVALUE(DateTimeEntered) = #09/10/2014#
    ORDER BY GageID DESC;

    Ken Sheridan, Stafford, England

    Wednesday, May 8, 2019 3:00 PM
  • PS:  another way to cater for date/time values with a non-zero time of day would be:

    SELECT GageID, DateTimeEntered,
    DATEVALUE(DateTimeEntered) AS TomsDate
    FROM IPMeasurementsChild
    WHERE GageID IS NOT NULL
    AND DateTimeEntered >= #09/10/2014#
    AND DateTimeEntered < #09/11/2014#
    ORDER BY GageID DESC;

    Ken Sheridan, Stafford, England

    Wednesday, May 8, 2019 3:09 PM
  • Ken,

    As usual, I thank you for your help!  I cannot get either of your first two examples to work.  However, your third example works fine.  Now, I want to take it one step further.  I have a form with two date format text boxes, one for start date and one for end date.  I thought I could use them to specify a date range, but it doesn't work.

    SELECT IPMeasurementsChild.GageID, IPMeasurementsChild.DateTimeEntered, DateValue(DateTimeEntered) AS TomsDate, IPMeasurementsChild.[GageID], IPMeasurementsChild.[DateTimeEntered]
    FROM IPMeasurementsChild
    WHERE (((IPMeasurementsChild.[GageID]) Is Not Null) AND ((IPMeasurementsChild.[DateTimeEntered])>=[Forms]![ReportMenu]![StartDate] And (IPMeasurementsChild.[DateTimeEntered])<[Forms]![ReportMenu]![EndDate]))
    ORDER BY IPMeasurementsChild.GageID DESC;
    Can you point me in the right direction?  I tried to use the BETWEEN operator to no avail!

    Wednesday, May 8, 2019 8:36 PM
  • A BETWEEN.....AND operation will not be reliable with values which include a non-zero time of day.  The way to do it is to return rows where the value is greater than or equal to the start date parameter, and less than the date following end date parameter.  With date/time parameters it is also particularly important that they be declared as such to avoid the possibility of the parameter values being misinterpreted as arithmetical expressions:

    PARAMETERS Forms!ReportMenu!StartDate DATETIME,
    Forms!ReportMenu!EndDate DATETIME;
    SELECT GageID, DateTimeEntered, DateValue(DateTimeEntered) AS TomsDate,
    GageID, DateTimeEntered
    FROM IPMeasurementsChild
    WHERE GageID IS NOT NULL
    AND DateTimeEntered >= Forms!ReportMenu!StartDate
    AND DateTimeEntered < Forms!ReportMenu!EndDate+1
    ORDER BY GageID DESC;

    Ken Sheridan, Stafford, England

    • Marked as answer by tkosel Friday, May 10, 2019 8:21 PM
    Wednesday, May 8, 2019 9:31 PM
  • Ken,

    Doesn't return any values, I know there are values that qualify.

    Wednesday, May 8, 2019 10:28 PM
  • I can't explain the behaviour you are encountering.  There must be another factor or factors in play.  Are you sure that both parameter controls have been updated at the time of opening the query for example.

    Ken Sheridan, Stafford, England

    Thursday, May 9, 2019 11:03 AM
  • Ken,

    I think I may be getting closer.  Below are the controls on my form with values entered.

    I modified my query as below.

    PARAMETERS Forms!ReportMenu!StartDate DateTime, Forms!ReportMenu!EndDate DateTime;
    SELECT DateValue(DateTimeEntered) AS TomsDate, IPMeasurementsChild.GageID, IPMeasurementsChild.DateTimeEntered, DateValue([Forms]![ReportMenu]![StartDate]) AS test
    FROM IPMeasurementsChild
    WHERE (((IPMeasurementsChild.GageID) Is Not Null));

    This is what the query yields, somehow the date on the form gets changed.

    Thursday, May 9, 2019 2:52 PM
  • Your WHERE does not include the criteria in the PARAMETERS. So assuming the DateTimeEntered field is the criteria field, try:

    WHERE IPMeasurementsChild.GageID Is Not Null AND IPMeasurementsChild.TomsDate >= Forms![ReportMenu]![StartDate] AND IPMeasurementsChild.TomsDate <= Forms![ReportMenu]![EndDate];

    Thursday, May 9, 2019 3:28 PM
  • As Lawrence has pointed out, you've not referenced the parameters in the WHERE clause, so they'll play no part whatsoever in restricting the results.

    From the name of the form I take it that you are opening a form based on the query.  For an example of this take a look at MultiSelect.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.

    Select the 'Restrict Report's Underlying Query on One or More Fields' option in the opening menu.  This opens a dialogue form in which, amongst other things, start and end dates for an employee's appointment can be entered.  Opening the report with the button will return those employees appointed within the data range.  You'll see that the report's RecordSource is a query restricted in much the same way as the query whose SQL statement I posted earlier, so you should be able to model what you are attempting on this part of the demo.  Note that in my case the WHERE clause allows each parameter to be left Null, so the range can be open ended in either direction, or all controls can be left Null to return all rows.  Whether you wish to do similarly is for you to decide.

    Ken Sheridan, Stafford, England

    Thursday, May 9, 2019 4:54 PM
  • Hi

    try it like this

    WHERE (((IPMeasurementsChild.GageID) Is Not Null) AND ([DateTimeEntered]  between  #2014-10-09 00:00:00# and #2014-10-09 23:59:59#)

    hope that will help.

    The idea is in my country the date format is dd/mm/yyyy   and access except mm/dd/yyyy

    so to avoid problems, the utc format yyyy-mm-dd hh:mm:ss works all the time.

    One more thing is that the field hold hour min and seconds and you must address this in your Query

    for that the between the beginning of the day to end .

    Asaf

    Thursday, May 9, 2019 5:25 PM
  • WHERE (((IPMeasurementsChild.GageID) Is Not Null) AND ([DateTimeEntered]  between  #2014-10-09 00:00:00# and #2014-10-09 23:59:59#)

    Hi Asaf,

    You still did not cover the very last second of #2014-10-09#. I agree, the change that this happens is very low, but not zero.

    So I am in line with Ken not to use BETWEEN, but use the syntax: ….. >= #2014-10-09# AND ….. < #2014-10-10#.

    Imb.

    Thursday, May 9, 2019 8:41 PM
  • To all,

    Thanks for your help.  I am almost embarrassed to tell you what it was.  In my query, I reference the controls named StartDate and EnDate.  To my dismay, upon re-visitation they are actually named Start Date and End Date.  This has been corrected and most all of your suggestions work fine when I use the correct control names! 

    Sorry for my lack of attention and again, thanks for all your help!

    Friday, May 10, 2019 8:20 PM