Solved

TimeValue Criteria

Posted on 2013-10-31
7
361 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 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)
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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access Database 5 48
Access Query Top 20 from subquery 11 47
Using Access multi Listbox to pass criteria to a query 9 32
MS Access Query Criteria How To Handle Many ORs 4 26
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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…

696 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