Is there a Date Time Conversion from text?

I have DateTime stored as text in an Access db field.

Ex:  1/22/2008 10:34:29 AM

I want to convert this data form text to a real DateTime value all 
inclusive.  I am able to pull the date DateValue and time TimeValue, but is 
there a way to get them both together.

Thanks


0
Utf
1/24/2008 1:08:02 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
973 Views

Similar Articles

[PageSpeed] 9

Try using CDate or DateAdd
DateAdd("D",0,[Your Text Date Time])
CDate([Your text date time])

If your string field could be null, or a zero-length string, or invalid, 
then I suggest you test it first

IIF(IsDate([Your Text Date Time]),CDate([Your Text Date Time]),Null)

-- 
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Dave" <Dave@discussions.microsoft.com> wrote in message 
news:B43CC237-E635-4D08-8959-221DFB229CAB@microsoft.com...
>I have DateTime stored as text in an Access db field.
>
> Ex:  1/22/2008 10:34:29 AM
>
> I want to convert this data form text to a real DateTime value all
> inclusive.  I am able to pull the date DateValue and time TimeValue, but 
> is
> there a way to get them both together.
>
> Thanks
>
> 


0
John
1/24/2008 1:43:48 PM
Thanks John, CDate did the trick :-)

BTW Is there an easy way to print the response only, I don't see a print 
friendly link of a way to just print the response without getting the entire 
web page.

"John Spencer" wrote:

> Try using CDate or DateAdd
> DateAdd("D",0,[Your Text Date Time])
> CDate([Your text date time])
> 
> If your string field could be null, or a zero-length string, or invalid, 
> then I suggest you test it first
> 
> IIF(IsDate([Your Text Date Time]),CDate([Your Text Date Time]),Null)
> 
> -- 
> John Spencer
> Access MVP 2002-2005, 2007-2008
> Center for Health Program Development and Management
> University of Maryland Baltimore County
> ..
> 
> "Dave" <Dave@discussions.microsoft.com> wrote in message 
> news:B43CC237-E635-4D08-8959-221DFB229CAB@microsoft.com...
> >I have DateTime stored as text in an Access db field.
> >
> > Ex:  1/22/2008 10:34:29 AM
> >
> > I want to convert this data form text to a real DateTime value all
> > inclusive.  I am able to pull the date DateValue and time TimeValue, but 
> > is
> > there a way to get them both together.
> >
> > Thanks
> >
> > 
> 
> 
> 
0
Utf
1/24/2008 2:12:01 PM
Reply:

Similar Artilces:

Max cell height for text
We need to insert text from a Word document into a cell. The text is about 300 words. After we do the insert, we can only see a portion of the text in the cell. We tried stretching the row boundary, but it stopped stretching towards the bottom of the window. Even the print image doesn't show all the text. How can we increase the cell height to see all the text? Normally pasting into the formula bar the row will cause it to fit automatically but you could try Format>Row>Auto fit, ...then the same with column if needed, but one wonders why you would want to do that as it would ...

Proper display of TIME from a formula
I need some help with the time in a formula please. I have this info in each cell: Friday, September 14, 2007 - Sidewalk 65 - 8:00 PM - Tom & Betty's - 387-3311 This is the formula I am using: =TEXT(F5,"m-dd-ddd")&G5&H5&I5&J5&K5&L5&M5&N5&O5&P5 Here is the result: 9-14-Fri - Sidewalk 65 - 0.833333333333333 - Tom & Betty's - 387-3311 It is returning 0.833333333333333 for what I would like to show as 8:00 PM The 8:00 PM is in cell J5. Please tell me the proper formula to make this happen. Thanks, I think this would work: =TE...

Conditional highlighting from current date
Hi, I would like to create a spreadsheet with dates as cell values and ge Excel 97 to highlight those cells that contain the current dat whenever I open that sheet. Is this possible with Excel 97? If not, which version of Excel (if any) would allow this? If yes, how do I go about it and what date format should I use? Thanks for any help -- Message posted from http://www.ExcelForum.com Hi oxag! Select the cells with the dates. Goto Format>Conditional Formatting. In the first dropdown list, select: Formula is In the text box that opens enter this formula: =A1=TODAY() (adjust the ce...

What are the formula for calculating ROI all dates (Total return all dates)
From http://money.mvps.org/articles/portfolio_columns.aspx, it says that dividends should be included in calculation of ROI all dates(Total return all dates) So the formula for ROI should be ((market price - purchase price)+ dividend) / purchase price. Is this correct? However, what I am seeing is that ROI all dates is calculated as (market price - purchase price)/purchase price. Is there any other parameter that includes dividends also In microsoft.public.money, rvsw@hotmail.com wrote: > From http://money.mvps.org/articles/portfolio_columns.aspx, it says >that dividends should be i...

Excel
Can anyone tell me how to convert a date to the number of days ago that date actually was? I need to find the number of days ago. Thanks in advance! Michael the function Today() returns you a number when the cell is formatted 'General' or 'Number' -today sept 29 you get 37893-. Apply the same to your date and deduct one from the other Generally speaking, why don't you read 'About dates and date systems' in Excel Help Emm >-----Original Message----- >Can anyone tell me how to convert a date to the number of >days ago that date actually was? I need to...

Archive email with receive date as none
How can I archive mail without a receive date. Outlook requires that one specify a date when archiving. An old KB entry pertaining to OL97 claims this was resolved in OL98, but I have OL02 and have this problem. It archieves on the Modified Date; not the received date -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -Creating Signatures -Create an Office XP CD slipstreamed with Service Pack 3 ----- "Toastie" <toastie97@yahoo.com> wrote in message news:157dc01c44695$fc042ce0$a101280a@phx.gbl... > How can I archive mail without a receive date. O...

DTPicker control
I added a date picker (DTPicker1) control on November 30, 2009 in one of my ..xlt template. At the initial Workbook_Open level of this template, the value of the "LinkedCell" of the DTPicker1 control is set to "YYYY-MM-DD". Here is the code: Sheets("MRF Form").Range("FundingDate") = "YYYY-MM-DD". My problem is that the first time (and only the first time) a user click on the down arrow of DTPicker1, the calendar shown is always November 2009 with the number 30 highlighted. I would like that the default date of the control...

How I keep original dates for check numbers?
I'm using Money 2003. When I enter a check number on a particular date, it remains that way in Money until it clears my bank; then when I download the history file from the bank it changes the date to when the check came in. I would rather keep the original date, but can't find anything in Money's help or other sections to tell me how to do this. In microsoft.public.money, Neil Harrington wrote: >I'm using Money 2003. When I enter a check number on a particular date, it >remains that way in Money until it clears my bank; then when I download the >history fil...

error in dates
Hi! I keep having problems when I type the date 6/16/86 in Excel, even if i format it, it won't display as a date. Can anyone help me? Thanks. Hi check the format of your cell prior to entering the date. Probably the cell format is set to 'Text'. change this to 'General' or a date format and after this enter your date -- Regards Frank Kabel Frankfurt, Germany cheng wrote: > Hi! I keep having problems when I type the date 6/16/86 > in Excel, even if i format it, it won't display as a > date. Can anyone help me? Thanks. If your regional setting is using d...

Dates defaulting to 2008
I have a form in Access 2007 that has 2 combo boxes that select dates from a single table (FROM and TO). The table that I am using for test purposes has dates from Apr-07 to Nov-07. Prior to New Year (i.e. 2007), if I clicked one of the combo boxes, it showed Apr-07 to Nov-07. If, for example, I clicked on Nov-07, Nov-07 would appear in the box. Everything normal! However, since the turn of the year (i.e. 2008), if I do the same thing (select Nov-07), Nov-08 appears in the box ... even though Nov-08 does not exist in the source table. Does anyone know a way around this problem? Many ...

Arrays, Dates & Blank Cells
Folks, I'm struggling to resolve this Execl formula. I've got a workbook with 2 sheets: Summary and Source Data. The workbook is used a template so that data exported from another system can be copied and pasted into the Source Data sheet and the Summary sheet will programmatically parse the data to provide the results that I am looking for. The Source Data sheet has a number of named ranges. My problem relates to the range named DATE (D2:D10000). This range contains dates formatted as "=DATE(2008,1,1)" for Jan 1, 2008 as an example. In virtually every case, there w...

Select Text Files from Combobox
Hello again! Is there a way to read in multiple files of "almost" similar into a combobox for the user to select the file they want? The filenames first few characters will be the same but the following characters will be dymanic. For example: filenames: SN1033_XXX_67489564926.txt SN1033_XXX_00909890879.txt SN1033_XXX_78987907893.txt SN1008_XXX_78290574238.txt SN1008_XXX_54545454544.txt and so on.... So I would like to read all the text filenames beginning with "SN1033". Can I sea...

Re: SUMIF only it has 3 ceretain texts in the same row
Look at this formula it sums the value if it has the pacific text. =SUMIF(E2:E47,"Pattern Change",D2:D47)+SUMIF(A2:A7,"Without Patter Change",C2:C47) But how can you create a formula to meet a certain condition meaning i "PATTERN CHANGE" is in A2: and '1' is in B2: and "MACHINE" in C2: The grab the the value in E2: I tried to create this formula but it doe not work. =SUMIF(A2,C2"PATTERN",'1',"MACHINE",E2) Any suggestions? Thnaks -- vane032 ---------------------------------------------------------------------...

Why don't Microsoft issue Self-pace RMS Text book
I find for material to study but cannot, why don't ms issue rms text book There is a training manual you could buy for around $100. Have you read the RMS manual and practiced with the sample database and used the RMS help files? Rick Brown DataBasics "falunkai" <falunkai@discussions.microsoft.com> wrote in message news:3483C77A-EAD9-40DF-AF32-345C62B8B88C@microsoft.com... >I find for material to study but cannot, why don't ms issue rms text book There's also a Web Based training - around 20 hours and self paced for about $300. It's what our new t...

Workflow
Hi, I've read a whole bunch of posts on workflow but cannot find the answer to my problem. I want to create a rule that on activation of a Quote a task is created with the task due date set to 7 days prior to the quotation expiry date. Cheers Andy. Sorry, there's no supported way to do this in the current version. The closest you are going to get is to use Wait statements in your workflow. -- Matt Wittemann http://icu-mscrm.blogspot.com "Andy P" wrote: > Hi, > > I've read a whole bunch of posts on workflow but cannot find the answer to > my prob...

How to display a text file dynamically?
I need to show a large text file ( <10M) to users in a dialog. Meanwhile, the file is being appened with more data by a seperate process. I have little experience with MFC. I guess I should use RichEditCtrl for this case. But I'm not sure if I should use SetDlgItemText(..) to send file content to the control. This is Question 1. Secondly, I'd like send only the newly appended part of the file to the control for every refresh, instead of the whole file everytime. Are there any existing functions/mechanisms in MFC for this purpose? By the way, I have to use VC++ for the project (no...

How to Make the "Look For:" Box Appear in Outlook 2003 All the Time?
Hello: My Look For box doesn't stay open all the time. Whenever I close Outlook and log back it I have to do the Cntrl+E to get it to appear. How do I set it to stay open all the time? I'm using Outlook 2003 on WinXP Pro. Thanks in advance! Spudy ...

Chart Title entering text VBA
Hi, Hope someone can help; At work we import a large number of textfiles which when imported int excel are graphed. We get the titles from the textfiles and this i fine for all but one file. What i want to do is edit the chart title t add text after it has imported. However the first part is variable. So in summary i want the chart title as it is, but then to add tex afterwards to the existing title. Is this possible? so "Graph ????" is the title but i want to make it "Graph ????: tes text" The below obviously just completely changes the title. Sheets("Asthma Gr...

Help needed with date format
I've always entered date as yy-mm-dd. In fact when I want to enter Aug 28, 2005, I would only need to enter "5-8-28". With the proper formatting on the cells, the date will be displayed as "2005-08-25", or "050825" or whatever I have formatted it as. My problem started after I installed Tiger (OS 10.4) over the weekend. Now all the format has changed. I've tried to change between the formatting on Excel, chedked the preferences as well as changing the System Preferences. But I just can't get back to what I used to get in the date format. The cell will...

Outlook 2007
Hi, In Outlook 2007 - specifically related to the Reading Pane - How does one adjust the text size so that it remains enlarged or reduced? Unlike with Outlook 2003 where we had to click on the border surrounding the reading pane to access the text size changes - all I can find relative to Outlook 2007 is to use the Ctrl+Mouse scroll wheel. If the feature was intentionally removed, it most certainly does not allow for success when in productivity mode and it is quite frustrating for those who wear glasses or are senior citizens. _If_ Microsoft has not taken into account people with vis...

Run-time error '1004' Method 'Sheets' of object'_Global' failed
I'm new to excel and don't really know what I'm doing. When the below macro is executed (through changing a combo box) the macro appears to run fine but when I close the workbook the above Run-time error message dialog box comes up, can anyone help? Sheets("dec").Range("A1:CO111").Cop Sheets("ROTA VIEW").Selec Range("B4").Selec Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= False, Transpose:=Fals Range("B3").Selec Thanks Richard, Add the line Application.CutCopyMode = false...

Up Dating Contacts on several computers?
How do I update contacts from 1 outlook to another,with out having old info reentered in one that has it deleted I'm thinking of something along the lines of update by date or something. Can anyone help ...

Benefit Open Enrollment
With open enrollment coming in June we were faced with an interesting question...how do you set a benefit/deduction dollar amount to change on a specific date. For benefits with premium changes we thought the easiest way would be to simply roll down the premium change right before doing the first check run that has the July 1st date. However, it's not that easy for HSA and FLEX benefits. Employees will each have different changes to the dollar amount they wish to allocate and getting all of them done between the pay runs would be difficult. Is there an option or module which mak...

How do i remove tabs from text?
I am doing a school assignment that requires the use of tabs. My problem is that I need to remove the tabs on 1 line of text while leaving the rest there. The line that has been highlighted in the picture shown below is the line that needs to be formatted. I am using the Word 2010 beta, so any information that relates to Word 2007 would be great. <a href="http://s366.photobucket.com/albums/oo105/Firefight_95/?action=view&current=Capture.jpg" target="_blank"><img src="http://i366.photobucket.com/albums/oo105/Firefight_95/Capture.jpg" ...

Copying Date and pasting special
I am copying from one sheet and pasting special (format and value) to another sheet. When I do, the date changes. I've converted the date to a general number - 40193 - In the source sheet it shows as 1/15/10 In the destination sheet it shows as 1/16/14 How can I get this cleared up? Anyone? Thank you!! Hi In the destination sheet you are using 1904 date system, but not in source sheet. In destinaion sheet goto Tools > Options > Calculation > Uncheck '1904 date system' Regards, Per "David" <David@discussions.microsoft.com&...