Trim Time off Date

Access 2007 linked to SQL 2008 database

I have a query with a date field that has the time included on it. How do I 
trim, truncate, remove the time part of the date in the query field named 
cndate?



0
GMC
2/25/2010 9:03:51 PM
access.queries 6343 articles. 1 followers. Follow

4 Replies
5440 Views

Similar Articles

[PageSpeed] 4

I might use an expression like the following:

IIF(IsDate(cnDate),DateValue(cnDate),Null)

Or perhaps a bit faster
   IIF(CNDate is Not null, DateValue(cnDate),Null)



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

GMC -LSND wrote:
> Access 2007 linked to SQL 2008 database
> 
> I have a query with a date field that has the time included on it. How do I 
> trim, truncate, remove the time part of the date in the query field named 
> cndate?
> 
> 
> 
0
John
2/25/2010 9:32:33 PM
Neither one of those are producing anything. The field comes out with 
nothing in it.


0
GMC
2/25/2010 9:40:39 PM
NEVER MIND!!! Brain isn't working. Your coding works.


0
GMC
2/25/2010 9:43:04 PM
"GMC -LSND" <gcoleman@legalassistfake.org> wrote in
news:eE1zEOmtKHA.4220@TK2MSFTNGP05.phx.gbl: 

> NEVER MIND!!! Brain isn't working. Your coding works.

I hate it when the brain isn't working. 

I've found a good slap up-side the head seems to help. 

Forehead, too. ;)

-- 
David W. Fenton                  http://www.dfenton.com/ 
usenet at dfenton dot com    http://www.dfenton.com/DFA/
0
David
2/26/2010 11:20:44 PM
Reply:

Similar Artilces:

Trouble with time formats
Any help appreciated! I need to work out the average speed it would take to cover a certain distance. EG. If you covered 10 miles in 28 minutes what would teh average speed be... Column A: 10 miles Column B: 28 minutes Column C: Would return 21.43mph. I then want to to add time increments of say 15 seconds in rows so that I can see that if it took 28 minutes, 45 seconds, the average speed column would return 20.87mph. I can work it out by entering 28.75 for the time (or 70.50 for 1h,10m,30s), but I want to format it as time, i.e: 00:28:45 (or 01:10:30) Is this possible? Thanks...

DTS Export fails on bad date
While trying to import an excel spreadsheet to a SQL Server table, DTS fails, pointing to a column with date values. I have looked at all the date values, and they appear correct. I need to get this excel spreadsheet loaded to a database table. Is there any way to pinpoint the exact cell causing the problem? Or, can you think of another way to export the data in the spreadsheet besides DTS that might not be so sensitive to data content? Thanks, Dean Slindee ...

How do I calculate escalation rates using dates and a set value
If I have a projected that needs to be escalated using start and completion dates by a certain percentage per year, how do I write the formula. For example if my project starts May 26, 2010 and lasts until may 26 2014 I want to add escalation to midpoint at 4% per year. ...

Importing Item List for the first time
We are currently change over from Quickbooks Accounting 2002 Pro to MS Retail Management System and need to know the easist way to import item list into MS POS. -- Dan Hausbeck Dan, Easiest is to upgrade to QB 2003 or higher. Then follow these instructions; https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?scid=kb;en-us;836444 Otherwise, does 2002 give you the ability to export a report to Excel? If so, create a report of your inventory items, export to an Excel file and then use the unsupported RMS Import Utility, again in CustomerSource. I think you are going to _have_ to upda...

Converting Numbers to Time (A second probelm)
Due to several peoples help in this group I learned some very helpful date/time functions but here is one that has since eluded me... What I am trying to do it convert... 1/6/1900 22:40:25 to the total number of hours and seconds. As if the start date was 1/1/1900 00:00:00. Or in other words I have a start date of 4/6/2005 11:54:53 End date of 4/7/2005 14:07:00 Total time of 1/1/1900 2:12:07 How do i convert the 1/1/1900 2:12:07 into 26:12:07 Thanks for all your help, Steve smonczka@hotmail.com Just format the answer cell as [hh]:mm:ss -- HTH Bob Phillips "Smonczka" <...

disc date for next month
We want to be able to choose a discount date for the next month the discount is allowed. Our terms are 2% 10th net 15th. We can choose the due date for the 15th of the next month, but we can't choose the discount date as the 10th of the next month. All the invoices entered into the system from the 1st thru the 10th calculate the discount for the current month instead of the next month. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the me...

combine text & date formula to show mmmm dd, yyyy?
How do I combine text and date formula to show the written date and not the Excel number date? I have text in a couple of cells and a date formula in another cell(input from a different worksheet). I have the formula =A3 & F3 & A4 in A1 and would like it to read: We went to the station on December 21, 2009 and took the train. However, it is reading We went to the station on 40168 and took the train. Thanks. Try something like this... A1 = Today is B1 = 12/21/2009 =A1&" "&TEXT(B1,"mmmm dd, yyyy") Returns: Today is December 21, 2009 ...

Is there a way to copy and paste the date into worksheets
Is there a way to copy and paste dates into the worksheet withou individually typing up each one -- mark_vi ----------------------------------------------------------------------- mark_vi_'s Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2450 View this thread: http://www.excelforum.com/showthread.php?threadid=38137 Is there a way to insert it the date into the the worksheet tab (not th cells in the actual worksheet but the name of the worksheet itself -- mark_vi ----------------------------------------------------------------------- mark_vi_'s Profile: ht...

Time Card setups
Hello, We are implementing HR for a client. What is the best practice in importing Time Code assignments to employees and establishing beginning leave balances? -- Greg. ...

Formaula for less than date and name begins with...!!! HELP!!!
IM trying to create a formula that gives me a total count for cells that are less than 02/05/10 and the name begins with FHLM, heres what i came up with... =COUNTIF(E:E,">="&DATE(1900,1,1))-COUNTIF(E:E,">="&DATE(2010,2,5))+COUNTIF(E:E,DATE(2010,2,5))-COUNTIF(U:U,"FHLM*") I keep gettin a bunch of ###### or 0, depending on how I move the data around.. Any ideas???? =SUMPRODUCT(--(E1:E100<=DATE(2010,2,5)),--(LEFT(U1:U100,4)="FHLM")) "Senor Martinez" wrote: > IM trying to create a formula that gives me...

Help! Search & Replacing time formats
Hi I have a whole spreadsheet full of fields similiar to the following 10:00AM 12:15PM 2:25PM 4:40PM 7:00PM 9:20PM 10:00PM 12:20PM 2:25PM 4:30PM 7:15PM 10:00AM 12:30PM 2:45PM 9:15PM 10:40AM 1:10PM 5:00PM 7:20PM 9:35PM etc. I need to convert all times to 24 hour, and drop the AM & PM. The later is easy, but how do I do a quick Search & Replace without incorrectly converting 10:**AM to 22:** etc. Any tips would be appreciated. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly fro...

Grouping dates in pivot table
I am pulling data from a SQL Server database to create a pivot table. Excel (07) is not recognizing the field as a date. This is a field I would like to group by in the pivottable. I am aware of all the techniques to convert this to a date field, but I am searching for an answer as to why XL pivot tables cannot consume the dates directly from a sql query. I have played with bringing the dates back in a number of differenent formats with no success. Any insights into this would be appreciated. Probably coming in as text and yuo may only need to copy an unused cell and paste sp...

Run Time Error 3711
I get this run error occsionally on a machine with Store Operations Manager running XP Pro. Run Time Error 3711 I would appreciate any help. Tony ...

how do i change dates on my calendar and keep my pics
Publisher doesn't support this. You would need to do it manually. -- Brian Kvalheim Microsoft Publisher MVP http://www.publishermvps.com ~pay it forward~ This posting is provided "AS IS" with no warranties, and confers no rights. "Bob T" <Bob T@discussions.microsoft.com> wrote in message news:68671977-F71B-444F-AE72-801D2B359FEE@microsoft.com... > ...

Outlook Multiple Profiles at the Same Time
I have two separate icons set up on my desktop to call Outlook using /profiles switch for profiles A & B. I can double click one for A, and it opens my POP3 account for profile A. Then I close it and double click one for B, then it opens my Exchange Server account for profile B. The problem is if I don't close the one for profile A and try to open the one for B, then even if the switch is set to open profile B, it just opens profile A again. Can someone tell me how I can have two separate Outlook for two separate profiles open at the same time? Adding POP3 to my Exchange S...

Suming Up Time over the course of days
I have a sheet the contains event durations on specific days. Some days have more than one event. I would like to add up all the event times for each day on a second sheet and (line) chart the results there. The list get longer with each event. Tuesday Nov.2 2010 10:16 am 00:37:22 Tuesday Nov.2 2010 08:28 am 00:15:48 Monday Nov.1 2010 08:30 am 01:03:07 Sunday Oct.31 2010 06:36 pm 00:09:20 Sunday Oct.31 2010 06:17 pm 00:03:26 Sunday Oct.31 2010 05:27 pm 00:07:42 Sunday Oct.31 2010 11:15 am 00:27:19 Sunday Oct.31 2010 08:56 am 00:45:26 Saturday Oct.30 2010 12:44 pm ...

use a time value in a bar graph
i have values in a series like c5 9:01 d5 21:01 e5 =(d5-c5) I would like to use e5 in a bar graph it gives values which would make sense only to excel If the axis is formatted as time, it shouldn't be a problem. If it doesn't choose the scales to give clean divisions, you can choose an appropriate unit on the axis, such as 03:00. -- David Biddulph "pdfrone" <pdfrone@discussions.microsoft.com> wrote in message news:A8C14E9F-104A-464A-81B6-39DA2C679E71@microsoft.com... >i have values in a series > > like > > c5 9:01 > d5 21:01 > e5 =(d5-c5...

Solved! (This time for sure!
Wait... Bullwinkle? Boris? Hmmm.... Boris, I owe you. I never in a million years would have guessed it was the icon, but that's exactly what it was. I just got an e-mail from the client. The system now works 100%. If by chance you are on facebook, please add me as a friend, or just send me an e-mail sometime - discgolfdad@cox.net And say "Hi!" to Natasha for me! I don't know Bullwinkle, and Boris is not my real name, but I'm glad it works now! Cheers!! On Sat, 17 Apr 2010 14:06:40 +0200, Boris Pauljev <nordiccoder@hotmail.com> wrote...

One link breaks every time I open a workbook...
I have 3 workbooks tied together with links. One is a source only, the other two push and pull data. One of the sources, Payroll Master, on opening updates all links fine to the source only workbook but not to the third workbook (which is in the parent directory). Every time I open it it prompts me to show it where the Payroll Entry is. Doesn't matter if I hit cancel and do Edit Links, I can change it there, but will still lose it upon closing (the link appears in the box and looks ok, just won't update...) Try going Edit->Links. Break the link and re-establish it? That m...

Looking up a date
Trying to get this to work, but no success: =LOOKUP(A6,{DATEVALUE("3/26/2010"),DATEVALUE("6/24/2010"),DATEVALUE("9/22/2010")},{"3","2","1"}) The value in A6 is a function; not hard-coded. It seems to work with one date and one result vector, but not two, and ultimately I will need several, maybe 7 or 8. Any ideas? Thanks! Ryan-- You can't use functions in array constants. >ultimately I will need several, maybe 7 or 8. Why don't you just create a table in ascending order by date: .............A.......

Daylight saving time Strange behaviour
Hi, I have created an application where I am using CTime( int nYear,int nMonth,int nDay,int nHour,int nMin,int nSec,int nDST = -1); Constructor to change the dates to UTC before saving them to database. I have set my timezone to Easterntime(US&Canada) which is 5 hrs behind GMT. I entered the following date, 01\11\2006 12:00:00PM.When its converted to UTC,the date its showing is 01\11\2006 4:00:00PM. In 2006,DST ended on 29\10\2006,thus ideally it should show 01\11\2006 5:00:00PM. This behaviour is shown for dates ranging from 30\10\2006 to 4\11\2006. Analysing the issue,I checked my...

Count the number of times a character appears in a field
This is a multi-part message in MIME format. ------=_NextPart_000_001B_01CAD018.FC830D40 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit How can I count the number of times the @ symbols appears in a text field. ------=_NextPart_000_001B_01CAD018.FC830D40 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META content=3D"text/html; charset=3Dus-ascii" = http-equiv=3DCon...

Format Date 05-28-10
Hi, all, How do I format the date to show May, 2010? The closest I can get is May-10 from the drop down list. Thanks, Malcolm Malcolm wrote: > Hi, all, > How do I format the date to show May, 2010? The closest I can get is May-10 > from the drop down list. > > Thanks, > Malcolm Custom date format: mmmm, yyyy Custom Format mmmm, yyyy Gord Dibben MS Excel MVP On Fri, 28 May 2010 09:05:01 -0700, Malcolm <Malcolm@discussions.microsoft.com> wrote: >Hi, all, >How do I format the date to show May, 2010? The closest I can get is M...

Charting an intermittent time series #2
I am trying to chart a data set. The x axis is an intermittent time series (e.g. 1pm, 1:15pm, 4pm, 11pm, 11:05pm, etc) I have one data item which is scaled from 50 to 300, another from 1 to 20, and a third which is 15 to 100. Is there a way to make a time scaled chart with the data on multiple y axis? ...

Excel Date Formatting
Hi, In Excel the regional settings are set to short date format. However, if you try and format cells individually with a date, the date changes, but when the spreadsheet is closed and the reopened the formatting reverts to the short date format! We are using Excel 2000 with sp3 on XP (If thats any help!) If anyone has any ideas why this is occuring i'd love to know! Cheers, Hayden Are you saying that the same person on the same pc opens the file and the date format has changed? If it's another user (or another pc), then it could be the way excel picks up the date format if ...