y2k date issue

I have an old database file in DBF format that I use Microsoft Access
to display. The dates in the DBF file only has 2 digits, so when it's
opened under Access, some dates are displayed as "19xx", while other
dates just as "xx". It creates quite a problem when I sort or search
by the dates. I do have a simple way to figure out whether the year
"xx" is 20xx or 19xx, so I was wondering if there is a way to add a
small macro for the date fields, such that when this DBF file is
opened under Access, proper conversion of the year would take place
automatically.

0
namsilat
4/29/2007 4:42:15 AM
access 16762 articles. 3 followers. Follow

3 Replies
1095 Views

Similar Articles

[PageSpeed] 51

On Sun, 29 Apr 2007 00:42:15 -0400, namsilat <talismana@hotmail.com> wrote:

>I have an old database file in DBF format that I use Microsoft Access
>to display. The dates in the DBF file only has 2 digits, so when it's
>opened under Access, some dates are displayed as "19xx", while other
>dates just as "xx". It creates quite a problem when I sort or search
>by the dates. I do have a simple way to figure out whether the year
>"xx" is 20xx or 19xx, so I was wondering if there is a way to add a
>small macro for the date fields, such that when this DBF file is
>opened under Access, proper conversion of the year would take place
>automatically.

Access uses (by a changeable default) 1930 as a cutoff: two digit dates 30-99
are assumed to be in the 20th century, 00-29 in the 21st.

If your dates just have the two digits, you need *SOME* sort of convention
that fits the "business rules" of YOUR data - which very will might be
different from someone else's business rules! Are the dates in this table the
birthdates of adults? If so, they're probably all 19xx - though my late friend
Anita was born in '97 and lived until '04; one of her
great-great-grandchildren was born in '97 as well. 

Or are your dates bond maturity dates? Those will probably be in the future.

So there IS no "proper" conversion. That's why we had a Y2K crisis - the two
digit dates *are ambiguous* and require external information to remove the
ambiguity.

             John W. Vinson [MVP]
0
John
4/29/2007 5:11:23 AM
The "cut-off" in my DBF file is actually 1908, because there are
birthdays of people over 90 years old. There are some entries made
after 2000, and those dates despite having only 2 digits for years,
are displayed as "19xx" by MS Access. No dates were displayed as
"20xx" that I could see. For entires made prior to 2000, they are all
displayed as "xx" by MS Access regardless of which year it is.

I wonder if there is an external program that I can write up a small
macro to convert the DBF file before it's imported into MS Access.






On Sat, 28 Apr 2007 23:11:23 -0600, John W. Vinson
<jvinson@STOP_SPAM.WysardOfInfo.com> wrote:

>On Sun, 29 Apr 2007 00:42:15 -0400, namsilat <talismana@hotmail.com> wrote:
>
>>I have an old database file in DBF format that I use Microsoft Access
>>to display. The dates in the DBF file only has 2 digits, so when it's
>>opened under Access, some dates are displayed as "19xx", while other
>>dates just as "xx". It creates quite a problem when I sort or search
>>by the dates. I do have a simple way to figure out whether the year
>>"xx" is 20xx or 19xx, so I was wondering if there is a way to add a
>>small macro for the date fields, such that when this DBF file is
>>opened under Access, proper conversion of the year would take place
>>automatically.
>
>Access uses (by a changeable default) 1930 as a cutoff: two digit dates 30-99
>are assumed to be in the 20th century, 00-29 in the 21st.
>
>If your dates just have the two digits, you need *SOME* sort of convention
>that fits the "business rules" of YOUR data - which very will might be
>different from someone else's business rules! Are the dates in this table the
>birthdates of adults? If so, they're probably all 19xx - though my late friend
>Anita was born in '97 and lived until '04; one of her
>great-great-grandchildren was born in '97 as well. 
>
>Or are your dates bond maturity dates? Those will probably be in the future.
>
>So there IS no "proper" conversion. That's why we had a Y2K crisis - the two
>digit dates *are ambiguous* and require external information to remove the
>ambiguity.
>
>             John W. Vinson [MVP]

0
namsilat
5/1/2007 12:39:56 AM
On Mon, 30 Apr 2007 20:39:56 -0400, namsilat <talismana@hotmail.com> wrote:

>The "cut-off" in my DBF file is actually 1908, because there are
>birthdays of people over 90 years old. There are some entries made
>after 2000, and those dates despite having only 2 digits for years,
>are displayed as "19xx" by MS Access. No dates were displayed as
>"20xx" that I could see. For entires made prior to 2000, they are all
>displayed as "xx" by MS Access regardless of which year it is.
>
>I wonder if there is an external program that I can write up a small
>macro to convert the DBF file before it's imported into MS Access.
>

I don't understand. Have you imported the .dbf file into an Access table, or
just linked to it? Do you have a Date/Time field? What are some examples of
the dates? 

You can set the Format property of a date/time field to display four digit
years. Try setting the format to "mm/dd/yyyy", or use Start... Control
Panel... Regional and Language Options to set the default short date format to
use four digit years. Do note that (unless you do so) dates like 2003 will
indeed display as 03.

Again... the cutoff is 1930. 00 through 29 will be displayed with just the two
digits, and will be assumed to be 2000 through 2029; 30 through 99 will be
stored as 20th century dates but will be displayed with four digits.

You can very easily run an Update query updating your 21st century dates. Use
a criterion 

>= #1/1/2000#

on the date field, and update the DOB field to

DateAdd("yyyy", -100, [DOB])

to set the date back one century.

             John W. Vinson [MVP]
0
John
5/1/2007 1:08:30 AM
Reply:

Similar Artilces:

Free Trial Issue
Hello, I just downloaded the Publisher 2007 free trial, recieved the email with the trial key, and was able to open the program. However, when I try to start a New project nothing will open...I keep getting the response ' Publisher cannot complete the operation'. What can I be doing wrong? Thanks! Did you activate the trial? Do you have a default printer selected? Try opening Publisher as the administrator. -- Mary Sauer http://msauer.mvps.org/ "michele411" <michele411@discussions.microsoft.com> wrote in message news:BCE8D514-B734-4E0C-929C-A6D4BB32D813@micros...

strange date format problem
Hi, I downloaded some data on excel using Bloomberg. One column has dates and the other has numbers. Dates appear as 01/01/1980 format which I want in Jan-80 format. I have changed the format cell properties but it still appears as 01/01/1980. I have to goto individual cell and press F2 <enter> so see the effect. Dont know why? I cant even copy paste format after that .... TIA/amag I am not sure, but is your workbook set to Auto-Recalculate? Try pushing <F9> to do a manual re-calculation, see if that changes the formats. If it does, go to Tools (on the menu bar), Options, Ca...

How to sort by name then date?
I would like to look through all the emails to a particular person. If I click on the "To" column I can see all the emails to a particular person (actually there is a problem with that which I will mention next) but they seem to be in random order. Is there a way to have them sorted by date? (i.e. by name first, then by date). Actually, sorting on the "To" column does not get all the emails sent to a given email address. Because Outlook uses the "display as" field. It will often give different "display as" names to the same email address. C...

Code 19 with Amex issued by Costco
I get an error : Code 19 when I swipe American Express cards that are issued by Costco. All others swipe fine. I can manually type the Costco cards, and they approve. Anyone else been down this road before ? Thanks for any help. Ed This is a multi-part message in MIME format. ------=_NextPart_000_06F2_01C71CC9.BD72F3A0 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Ed, Check with your processor as the Costco card and others have a special = discount rate (higher) attached to them and you may not be able to = accept them. --=20 Jef...

Concatenate including a date so that the date appears as a date
When I try to join the contents of several cells, one of which is a date and time obtained from =now(), the date and time part appears as a number. How can I make it appear as a date and time? I have tried changing the cell formats - probably there is a combination that works but I haven't found it. I also tried copying the original date cell to another cell and changing the latter cell's format and concatenating using that latter cell, but still no success. Use the TEXT function. For example, =TEXT(NOW(),"mmmm d, yyyy") would give January 6, 2010 "dd...

printing error handling issue? (when clicking cancel get 2501 error)
I have a report that when it is closed the DoCmd RunCommand acCmdPrint occurs ... but of course if the user clicks cancel, it gives me a 2501 error. How & where do I put code to surpress this error. I would just the close even to run the DoCmd RunCommand acCmdPrint and if cancel is selected just continue to close the report? TIA, _Bigred I understand that I have to do this, I actually don't understand HOW to do it, and where to put code to accomplish this?? 1) What code do I use? 2) Where do I put the code? Thanks, _Bigred "Duane Hookom" <duanehookom@NO_...

if statement with dates #2
I would like to have a statement like: IF(a1>0,1/1/2004) with 1/1/2004 beint a date. EXCEL is interpreting 1/1/2004 as an arithmetic statement and giving me the result of the arithemtic. I could put in the numeric offste for the date and write IF(a1>0,37987), formatting the cell as a date, but this seems awkward. What else can I do? TIA, Sanford ...

Count Unique Cells, within a date range (dates stored in separate column).
Hello All: I am attempting to create a formula that will count the number of unique cells (alpha-numeric) in an entire column (no set length, so ex: "$B:$B"). This count will be based on a date range (Between 2 dates). The dates are stored in another column (Ex: "$A:$A"). The following is an example of the cells. Dates PO_Num 1/2/2010 A123456 1/3/2010 B453453 2/2/2010 A123456 3/4/2010 C2342F3K 4/3/2010 123456 I would like to know a formula that can count the number of different PO's based on a given date range. Please help me if you...

Counting Dates?
=COUNTIF(A1:A100,"<" & NOW()) This works good for specific cells. I have a similar question. How would I do the same thing given that: 1) You only have a starting date 2) Your only wanting to count work days (in this case 4 day/wk) up to NOW() Thank you for all your help. Aviator On Thu, 6 Jan 2005 12:53:03 -0800, "Aviator" <Aviator@discussions.microsoft.com> wrote: >=COUNTIF(A1:A100,"<" & NOW()) > >This works good for specific cells. I have a similar question. > >How would I do the same thing given that: >1) You only ha...

An expression for dates from and to
I am trying to create an expression to select items from a date field falling from a certain date to a certain date. I am obviously missing something in my expression. The field name is DateComplete. How can I set up this expression to pull date from a certain date to a certain date? -- Ronnie Hi Ronnie, An example of the SQL would be: SELECT * FROM [TABLE NAME] WHERE DateComplete Between #1/1/2006# And #1/1/2007# ; On May 23, 3:52 pm, Ronnie <Ron...@discussions.microsoft.com> wrote: > I am trying to create an expression to select items from a date field falling > from a...

Returning only MAX(date) not all dates
I want to be able to list the most current date an item is checked out. As I was working through the query I used max as an aggregate function but I still can't get the simple result I want. I'd rather list the fields I have chosen and somehow select just the most current checkout date. Any help would be greatly appreciated. Here is my query: SELECT tblTerritory.TerritoryID, [TerritoryName] & " " & [TerritoryDescription] AS Description, tblTerritory.TerritoryTypeID, tblTerritoryType.TerritoryType, Max(tblTerritoryCheckout.DateCheckedOut) AS Checked...

KB817809
Howdy all, Does anyone happen to have the hotfix for Exchange2k3 referred to in the above KB article? I just dropped a note to MS to see about getting the hotfix sent to me, but I really need to get the problem taken care of ASAP and it'd be much quicker to just be able to download it/get it sent to me from somewhere. This is in reference to the fact that there's no non-3rd party method of getting non-MAPI email sent to public folders to display as ipm.note rather than ipm.post. TIA, Jeremy Call MS - tell them you are having the exact problem listed in that KB. They will email ...

System Date
Can anyone help me on how to force the system date to a member field of "Date" type? I could not find any help on msdn that comes with visual c++. Any hint or help will be appreciated. Thanks, Behrouzk Are you just trying to get the current date. You can use CTime::GetCurrentTime() or COleDateTime::GetCurrentTime() Tom "Behrouzk" <Behrouzk@discussions.microsoft.com> wrote in message news:D8FA4B6B-73CE-4058-B50C-C9BEBAB67E68@microsoft.com... > Can anyone help me on how to force the system date to a member field of > "Date" type? I could not fi...

Conditional Formatting
Hi, I have a spreadsheet that is for tracking work. I would like to have conditional formatting so that if the date in column E is between todays date and 4 weeks away then make it blue, if it is a date that is after 4 weeks away then make it yellow but if it is in the past make it red. Can this be done with conditional formatting or does it need vba? Also it would be nice if the whole row changes colour even if just the condition is on the date which is in column E - but if that can't be done then just the cell is fine. Thanks Assume the dates start in E2, select all dates w...

Time and date problem
Hello I have spreadsheet cell (B1) that contains a date and time: 05/11/2003 13:45:50 I have another series of cells (A1 to A100) that contain an increment of 30 seconds: 30.00 I would like to add the seconds to the date and time putting the result in column C, I have tried to do: =B1+(A1/100000) but the answer is wrong by 4 seconds and then gets worse the further down the column I go. Can somebody show me how to add the seconds to the date please. Thanks Tom Tom, Right method, wrong divider! 86,400 secs in a day.. change your 100000 to 86400 and you're there. Nikos Y. (nyannaco ...

Date subtraction -How to not show negative when 2nd date not entered
Hi, I have a section of a worksheet that just needs to show working days between two dates. For example, an order was entered on 9/2/07 and complete on 9/15/07 The formula =NETWORKDAYS(A1, A9) works perfectly except that until the "completed date" ise filled in, a negative number appears. Is there a way to have that remain blank until both dates have been entered? Thanks! Hi Edward, change your formula as =IF(A9="","",NETWORKDAYS(A1, A9)) -- Pranav Vaidya VBA Developer PN, MH-India If you think my answer is useful, please rate this post as an ANSWER...

Automatically send email based on date value
Is there way to send an email automatically based on a date field in a table? Some more information about what I am trying to do. I have a form where users enter in upcoming bids. I would like to send a reminder email out some specified time (ie, 14 days, 7 days, and again 3 days) prior to the bid being due based on the Bid due date field in the table. I don't want to have to go into the form and hit a button, I would just want it to review the table perhaps every time the database is open or even when the form is opened and send the email. The email only needs to go to one person, so ...

enter days and dates
I've used Fill handle to add all the dates of 2009 in cloumn A. 1) How do I enter all the days (Mon, Tue, etc) in Column B based on column A? 2) What to do if I want only the weekdays in column A? Thanks for the help, 1) =A1, format as ddd 2) format as ddd -- Kind regards, Niek Otten Microsoft MVP - Excel "cpliu" <spamfreeliu@yahoo.com> wrote in message news:edcc7e34-edd1-4718-b2a5-1d758546d8ee@a26g2000prf.googlegroups.com... > I've used Fill handle to add all the dates of 2009 in cloumn A. > 1) How do I enter all the days (Mon, Tue, etc) in Column B based...

Dates in cells #2
When I start a spreadsheet my first step is to format the columns and the date format is selected. Occasionally when entering dates they do not follow the format for that column or other dates in that column. Am I doing something wrong? Anyone else have this problem. Ralphael, the OLD one Hi Ralphael1, You may want to create an excel template for your date format. Just type this on the cell "=NOW( )" format the cell you prefer. "Ralphael1" wrote: > When I start a spreadsheet my first step is to format the columns and > the date format is selected. > Occasiona...

Excel issue saving files to hexadecimal filenames on terminal server...
Hi, I have a customer who has an issue with Excel. He has people connecting to a terminal server where the spreadsheets and excel is installed and they open the sheet, make changes, and then save/close the sheet. The issue is that sometimes the saved sheet ends up with an 8 digit hexadecimal filename. The users do not get an error and we don't see anything in an eventlog. Any ideas? We read something about excel and the way it saves files. First to the hex name, then it deletes the original file, then it renames the hex name to the normal name. I have to assume something is m...

My stock transaction date is off 1 day
I just installed the trial version of MSMoney 2004. I downloaded my data from my broker, but almost all the transaction dates in MSM are listed in the Capital Gains Report as the day before the actual transaction. I say almost all, because some are correct. I also bought and installed Quicken (i'm gonna return it though) and it has all the dates listed correctly. Any idea how i can fix this?? ron Please insert an E between the n & t, to reply via email. In microsoft.public.money, rondi wrote: >I just installed the trial version of MSMoney 2004. I downloaded my >dat...

Automatic date entry upon cell completion
I have been unable to figure out how to have the current date automatically inputted when another cell is completed. For example, when an employee types his initials in B4, I would like the current date to appear automatically in C4. Is this possible, and if so, what is the best way to achieve this? Many thanks, Amber --- Message posted from http://www.ExcelForum.com/ This requires a worksheet_change event macro. Right-click on the worksheet tab, View Code, and paste this in: Sub Worksheet_Change(ByVal Target As Excel.Range) If Not Intersect(Target, Range("B4")) Is Nothing ...

Finding Dates in a date range
I have a column in a spreadsheet that consists of dates in dd/mm/yyyy format. I'm trying to write a formula that looks at the dates by row in the colum and if the date falls within a certain range (eg from 01/04/2004 to 01/06/2004) then I want to sum the values in another column. I've worked out that I need to use the sumif function but I can't work out how to analyse the date range. How do I tell the function to look at the dates in the column and if they are within a certain range to sum the values in another column. As I said, I'm pretty sure I need to use the sum...

Simple Date Display Question
Hello, This is a simple question I think. I have an Excel worksheet that is displaying dates in the Excel standard date format (five digit number). However, when I click on a cell, the date is displayed in m/d/yyyy format in the formula bar. How do I get Excel to display it that way in the worksheet??? Note that the cells are formatted as date, with mm/dd/yy selected. Thanks, Seth You could always try just formatting the cell as text and then enter the date as mm/dd/yyyy. Or maybe I am not getting what you are asking. "Seth" wrote: > Hello, > > This is a simp...

Date formatting problem #5
Hi there, I've tried everything I can think of with this one - help!! I'm reading dates from 2 cells, storing them in variables, comparing them and then storing the resulting date in a different cell. Code currently is: If stStoredDate<> stDate then stStoredDate=stDate Activecell.Offset(0,1).Value = stStoredDate End If My problem is stStoredDate = 04/09/05 (4 Sep 05 as I'm in the UK), stDate is the same, but the value in the cell is 09/04/05 (9 Apr 05). Both cells have identical formatting. If I change the formatting to 'general', stStoredDate= 38599, but t...