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
> Help Me Understand Datevalue And Timevalue On A Field Populated With Now(), Access 2013    
 
   
ccIces
post Aug 7 2018, 08:39 AM
Post#1



Posts: 590
Joined: 28-August 02
From: Belle River, Ont, Canada


I have a field that is date stamped with the Now() function which shows a date/time value.
I would like the user to be able to enter a date and a time in separate text boxes on a form and preform a search for all records of that date <= the time requested. The form is set to record the input as date/time value using format.

When I build a test query, if I enter the criteria for the DateChange Field as #<a valid date># I get no records returned.
If I set the field to JustTheDate: Int([DateChange]) the criteria works.
If I set the field to JustTheDate: DateValue([DateChange]) I get a type mismatch error.

What is the correct way to specify a criteria to search a date/time field? What would the function to retrieve just the time be (MOD?)?
Thanks in advance.
Go to the top of the page
 
GroverParkGeorge
post Aug 7 2018, 08:52 AM
Post#2


UA Admin
Posts: 33,259
Joined: 20-June 02
From: Newcastle, WA


Here's some information that might help.

Whether you specify a time portion or not (e.g. using Now() returns both the current date and the current time on that date, Date() returns the current date AND the clock time 00:00:00 or midnight of that date), all datetime fields in an Access table DO contain both the date and time. If there is no specific time included, it defaults to 00:00:00 on that date.

Therefore, when you need to do a comparison that takes into consideration both a date and a clock time on that date, you need to account for both elements, not only in the parameters specified, but also in the datetime field being checked.

You are storing both date and non-zero time when using Now(). Therefore you must specific criteria that accounts for the time, whether you want it or not.

DateChange Field as #<a valid date># probably won't return anything because it is comparing something like, for example, 2018/08/06 00:00:00 to dates with non-zero times like 2018/08/06 03:22:19, or 2018/08/06 09:02:44 That means no matches and no records returned.

To get a match on date only, you have to select out only the date portion of the datetime field in question:

Perhaps something like DateValue([2018/08/00 03:33:19]) so that ONLY the date portion is used to compare to only a date portion of the criteria date:

The same is true when you want to do a time comparison using separate date and time criteria. Each has to be parsed, prior to the comparison, to make sure you are comparing only the date portion of the datetime field to only the date portion of the criterion, and only the time portion of the datetime field to only the time portion of the criterion.

The problem is, that by default, the DISPLAY FORMAT used in both fields in tables and controls on forms only DISPLAY a part of the actual datetime values involved, so you must work directly with the stored values, not with the DISPLAYED FORMAT of those values.
This post has been edited by GroverParkGeorge: Aug 7 2018, 08:54 AM

--------------------
Go to the top of the page
 
ccIces
post Aug 7 2018, 10:03 AM
Post#3



Posts: 590
Joined: 28-August 02
From: Belle River, Ont, Canada


Thanks George.
I understand your explanation but I can't seem to grasp how to do the comparison. In the table, the filed DateChange records the value from the Now() function.
When I build a test query on this field, I want to compare the criteria of a date and a time to this field. Would I need to concatenate the inputs from the form (separate text box values) to establish the criteria to do the comparison?
I get a type mismatch error when I try to change the DateChange value by making it an expression to only compare the date part of it.
CODE
SELECT tblAudit.RecordID, DateValue([DateChange]) AS JustTheDate
FROM tblAudit
GROUP BY tblAudit.RecordID, DateValue([DateChange])
HAVING (((DateValue([DateChange]))=#7/20/2018#));


But this works
CODE
SELECT tblAudit.RecordID, Int([DateChange]) AS JustTheDate
FROM tblAudit
GROUP BY tblAudit.RecordID, Int([DateChange])
HAVING (((Int([DateChange]))=#7/20/2018#));


I know the INT function is removing the time portion (or decimal value) of the DateChange field so I believe I am just comparing the date even though the time is there as .000
but doesn't DateValue function do the same? Why the type mismatch?
Go to the top of the page
 
GroverParkGeorge
post Aug 7 2018, 11:47 AM
Post#4


UA Admin
Posts: 33,259
Joined: 20-June 02
From: Newcastle, WA


It ought to work the same, yes.

--------------------
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    19th August 2018 - 12:49 PM