Solved

TimeValue Criteria

Posted on 2013-10-31
7
366 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

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!

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

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