year conversion in date fields eg 1900 to 2000

I moved a existing database from foxpro to access 2000.  
The dates in foxpro had a 2 digit year and when brought in 
to access it converted them to 1900 (eg. 11/12/00 to 
11/12/1900 instead of 11/12/2000).  Is there a way in 
access to convert (replace) only the year of vaious dates 
to another year. If this is not possible is there a way to 
prevent this while converting?

thanks alex
0
alex
1/22/2004 4:23:37 PM
access.conversion 3037 articles. 0 followers. Follow

1 Replies
899 Views

Similar Articles

[PageSpeed] 2

An update query can work for this.

Something like this:

UPDATE TableName SET [DateFieldName] =
DateAdd("yyyy", 100, [DateFieldName])
WHERE Year([DateFieldName]) = 1900;

-- 
       Ken Snell
<MS  ACCESS MVP>

"alex" <anonymous@discussions.microsoft.com> wrote in message
news:290501c3e104$16d78e10$a601280a@phx.gbl...
> I moved a existing database from foxpro to access 2000.
> The dates in foxpro had a 2 digit year and when brought in
> to access it converted them to 1900 (eg. 11/12/00 to
> 11/12/1900 instead of 11/12/2000).  Is there a way in
> access to convert (replace) only the year of vaious dates
> to another year. If this is not possible is there a way to
> prevent this while converting?
>
> thanks alex


0
Ken
1/22/2004 5:31:34 PM
Reply:

Similar Artilces:

Pivot Table not pulling all fields or data
Hi All........ Without getting too verbose, I have a 900 row by 26 column database I am applying a Pivot Table to. The code seems to run fine, but it is not returning some of the fields, and of course the related data. I have tried reformatting the entire columns I am pulling from but no joy......however, when I reformat an errant cell from General to TEXT, that row comes in........and/or, if I change the value in one of the missing cells, it will bring that row in. The code runs fine, and was mostly derived by recording a macro in XL2k, but the data cannot be trusted becau...

whole year calendar
does outlook have a whole year calenar so i can put an appointment in byfinding the date on the whole tear? thanks peter....... peterftl.codner@tiscali.co.uk From what I have seen, the Outlook Calendar allows entries for a year (or more) and can display all active appointments for a year (or more) in a Table View. -- Donald L McDaniel Post all replies to the Newsgroup, so that all may be informed. Remove the obvious to reply by email. +++++++++++++++++++++++++++++ "Peter" <peter.codner@lineone.net> wrote in message news:%23IpAYHp3DHA.3140@tk2msftngp13.phx.gbl... >...

Sorting file list by date/time...
Hello, does anybody have an idea how to program a sorting function given a directory with files, which should red to an array which is sorted by date and time, but contains only the filenames. Is there a MFC function or a shell function of the OS which could ease the job instead of doing it manually? Regards, Diana On Mon, 16 Apr 2007 16:39:50 +0200, "Diana Bulthaupt" <Diana.Bulthaupt(at)web.de> wrote: >does anybody have an idea how to program a sorting function given a >directory with files, which should red to an array which is sorted by date >an...

Access 2007 date function not working
I had a database in Access 2003 that I imported into 2007 and all works well except the date function in Reports. I have tried to go into reports and repair by adding a date field, but when I try to open the report and error box comes up asks for update parameter value for the date. The currrent date will not display in my reports, just (#name?) is in the date field. You would think trying to put the current date in a report would be easy. -- Thanks, Mark Use this --- =Date() -- KARL DEWEY Build a little - Test a little "Mark K" wrote: > I had a database in Access ...

Return a value when Date x is between Date y and z
Hi, I have a table something like this: A B C 1 From To Result 2 01/07/2009 01/07/2010 0-1 3 01/07/2010 01/07/2011 1-2 4 01/07/2011 01/07/2012 2-3 5 01/07/2012 01/07/2013 3-4 In another column (X) I have a list of Dates and I want return the "result" from the row where the date lies between. So for example in X3 I have 16/10/2011 and I can see this date is between the 2 dates in row 4 in the table above so I want to return "2-3" I've tried an array formula ...

Incorrect Field Names Appear in PivotTable Field Dialog Box
I have found that items deleted from my source, standard worksheet, are still appearing in the Pivot Table Drop down list. I have applied the sollution in MS202232 but it did not clearup the outdated data. I have refreashed the pivot table before and after runing the MACRO "Delete_Fields" Not sure what else can be done, I continue to search groups and MS. ******** This is the code for the MACRO based MS202232: Sub Delete_Fields() On Error Resume Next For Each pvtfield In Worksheets(PivotTables).PivotTables(PivotTable1).PivotFields For Each pvtitem In pvtfield.PivotItem...

restore exchange 2000 using ntbackup
Hi! My exchange 2000 server is remotely backed up every day using veritas 9.2. I am now testing disaster recovery and would like to use ntbackup locally on my disaster recovery exchange server. (I copied my .bkf file on my exchange server) I dismounted my databases and ran ntbackup locally on my exchange server. I cataloged the bkf file and selected the microsoft Information store that I want to restore. „Restore to „ I entered my exchange server name. The following error message is then displayed: „the specified computer is not a Microsoft Exchange server or ist Microsoft Excha...

How many recordset can be opened simultaneously for a MS-Access 2000 Database for adding /editing records
Hey Friends Can someone please suggest how many recordset can be opened simultaneously for a MS-Access 2000 for adding /editing records. I also want a comparison with MSDE for the same. Vikrant wrote: > Hey Friends > Can someone please suggest how many recordset can be opened > simultaneously for a MS-Access 2000 for adding /editing records. > I also want a comparison with MSDE for the same. Uhm... If I remember correctly that would be max. 20 connections. Successive CRecordset::Open(...) requests would fail. I don't know this parameter for MSDE. There's a limit in...

Newsreader gone missing in outlook 2000
My news tab has suddenly gone missing in the View- Goto menu. Any ideas out there. Reinstalling Office does not solve the problem. Check your version. This does not sound like Outlook 2000. -- Russ Valentine [MVP-Outlook] "Dave Ramsay" <davidsramsay@yahoo.com> wrote in message news:wYKdnbB_hYBDySPcRVn-vw@rogers.com... > My news tab has suddenly gone missing in the View- Goto menu. Any ideas > out there. Reinstalling Office does not solve the problem. > ...

Date + 10 days?
I have a field that shows the date I sent a notice to someone. I want to create a field that automatically shows the date the notice is due back, which is 10 days from the sent date. I would also like the field to show up in red when the due date is past. =?Utf-8?B?YXJjaDI1Mg==?= <arch252@discussions.microsoft.com> wrote in news:9F8D2485-C1BB-41DB-8B94-E5DA949F87C2@microsoft.com: > I have a field that shows the date I sent a notice to someone. > I want to create a field that automatically shows the date > the notice is due back, which is 10 days from the sent date. >...

Enter month, get the first date
Hi, I want to be able to select the month from the drop down list and once the month is selected, I want the first date cell to change itself to the first date of the month selected. For rest of the date cells I have just used the formula "First date cell + 1" any help will be appreciated!! Thanks a lot in advance. Check your other post. Gaurav wrote: > > Hi, > > I want to be able to select the month from the drop down list and once the > month is selected, I want the first date cell to change itself to the first > date of the month ...

inserting text form field in a text box
I'm designing a form that has several text boxes I want to be able to insert a text form field that I can type in different names in when needed, while keeping the rest of the text constant. Every time I go to insert form field from Legacy it is not available. Anybody have a solution. Your subject line has the missing clue. There is no way to insert a legacy form field (text or any other kind) in a text box, footnote, endnote, or header/footer. You can remove the text box and put the field into ordinary text, or you can use a text content control in a text box or elsewhe...

Contact to Address Relationship adding new Address fields
The Contact Entity has a number of field attributes attached to it from the Address table like Address1_city. With CRM 3.0 can I add a new field to the Address table and get it to link to the contact like Address1_City? If so how would I do that? I don't want to have to add a field to the contact table and then map it to the address table because that will mean having two copies of the same data and having them keep each other in synch in an onchange event, if I can. Can I do what I want or do I have to take the second approach? Mike You can do this in a completely unsupported w...

Retrieve information from another field / entity
Hello. I've searched around and found some code for me to be able to get the value from two fields in the Account Entity to two fields in the Opportunity Entity. This would happened on the OnChange event of my "client" / "account" field. This field is the customerid field and is type "customer". It seems the code is doing what it's supposed to do until the end...where it's supposed to populate the two fields in the opportunity. I get the following error: "There was an error with this field's customzied event. Field: customerid ...

Exchange Server 2000 on Standalone PC
Hi, I want to do some R&D with SQLMail, which needs Exchange Server. Can I install Exchange Server 2000 on a Standalone PC. My Standalone PC don't have a Network card. Thanks in advance, kamlesh Exchange 2000 requires an Active Directory domain. -- Neo [MVP Outlook] Due to the Swen virus, all e-mails sent to this account will be deleted w/out reading. "Kamlesh" <kamlesh2000@yahoo.com> wrote in message news:2644c0f6.0311212156.abbc746@posting.google.com... > Hi, > > I want to do some R&D with SQLMail, which needs Exchange Server. Can > I install...

Problem in saving Date and $ field in Excel to CSV file
We just copy and paste data from MS Access 2003 to Excel 2003. When we save the Excel as CSV file, we find that the date has been converted to American format. We have checked the Regional Settings and Australia is used. Is there any language setting in Excel ? Moreover, on another machine, we are able to save to non-American date. However, we find that for monetary field, most of them are quoted - Like "$2,000.00" but some are not quoted - like $580.00. Your advice is sought. Thanks Peter ...

Date entry automatically
I am creating a spreadsheet containing stats for a weeks work, broken down by day. I will use the same spreadsheet each week, just replacing the dates. If I enter Monday's date, is there a way to make the rest of the week's dates automatically appear? And would this work if the Monday was the end of the month, like Mon 30 May? Thank you, Rob, Teesside, UK You need only add 1 to Monday's date to get Tuesday, 2 for Wednesday, etc. Thus, if you will place your Monday starting date in A2 - say 4/11/05, then B2 would contain =A2+1 and C2 would contain =B2+1 ...

convert pub 2000 doc into a pdf file
how can i convert a pub 2000 file into a pdf doc so that i may email it to people who don't have publisher? Thanks! While in a state of ecstasy after repairing his laptop, Ed sees a message from regi k <regi k@discussions.microsoft.com>. On it is written: > how can i convert a pub 2000 file into a pdf doc so that i may email > it to people who don't have publisher? You need to use a program like PrimoPDF (www.primopdf.com) or Adobe Acrobat (www.adobe.com/acrobat) to do this. PrimoPDF is free, Acrobat isn't (you can't create PDFs using the free Acrobat Reader) ...

list mails sorted on date, independent of subfolder structure of INBOX
I hav e a lot of mail rules that sort incoming mails in subfolders of the INBOX. But now I want to see a list of all mails, sorted on date, independent of the folder structure of the INBOX. Possible ? how ? If you're using Outlook 2003, set up a search folder. If not, use = Advanced Find.=20 --=20 Sue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://www.turtleflock.com/olconfig/index.htm and Microsoft Outlook Programming - Jumpstart for=20 Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx =20 <Osiris>...

Update multiselect field in table with code
Access 2007 under Vista Using a form to select variables for a report. One of the fields is a multi-value field in the underlying table. Have determined that following the selection of required items from the multi-select combo box and the clicking of the OK button, the underlying table is not immediately updated. The table is however properly update once the form is closed. This does not meet my purposes for the report generation, as exiting the form and returning is not a reasonable approach. I would like to use the After Update event from the multi-select combo box to ac...

Analyical services License for SQL 2000 Standard
We are on SQL Server 2000 Standard Edition. Do we need any additional license for installing SQL Analytical services for Analysis cubes or is it already included when SQL Server 2000 Standard is purchased. thank you. The SQL Server Analysis Services portion is "free" because it is part of the SQL Server 2000 product. The prebuilt GP cubes are not free and must be purchased from MBS. In GP 9, for the benefit of those who may not know, the Business Intelligence Foundation layer includes the cube for the financial modules. The distribution and multi-company cubes are extra c...

Outlook 2000 address Book
When in Outlook 2000 > I see all of my Contacts from Inbox tree > When I click Tools> Address Book they do NOT show up > When I click send new email, click to, they do NOT show up The only thing I think I changed prior to the problem was in Internet Explorer - chnaged back to defaults under the advanced tab Hi RC, I understand that you are using Outlook 2000, and although you can see your contacts in the Contacts folder, they do not appear in your address book. In order for each contact folder to be displayed as an address book, each folder must be set to "Show this...

Problem transfering database from Access 2000 to Access 2003
Hi all I am having problems with a database which has been running for number of years. It was set up in Access 2000 and windows 98se. I hav now transferred the database to a new machine, running Office 2003 an Win. XP. The Mail Merge automation now does not work and gives me Run time error�432� File Name or Class not found during automatio operation. When I use the debug it points to the following line in th code , Set objWord = GetObject("C:\Church Documents\") & strDocName On further investigation objWord =0 And & strDocName= mail.doc, whic is the name of the word do...

Outlook 2000
I have been performing critical windows updates on our machines at work and have been getting reports that the "Customize Outlook Today" button is no longer working. One machine has had all Office updates installed, and most of the other have not. Does anyone have any idea of what happened or how to fix? Help is greatly appreciated. Eric On Fri, 7 May 2004 15:52:25 -0700, "Eric" <anonymous@discussions.microsoft.com> wrote: >I have been performing critical windows updates on our >machines at work and have been getting reports that >the "Custo...

M02 & M05: Download date doesn't change
I don't use Passport or anything like that with my Money program. I import my bank transactions from my credit union site into my checking account in Money. I had M02 and a year ago, the "downloaded:" date stopped working. It's stuck on 10/13/2003 at 12:00 AM. I figured maybe it had something to do with the file being an "OFX" file now instead of whatever format it had always been ("OFC?"). I also figured when I upgraded to a newer version of Money, it would go back to giving the last date I exported the file. It doesn't. Any ideas? It's not ...