UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Query Date Criteria Not Working, Access 2016    
 
   
jpiper1921
post Mar 14 2019, 02:26 PM
Post#1



Posts: 53
Joined: 11-December 18



I have a NEW table (imported) from another database that has a Date Field (correction) "it has no formatting" but takes the appearance of General Date which of course includes time. Problem: my query fails using this criteria #3/14/2019#. I can put an exact entry such as, #1/14/2018 2:17:16 AM# and the criteria works.

I can use < Date() which pulls previous dates; however, I cannot use Date() to find today.

I have not had this issue with other tables/queries until this new table was created recently.

What might be going on?

Thanks,

John P
This post has been edited by jpiper1921: Mar 14 2019, 02:35 PM
Go to the top of the page
 
LPurvis
post Mar 14 2019, 03:00 PM
Post#2


UtterAccess Editor
Posts: 16,327
Joined: 27-June 06
From: England (North East / South Yorks)


Hi

You seem to need to negate the time component without harming your efficiency too much.
You could consider just surrounding the current date as criteria:

>= Date() AND < Date() + 1

Cheers

--------------------
Go to the top of the page
 
jpiper1921
post Mar 14 2019, 03:48 PM
Post#3



Posts: 53
Joined: 11-December 18



Solved it.... I wrapped the Date field into a Format([ReleaseDate],"Short Date") which forced it into a format that is now accepting my criteria.

Thanks... all better.

John P
Go to the top of the page
 
tina t
post Mar 14 2019, 05:07 PM
Post#4



Posts: 5,793
Joined: 11-November 10
From: SoCal, USA


QUOTE
I wrapped the Date field into a Format([ReleaseDate],"Short Date")

John, just a reminder that the Format() function returns a String (text) value, not a Date/Time value. something to keep in mind in case you find yourself wanting to do something with the return value as a date - you'd have to coerce the data type back to a Date/Time, or find another solution.

also keep in mind that using the Format() function in your query means that the function must run for every record in the source, in order to apply the criteria. probably not noticeable in smaller recordsets, but could slow down the querying of larger datasets. AFAIK, a function used in a criteria argument runs only once, and then the returned value is applied to the dataset. if that's correct, then i'd think that Leigh's solution would be faster.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
LPurvis
post Mar 15 2019, 04:52 AM
Post#5


UtterAccess Editor
Posts: 16,327
Joined: 27-June 06
From: England (North East / South Yorks)


Just agreeing with Tina's comment.
And yes, a function is called only once unless it contains a parameter which changes row by row (i.e. a field value).

The answer I gave wasn't random but deliberately SARGable. ;-)
It's good you investigated and found a method. Exploring is so important in learning. But there are often then more appropriate ways of achieving a goal that you also learn in time.

Cheers

--------------------
Go to the top of the page
 
jpiper1921
post Mar 15 2019, 12:56 PM
Post#6



Posts: 53
Joined: 11-December 18



UtterAccess works because of fine people like you both. I appreciate all input/advice.
Go to the top of the page
 
tina t
post Yesterday, 01:14 PM
Post#7



Posts: 5,793
Joined: 11-November 10
From: SoCal, USA


hello John, in response to your PM msg - first, some education on Date/Time in Access:

a Date/Time data type is actually a number; value to the left of the decimal point is the number of days since 12/30/1899, and value to the right of the decimal point is the number of seconds since midnight.

every date value in Access includes both a date and a time. period. if you enter only a time value in a Date/Time field, the date part of the value (to the left of the decimal point) is zero (0) - or, 12/30/1899. if you enter a only a date value in a Date/Time field, the time part of the value (to the right of the decimal point) is zero (0) - or, midnight, as in zero seconds into the date.

formatting has no effect on a date/time value stored in a table as Date/Time data type. doesn't matter where you apply the formatting - in the table field's Format property, in a form or report control's Format property, etc - the stored value does not change. 3/19/2019 10:45:37 AM can be formatted to show only the date, or only the time, or the day of the week, or the quarter of the year, or the hour in military time, yada yada yada - none of that changes the stored value 3/19/2019 10:45:37 AM .

using the Format() function returns a String value, even if you format it to look like a date/time value. but, to be clear, using the Format() function in a query returns a calculated value - the String value - in the query. it does not change the value stored in the table, unless you specifically write that value back to the table. a SELECT query does not affect table data by itself, so using the Format() function to create a calculated field in a query will not harm the data you have stored in a table.

okay, hopefully i haven't been too confusing above. and hopefully this helps you understand why your query for a date value did not work - the reason you posted originally. when date/time values that include specific hours/minutes/seconds are stored in a table, you can't get a specific record identified by the date/time, unless you query the exact time that's stored. so 3/19/2019 10:45:37 AM will never be returned by a criteria saying MyDateTimeFieldName = #3/19/2019# (remember, when no time is specified, the time is midnight) - because the equal sign (=) requires an exact match of data, and
3/19/2019 10:45:37 AM does not equal 3/19/2019 00:00:00 AM

as you've found, using Format() function does not help because it converts the Date/Time value to a string. but you're ahead now, because you understand what is actually stored in the Date/Time field in your table. (and btw, recommend you never apply a specific format to a table field, because it can obscure what values are really stored in the field. save formatting for display - forms and reports.)

so, having said all the above, i'd recommend that you use the solution that Leigh posted in this thread: change your query criteria to encompass the "whole" target date, as

>= Date() AND < Date() + 1

the Date() function returns today's date, with no time specified - in other words, midnight. (note that the Now() function returns the current date and time, so it's better to not use that in Access unless you specifically care what the time is, as well as what the date is.) so let's translate the criteria line above, as

greater than or equal to 3/19/2019 00:00:00 AM AND less than 3/20/2019 00:00:00 AM

that gives you every possible time between midnight this morning and midnight tomorrow morning - the entire 24 hours of 3/19/2019. so effectively, it doesn't matter what the time is in the date/time values, you'll get back everything with a date of 3/19/2019.

the easiest solution, and completely accurate, when you understand what data is actually stored in your table field, and how to query against it.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
LPurvis
post Yesterday, 02:11 PM
Post#8


UtterAccess Editor
Posts: 16,327
Joined: 27-June 06
From: England (North East / South Yorks)


Hi

Some good info for you there from Tina.
I'd just suggest one teeny alteration.

>> ... and value to the right of the decimal point is the number of seconds since midnight.

To my mind, the easiest way to think of the decimal component is really just a continuation of the integer component.
If you think of the entire number (FWIW it's a double data type) as the number of days since 1899-12-30, then you're there. The decimal component represents a partial day. (The amount of a day that has passed - which is then expressed in Time format.)

So 0.5 is half a day. It is, therefore, 12:00 noon.
0.75 is three quarters, and so is 18:00.
0.99 is 23:45:36.
(That last one isn't as convenient, but is indicative of the conversion.)

You can, of course, determine the number of seconds since midnight (how far through the day you are measured in seconds) by multiplying the decimal component by 86400 (the number of seconds in a day) and you're off to the races in that regard also. Though it's not the number of seconds by default.

Hope that helps too.

Cheers

--------------------
Go to the top of the page
 
jpiper1921
post Yesterday, 03:43 PM
Post#9



Posts: 53
Joined: 11-December 18



I had no idea. OK my heads hurts a little. confused.gif I will read both responses a few times, study and formulate my action. Much appreciation.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th March 2019 - 12:57 AM