Convert text to date 12-21-07

Hello,

I am trying to link to a text file. I was having trouble converting 20011975 
into a date/time format but it wasn't working. So I formatted the field as a 
text file. I have tried converting this to 20/01/1975 in a query but it comes 
out with errors and wierd dates.

I've had a look at the other messages and had a go but no go.

thanks
0
Utf
12/21/2007 2:35:01 PM
access.queries 6343 articles. 1 followers. Follow

4 Replies
681 Views

Similar Articles

[PageSpeed] 58

Regardless of how your dates are displayed either by formatting or based on 
regional settings, all date functions exppect a month day year format.  To 
convert your string to a date field:

strTextDate = "20011975"
SomeDate = DateSerial(Clng(Right(strTextDAte,4)), 
Clng(Mid(strTextDate,3,2)), Clng(Left(strTextDAte,2)))
-- 
Dave Hargis, Microsoft Access MVP


"scubadiver" wrote:

> Hello,
> 
> I am trying to link to a text file. I was having trouble converting 20011975 
> into a date/time format but it wasn't working. So I formatted the field as a 
> text file. I have tried converting this to 20/01/1975 in a query but it comes 
> out with errors and wierd dates.
> 
> I've had a look at the other messages and had a go but no go.
> 
> thanks
0
Utf
12/21/2007 3:00:01 PM
Brilliant!

"Klatuu" wrote:

> Regardless of how your dates are displayed either by formatting or based on 
> regional settings, all date functions exppect a month day year format.  To 
> convert your string to a date field:
> 
> strTextDate = "20011975"
> SomeDate = DateSerial(Clng(Right(strTextDAte,4)), 
> Clng(Mid(strTextDate,3,2)), Clng(Left(strTextDAte,2)))
> -- 
> Dave Hargis, Microsoft Access MVP
> 
> 
> "scubadiver" wrote:
> 
> > Hello,
> > 
> > I am trying to link to a text file. I was having trouble converting 20011975 
> > into a date/time format but it wasn't working. So I formatted the field as a 
> > text file. I have tried converting this to 20/01/1975 in a query but it comes 
> > out with errors and wierd dates.
> > 
> > I've had a look at the other messages and had a go but no go.
> > 
> > thanks
0
Utf
12/21/2007 3:06:03 PM
I discovered the "advanced" option and it converted the text to dates.

"Klatuu" wrote:

> Regardless of how your dates are displayed either by formatting or based on 
> regional settings, all date functions exppect a month day year format.  To 
> convert your string to a date field:
> 
> strTextDate = "20011975"
> SomeDate = DateSerial(Clng(Right(strTextDAte,4)), 
> Clng(Mid(strTextDate,3,2)), Clng(Left(strTextDAte,2)))
> -- 
> Dave Hargis, Microsoft Access MVP
> 
> 
> "scubadiver" wrote:
> 
> > Hello,
> > 
> > I am trying to link to a text file. I was having trouble converting 20011975 
> > into a date/time format but it wasn't working. So I formatted the field as a 
> > text file. I have tried converting this to 20/01/1975 in a query but it comes 
> > out with errors and wierd dates.
> > 
> > I've had a look at the other messages and had a go but no go.
> > 
> > thanks
0
Utf
12/21/2007 5:06:00 PM
Can you share?  I have the same issue.
-- 
-The Novice
Learn Today, Teach Tomorrow

Great Success is ones ability to ask for Help.


"scubadiver" wrote:

> 
> I discovered the "advanced" option and it converted the text to dates.
> 
> "Klatuu" wrote:
> 
> > Regardless of how your dates are displayed either by formatting or based on 
> > regional settings, all date functions exppect a month day year format.  To 
> > convert your string to a date field:
> > 
> > strTextDate = "20011975"
> > SomeDate = DateSerial(Clng(Right(strTextDAte,4)), 
> > Clng(Mid(strTextDate,3,2)), Clng(Left(strTextDAte,2)))
> > -- 
> > Dave Hargis, Microsoft Access MVP
> > 
> > 
> > "scubadiver" wrote:
> > 
> > > Hello,
> > > 
> > > I am trying to link to a text file. I was having trouble converting 20011975 
> > > into a date/time format but it wasn't working. So I formatted the field as a 
> > > text file. I have tried converting this to 20/01/1975 in a query but it comes 
> > > out with errors and wierd dates.
> > > 
> > > I've had a look at the other messages and had a go but no go.
> > > 
> > > thanks
0
Utf
1/10/2008 5:56:04 PM
Reply:

Similar Artilces:

Date question
Is there a simple formula that will give the difference between two dates in MONTHS? Today() - (6-1-2003) = MONTHS Try this... A1 = 6/1/2003 =DATEDIF(A1,NOW(),"m") -- Biff Microsoft Excel MVP "Ziggy" <ziggy943@xmission.com> wrote in message news:40762e16-9a39-4906-8630-c1ffc5ed1e4d@i25g2000yqm.googlegroups.com... > Is there a simple formula that will give the difference between two > dates in MONTHS? > > Today() - (6-1-2003) = MONTHS On Apr 16, 2:44=A0pm, "T. Valko" <biffinp...@comcast.net> wrote: > T...

How do I extract text content from an exported .rwz file
Outlook 2003, Office Pro - When you export Rules and create a .rwz file, the file is not a pure text file as it seems to be loaded with null chars. I would like to extract the long list of spam words used in a particular rule. Is there a way or a utility which will remove all the nulls from the file so I may edit it to get the contents I want ? The alternative storage location is not any easier - trying to get the same info, as it is similarly stored, out of the registry. :( -Ric Not possible. -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -Backup and R...

Unbound memo default text font
An unbound memo field is completed by the 'zoom' popup. The default font is set for 8. Is the default adjustable to a different font size? How? Manually setting it everytime with the 'font' button is a headache. The text is too small to be readable at the screen res., which cannot be changed. Nick First, there is no such thing as an "unbound memo field!" Memo fields (like all fields) are only defined in tables, and hence have to be bound. The control that hold memo fields on forms are textboxes, and I assume this is what you're referring to. To set...

change control source or value of text box
I have a report with GroupHeader0 and GroupHeader1. The data source of the report is a query. In the GroupHeader0 I have the Purchase Order Header info which includes the Purchase Order due date. In the GroupHeader1 I have the Purchase Order line info which includes the line due date. What I need is if the line due date is Null or blank to default to the PO header due date. How can I assign the header due date to the line due date. Can I change the control source of the text box in VB programming. -- MNJoe Put the following expression in the first blank field in your query: ...

Conditional Formula based on previous date + 30
I have a spread sheet that caluclates when proposals expire. Date Sent Follow Up date January 13, 2010 February 12, 2010 What I am looking for is for the Follow up date to turn RED when the date is expired (over the date listed). Just use CF with a formula of =B2>TODAY() -- HTH Bob "Chris" <Chris@discussions.microsoft.com> wrote in message news:66DE65F1-F041-434C-86A7-B13635C6914F@microsoft.com... >I have a spread sheet that calu...

Display Field Name as Text Listing if Value True from Checkbox Fields
Hi everyone! I have 40 or so columns in a table using the yes/no data type. Each record in the table represents an audit of a paper form. Those items filled incorrectly or incompletely on the paper form receive a check on an access form during the audit process. I would like to create a feedback form based on the audit record that displays a listing of fields that received the check boxes, or rather, were incomplete or incorrect on paper form that was audited. On the feedback form, I only want to display something like this: "Your form had the following errors: ErrorFieldOneName Erro...

Format cells with dates
Is there a way to format cells so that dates would change when the lead date is changed. for example, when I input monday's date, tue, wed, thur, etc will follow suit. Pat, Assuming the first date is in A1 B1: =A1+1 C1: =B 1+1 etc. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Pat" <anonymous@discussions.microsoft.com> wrote in message news:115681B0-348E-447E-BB1F-F8347CFDB19B@microsoft.com... > Is there a way to format cells so that dates would change when the lead date is...

paste-linking annotating text boxes
I'm trying to insert an Excel chart into a PowerPoint presentation and link it dynamically. The data link is not an issue but I cannot find a practical way to paste the text boxes that were added to the Excel chart by way of annotation. Selecting and pasting the chart itself and the textboxes as if they were separate objects results in a distorted PowerPoint layout. Is my only choice to add the text boxes anew in PowerPoint? Select the chart prior to adding the textbox. The textbox then will be part of the chart, rather than just another drawing object floating above the worksh...

Page numbering 12-04-09
I have taken the trouble to review all the related previous posts but do not see the answer to meet my needs. I have a 7 page document which consists of a front and back cover 2 pages each and so far 3 pages of report. Page 2 is followed by a section break next page while page 5 is followed by a section break next page. In addition I also put a section break at the end of the document which I should not need. Numbering starts on page 3 as page 1 of Y which seems to work fine. However, page 4 and 5 do not display page numbers while page 6 displays page 1 of 6 and page 7 i...

Arranged by date
Is it possible to set up extra ranges for the arrange by groups in Outlook 2003. For example I would like to extend "arrange by date" to group emails by 2 months ago, 3 months ago, ... last year, 2 years ago...... Currently everything that is older than 1 month gets grouped as "Older" Any ideas. Regards Alex Weatherall ...

System date
The system date is correct for todays date. Maybe last October it off by a year? Hank ...

How to goto cell containing specific date
Thought I asked this before, but can't find the thread w/ my question or any replies... I have a worksheet wih a full year's dates in the cells running down a colum, with other data for each date in the the adjacent columns; Instead of scrolling up & down to a cell with a specific date I'm looking for, is there another way to goto a cell containing a specific date? (e.g., today(), or another specific date) In case this is pertinent: the date series begins with the entry of one date (e.g., 01/01/2010 in cell A1), with the dates in subsequent rows arrived at ...

How can I convert my Archive Folder from its OL2002 style to the new OL2003 style?
Dear friends: The size of my Archive Folder is approaching 2 GB. How can I convert my Archive Folder from its OL2002 style to the new OL2003 style? I understand that the OL2003 style doesn't have the 2 GB limitation. I use Outlook 2003. Any hint? Thanks Juan I. Cahis Santiago de Chile (South America) Note: Please forgive me for my bad English, I am trying to improve it! In news:1kp8j1le66sp3j25022kl7pktvbj63hnrp@4ax.com, Juan I. Cahis <jiclbch@SINBASURAattglobal.net> typed: > Dear friends: > > The size of my Archive Folder is approaching 2 GB. > > How can I conv...

how to count if the value falls between a date range
example 1/1/2005 1/2/2005 = 2 2/1/2005 2/2/2005 = 2 I am trying to get the ount if the value is between 1/12005 and 1/3/2005 and similarly 2/1/2005 and 2/3/2005 =countif(a1:a10,">="&date(2005,1,1)) - countif(a1:a10,">"&date(2005,1,3)) (I'm not sure what between means--include the end dates or not???) joe wrote: > > example > 1/1/2005 > 1/2/2005 = 2 > 2/1/2005 > 2/2/2005 = 2 > > I am trying to get the ount if the value is between 1/12005 and 1/3/2005 and > similarly 2/1/2005 and 2/3/2005 -- Dave Peterson Dave, ...

Blurry text in Bold
Bold characters in excel cells are blurred (reduced clarity) regular text appears fine. Please advise on remedy I understand that Word 2003 has similar issues on regular font. (deactivate ClearType was solution for word) I cannot find any info regarding Excel 2003 in knowledge base ...

Word windows 07 docs let's crash win for mac 2008
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Hello, <br><br>I got a problem with my word docs. <br> Everytime i would open and change something in my word doc (created on a pc) win for mac crashes. <br> When? on the moment a insert a frontpage, indexpage. <br><br>Who got a sollution for me? <br><br>Kind regards It's hard to say since you don't indicate; Whether Office is fully updated on your Mac, What your update level of OS X is, What version of Word was used to create the document, What f...

Mapping Associated Entities when Converting a Lead to an Account
I have a Lead record that, over time, has had one or more items from a custom entity (Many-to-1 relationship) associated with it. (This is somewhat akin to multiple Contacts associated to a single Account record.) When I convert the Lead to an Account, I would like those associated records to also be associated with the newly-created Account record. Is this possible? Something similar is happening when creating a quote from an Opportunity. In this situation, the associated product items are associated with the new quote. So, I'm hoping that it can be done in other situations....

convert files
how do I import and sort files from First Choice in ASCII form into excel and fix into columns? -- helpoldlady ...

Import plain text with formulas into Excel
I'm having some trouble in Excel. If I create a plain text file like the following: 1,2,3 4,5,6 =sum(a1:a2), =sum(b1:b2) Nothing can be done as if in spanish is (SI), iserror is (ESERROR), and so on. This really sucks as I can't just install other software on the server nor make other modifications. I don't know what Microsoft was thinking when they translated the versions; any Excel version should accept it's language commands and English commands, but well, that's too much to ask for... Any good sugestions? Regards -- schmiedel ---------------------------------...

Text fields in report writer
I have a modified report dictionary, when I go into a report layout and enter a text field, when I tab off the field what I wrote is converted to something totally different. Has anyone seen this before? Reports that I have been using for months suddenly the text fields print something totally different. ????? Any suggestions would be helpful. Thank you! Hi J. We use a shared dictionary located on our server, and I find text fields do not "travel" well - ie. I often lose info if I export a report out of the dictionary to work on it. What I do is make a copy of the enti...

how do i count mails with time and date
i have a helpdesk , i would like to know the tracking all mail , as like time and date on responded "Bhanu C" <Bhanu C@discussions.microsoft.com> wrote in message news:4F0F08B1-3302-43F9-B2F8-5FE1BEC99A9B@microsoft.com... >i have a helpdesk , i would like to know the tracking all mail , as like >time > and date on responded Why not just look at the InfoBar? -- Brian Tillman [MVP-Outlook] see http://www.slipstick.com/exs/customfields.htm - specifically the replied time section. If needed, you can copy rows to excel and compare dates etc. -- Diane Poremsky...

Formatting date fields after export
I am experiencing problems with my exported date fields into Excel from other applications. The data formats to "yyyy-mm-dd" and cannot be modified unless I double-click on each field. Has anyone else experienced this problem? And what solutions would you suggest? It is probably seen as text, select the imported dates, do data>text to columns, click next twice, under column data format select date and YMD click finish Regards, Peo Sjoblom "Raymond" wrote: > I am experiencing problems with my exported date fields into Excel from other > applications. The d...

Parse multiple text lines into 1 line in excel
help. I am an excel beginner and can't find out how to turn multipl lines of text into 1 row in excel. It's probably really easy but m manual is USELESS. Can anyone help ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com debbie You're a little short on details. If nothing below fits the bill post back. "Multiple lines" is how many and is each line in a separate cell down one column? Do you want all lines to go into one cell? You can use this form...

Publisher 07 Default Folder
I have Publisher 2007, how do I set the startup folder to something other than "MyDocuments"? I edited the properties and set the "Start-in Directory" to the desired one, but when I launch Publisher and attempt to open a file, it goes right back to "MyDocuments" Thanks Charliec ****************************************************** Charliec Change the name of the My Documents folder Do the following in these 2007 Microsoft Office system programs: Word, Excel, PowerPoint, or Access Click the Microsoft Office Button, and then click Save As. InfoPath, Project, Pub...

Outlook Plain Text Setting
I am starting to move my users over to Outlook and Exchange 2003. I noticed that by default Outlook is setting the users up as HTML e-mail. Is there a way that I can set all of my users up with Plain Text for e-mail? Thanks, Jeff That is a user setting, of course, under Tools-->Options-->Mail Format You can use the following to ensure inbound email is read as plain text, and therefor replies will be plain text: http://www.outlook-tips.net/howto/plain_text.htm "Jeff Grossman" <jeff.nospam@stikman.com> wrote in message news:lcr542hthnrf6qfqikqrm4cdebs11ikuaa@4ax.co...