Date and time query

I have a table that records time-stamps with the date and time contained in 
the same column.  How do I query these fields to only show the time or only 
show the dates?   

LASTUPDATEDT
4/23/2007 11:48:30 AM
0
Utf
5/1/2007 8:34:01 PM
access 16762 articles. 3 followers. Follow

2 Replies
1006 Views

Similar Articles

[PageSpeed] 10

For same date:

WHERE DateValue(MyDateTimeField) = #04/23/2007#

or

WHERE MyDateTimeField BETWEEN #04/23/2007# AND #04/24/2007#

For same time:

WHERE TimeValue(MyDateTimeField) = #11:48:30#

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"mpcoon" <mpcoon@discussions.microsoft.com> wrote in message 
news:89547704-8235-4B9D-BE0A-BF4D87B0BB59@microsoft.com...
>I have a table that records time-stamps with the date and time contained in
> the same column.  How do I query these fields to only show the time or 
> only
> show the dates?
>
> LASTUPDATEDT
> 4/23/2007 11:48:30 AM 


0
Douglas
5/1/2007 8:53:08 PM
On Tue, 1 May 2007 13:34:01 -0700, mpcoon <mpcoon@discussions.microsoft.com>
wrote:

>I have a table that records time-stamps with the date and time contained in 
>the same column.  How do I query these fields to only show the time or only 
>show the dates?   
>
>LASTUPDATEDT
>4/23/2007 11:48:30 AM

You can set the Format of a textbox to Short Date or Short Time to display
only the date or time posted - you can even have two textboxes both bound to
the field (don't allow them to be updated though, or the date field will put
in a time of midnight and the time field a date of 12/30/1899!)

To search for a date use a criterion like

>= DateValue([Enter date:]) AND < DateAdd("d", 1, DateValue([Enter date:]))

             John W. Vinson [MVP]
0
John
5/1/2007 9:18:56 PM
Reply:

Similar Artilces:

Form & Query help
Hi, I have a form designed to compile an email based on database records and enables the user to open MS Outlook and have the email already populated. The form's Record Source is a query - qryCurrentRecord. That query is based on two tables where the field StaffName from 'tblQueryDetails' is linked to the field StafffName from 'tblEmailAddresses', enabling the email address for that staff member (tblEmailAddresses.StaffEmailAddress) to be returned by the query. So on my form I have a text box representing the 'To' address for the email, the Control Source ...

Year-To-Date Query or Report
Does anyone have information on where I can find or how to do a YTD query or report? Thanks, Ron Here's a starting point: Subquery basics: Year to date at: http://allenbrowne.com/subquery-01.html#YTD -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "rwr" <rwr@robcom.com> wrote in message news:%23yz6K72aHHA.4396@TK2MSFTNGP06.phx.gbl... > Does anyone have information on where I can find or how to do a YTD > query or report? > &g...

How do I concatenate two columns when one is in date format?
I am trying to combine two columns using =concatenate. One of them is a column of dates. When I execute the formula the returned value changes my date into a text number. How do i maintain the date value? Within the CONCATENATE() function, instead of referring to the date cell alone, refer to it by nesting the TEXT() function. For instance: if your date cell is A1 and you're concatenating it with cell B1, your formula would look something like this: =CONCATENATE(TEXT(A1,"mm/dd/yyyy"),B1) You may need to change the arguments of the TEXT function to fit your specific applic...

Subtracting dates to get hours... but I want to skip weekends
In column A I have start times (DD/MM/YY HH/MM/SS) and in column B I have the stop times. I am measuring the hours from the difference of these columns for each row, in column C. However, I would like to exclude weekends from the subtraction. I.e Friday 5 pm is the start time, Monday 8 am is the stop time, then the difference would be 15 hours instead of 63 hours. I know how to convert the fractions returned from the difference, into hours/minutes/days, and I also know how to convert dates to "day of week", so unless you guys know of a good idea I was just going to sort the file...

MEET & DATE MORE LADIES EASILY (Proven) ssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssss
...

date of monday of each week automatically
Code ------------------- =TODAY()-WEEKDAY(TODAY() ------------------- I have the above code which gets the date for the monday of the week i to a cell, if you open the file in that week. However I thought once i was saved, then it would stay the same, however, it keeps gettin overwritten. I want to get it to remain as the date it captures fo that week. The only idea i can think of is : 1.Create the date using vba. 2.Make a button to insert it when creating the document. Is this the quickest, easiest way? I was hoping that no manua interference would be needed. Thanks for any help ...

Union query changes Memo to Text (256 char limit)
Hi, I am using a Union query to join data from two different tables. The columns that are joined include Memo data types. The recordset after the union query truncates the Memo fields by quite a bit. I am guessing that the fields' Memo data type have been changed to Text data type. Is there any way to prevent this from happening? Thanks. baldwin.kevin@gmail.com wrote: >Hi, I am using a Union query to join data from two different tables. >The columns that are joined include Memo data types. The recordset >after the union query truncates the Memo fields by quite a bit. I am...

Automatically enter date and time but only update once.
I have a workbook that contains 14 sheets. I have a sheet for each month followed by 2 sheets for information. Each Month sheet has the following column headings associated from columns A through J:- Owner; from date; number of days; to date, address, ID, month, input by; date; time. I have to input data in columns A, B, C E, H, I and J. Columns A and H are pick lists. I have formulas in the following columns:- Column C: =IF(ISBLANK(Cnn),"",+Bnn+Cnn) Column F: =IF(ISBLANK(Enn),"",+Fnn+1) I want column I to be populated AUTOMATICALLY (do not want to use the Contr...

Graphing Time
I have a datasheet with time values that are in minutes:seconds format and I need to graph them in some way. However, the only way i am finding to graph them is by manually rounding the number to just minutes. Manually rounding the numbers will not work in the long run because I need to have an automated process and no formula will work with my numbers. here are some of my numbers in (minutes:seconds) 00001:40 00006:40 00013:20 00020:00 00005:00 00023:20 00008:20 That is the way they are given to me and I would like to use them in that format however I can't figured out how to have those...

How to restore Point In Time in this situation ?
Hi, We are new to SQL Server 2008. We make daily FULL backup of production database at night and transaction log backup every hour by maintenance plan. We also grant privilege to run FULL Database Backup by using the following SQL: BACKUP DATABASE [Finance_Live] TO DISK = N'D:\FINBackup\FLIVE.bak' WITH INIT We have tested the script above for a number of times between 4:00pm and 4:45pm. When we attempt to restore the backup (FLIVE.BAK) to a new database (For testing), we find that we have to use the FLIVE.BAK instead of the FULL Backup last night + All today&#...

Data from .txt file being read in as date, and should be text #2
I have a macro that reads in data from a .txt file. The data contains user IDs that consist of the first 4 letters of their name and 4 numbers. When names are read in that begin with MARCxxxx (ie MARC1234), excel is formating them as a date, and I lose the original data (even when I try to format them after the import, they are a number and the letters are not there. I've tried to format the column that they data read into before the import, but excel still imports them as a date. How can I make the import be read as text? Here is the code for the section I am reading in data for. Do Whi...

Counting number of dates in a column
Hi - Is there a way to count the number of dates in a column that contains both text and dates ? thanks, Rick Hi Rick try =SUMPRODUCT(--(ISNUMBER(A1:A1000))) -- Regards Frank Kabel Frankfurt, Germany Rick wrote: > Hi - Is there a way to count the number of dates in a > column that contains both text and dates ? > > thanks, > > Rick Thanks Frank ! Quick question - what do the two dashes do in the formula? thx Rick >-----Original Message----- >Hi Rick >try >=SUMPRODUCT(--(ISNUMBER(A1:A1000))) > >-- >Regards >Frank Kabel >Frankfurt, G...

Query Question 03-05-08
I am working with queries pulling from an SQL linked table. I'm pulling orders within a given time period with the below fields. Each order line item appears on a separate line. Customer Name Order Number Date Item Type New Start Joe Smith 12345 3/3/08 Core Y Joe Smith 12345 3/3/08 Comp Y Pete Smith 33224 3/4/08 Comp Y I need to do a query to pull customers who only have Comp items only within the time period I pull, so ...

Could not deliver the message in the time limit specified.
Periodically we get the message "Could not deliver the message in the time limit specified. <*******.******.COM #4.4.7>" when we send emails. I suspect it is a problem somewhere on our end because it happens with a number of recipients with different email addresses (i.e different domain names). For a week or two we could be sending emails to these email addresses and then all of a sudden we will start getting theses messages, then the following week when we send to these email accounts it gets through without any problems. Can anyone give me ideas on how to troublesh...

Reporting on user logon and time in CRM
Is there any possible way to report on when users log into CRM and how long they are in? Any thoughts or insight is greatly appreciated. Regards, Sean Aaron and I bounced a couple of ideas around before he left, but we never came up with anything that would work consistently. The problem is that the whole nature of HTTP is that it's stateless. We get around some of that by caching a bit of information in the application tier, and that was the core of the original idea. That is, could we use the cache insert time to determine, within the cache timeout time, the last time a user conn...

Run time error 429 at startup of pos
we uninstalled epson opos adk to install newer version. now when we try to open pos, we get an activex run time error 429. anyone have any ideas on how to fix it? JohnnyBoy, Try re-installing the RMS program as the Epson uninstall must have wiped out something RMS needed. -- * Get Secure! - www.microsoft.com/security You must be using Outlook Express or some other type of newsgroup reader to see and download the file attachment. If you are not using a reader, follow the link below to setup Outlook Express. Click on "Open with newsreader" under the MS Retail Management System...

need to query AD and Exchange attributes
hi everyone. been struggling with this for way too long now. i simply need to write a script that will take an AD account name as input, and report on 2 things for that user: Exchange mailbox size limit, and current mailbox size. Bharat, an MVP in here got me a good start a few days ago but im still struggling. i guess what im looking for is an example... i cant figure out how to determine limit if the limit is imposed by a policy in exchange. supposedly there are attributes called msExchangePolicyList and msExchangePolicyListBL that i should be looking at, but i dont even know where ...

Word keeps Times New Roman as default
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Hello, <br> I have tried to change default to Verdana but unsuccessfully. Word keeps typing new words in Times New Roman. The same applies with editing of documents where the paragraph was written in other font/style and my insertions appear in Times New Roman. It used not to be in this way (MSWord for Windows), where insertions where always in the style what paragraph used. <br> Please help. How did you try to change the default font? Exactly what commands? --rms www.rmschneider.com ...

Time scale axis should be available in PivotChart Report. #2
We should be able to time scale the X axis in PivotChart Reports. Pivot Charts are very usefull. I don't see any good reason to lose the time scale propety while doing a PivotChart Report. ...

Code to export my query as XLS to same directory each week.
I have the following query: qryEquipment_Intelebill - which I output as an Excel file - save to a shared folder - email the link to a co-worker - first Monday of every week How do I make this process completely automatic?.. - On Monday morning have the database open, - run the query & save as Excel 2003~2007 - to my shared folder & always overwrite the old file - then forward a LINK to that file only, in the Body of the email to the same co-worker I have worked with parts of this kind of procedure before but a little unclear on putting it all together in a single unattended proces...

Disabling up-to-date notifications for all users
Hi there I need to disable up-to-date notifications for all users except a few. How do I do this the smartest way? Regards Klaus R.N. ...

I need to add and subtract time cells. ie. 13:45 minus 13:58
I am having trouble manipulating cells formated to time. I need to be able to add, sum, subtract the entered cells, and then work with the results of the addition etc. To add times and prevent them being converted into days, Cusstom Format as [hh]:mm:ss Regards, Alan. "Mschertz" <Mschertz@discussions.microsoft.com> wrote in message news:3B1A92BC-AA5B-4356-A271-84ACD75E82E4@microsoft.com... >I am having trouble manipulating cells formated to time. I need to be able > to add, sum, subtract the entered cells, and then work with the results of > the addition etc. ...

I want to circle a date on a yearly calendar.
I am making a yearly calendar for my bass club. I want to circle the dates for our meetings each month and also our tournaments. How can I do this? -- Lee Use drawing items. -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "Leep5904" <lpruiett2@comcast.net> wrote in message news:46CA3EBC-1FE0-41F0-8816-2C6E18E68D04@microsoft.com... > I am making a yearly calendar for my bass club. I want to circle the dates > for our meetings each month and also our tournaments. How can I do this? > --...

Release Date of V 4.0
Hi Does any one know the official release date for CRM V 4.0? Will CRM V 4.0 be available for partners before it is available for the general public? Thanks alot currently not specific, but 2nd week of jan is approximately date. You can access the TAP version from partner source which you can use, demo etc. -- Regards, MS CRM Certified Professional http://microsoftcrm3.blogspot.com Chat with me on MSN / Gmail / Skype : ID Is :.. mscrmexpert@gmail.com "Mark Braithwaite" wrote: > Hi > > Does any one know the official release date for CRM V 4.0? Will CRM V 4.0 b...

number of days between two dates in european format
Hi I was trying to calculate number of days between two dates. The dates are in dd/mm/yyyy format. lets say in a1 31/03/2005 and in b1 its 28/02/2007. But when i try to put the formula =b1-a1 in c1 then its throwing up #value error. How do i rectify this. Thanks in advance -- manan ------------------------------------------------------------------------ manan's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=34216 View this thread: http://www.excelforum.com/showthread.php?threadid=544167 Works by me! It returns 699 HTH -- AP "manan" <manan.2875...