Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

TimeValue Criteria

Posted on 2013-10-31
7
Medium Priority
?
377 Views
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?

Thanks
Sean
0
Comment
Question by:SeanNij
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
7 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39613771
Try this -

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

For the current date/time:

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

Expert Comment

by:mbizup
ID: 39613774
Or for a date/time field:


cdate(YourDateTimeField - int(YourDateTimeField))
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39613776
You can also use the Format function:

Format(YourDateTimeField, "hh:nn:ss")
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 1

Author Comment

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

Accepted Solution

by:
mbizup earned 2000 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)
0
 
LVL 1

Author Comment

by:SeanNij
ID: 39613826
HAH! That works!
Thankyou!!!
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39613831
Glad to help out -

Sorry about all the earlier posts.  Need more caffeine.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

636 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