TimeValue Criteria

Posted on 2013-10-31
Last Modified: 2013-10-31
Hi all,

I have a query that extracts the Time portion of a date/time field using TimeValue([Field]).

How do I specify the criteria now on that extracted portion to give me only the returned values of 12:00:00AM?

I have tried #12:00:00 AM# and TimeValue("12:00:00 AM") and all i get is data mismatch?

Question by:SeanNij
  • 5
  • 2
LVL 61

Expert Comment

ID: 39613771
Try this -

Subtract the integer part of the time, and convert back.

For the current date/time:

cdate(now() - int(now()))
LVL 61

Expert Comment

ID: 39613774
Or for a date/time field:

cdate(YourDateTimeField - int(YourDateTimeField))
LVL 61

Expert Comment

ID: 39613776
You can also use the Format function:

Format(YourDateTimeField, "hh:nn:ss")
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.


Author Comment

ID: 39613797
I am already extracting the time portion. I want to know what to use in criteria for 12 am.
LVL 61

Accepted Solution

mbizup earned 500 total points
ID: 39613811
Sorry - I think I totally misread your question.  

Give this a try for criteria to just return records with 12:00:00 AM as the time part:

WHERE TimeValue(YourField) = "12:00:00 AM"

(The where clause in SQL view)

Author Comment

ID: 39613826
HAH! That works!
LVL 61

Expert Comment

ID: 39613831
Glad to help out -

Sorry about all the earlier posts.  Need more caffeine.

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Documents and settings folder 30 78
Access Update Query 1 20
Object Variable or With Block Variable not set - Microsoft Access vba 3 34
Create macro from runcode 30 26
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question