Convert Date To Time Back To Date

[Lovely Angel For You]

Hi,

I know the subject line is too confusing. I didnt know how to explain
this so I used what I want.

I had some one do some data entry work for me sometime back. Now this
guy, used his own brains at added time in the format of MMD:YYYY
instead of MM/DD/YYYY. Now once that is done, as the field was set to
store date, it converted the MMD:YYYY to some date, other than the
actual one. So I now I have completely new date with me. Now I need to
take it back to original date. And I have no clue on how to do it.

e.g.

Actual Date - 04/08/06
Entered As - 04:08:06
Visible As - 12/30/1899

Now if I do the following in VB6:
abc = Format$("04:08:06", "mm/dd/yyyy")
I get this 12/30/1899

However I know of no function which can reverse that. Any ideas on how
to do this.

Best Wishes
Lovely

________________________________________________________________________________

P.S As I was thinking on getting it resolved in VB, this query is
posted in that news group as well.

 

[missinglinq via AccessMonster.com]

Try using the Replace function:

Replace(YourFieldNameHere,":","/")

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000

Message posted via AccessMonster.com

 

[BruceM]

If you format the field as Long Time you will see the 04:08:06. Access uses
serial numbers behind the scenes to store dates. The numbers before the
decimal point are the day, and the numbers after it represent the time of
day. If you have the value 0 in a number field, then format it as General
Date, Access will interpret that as 12/30/1899. 1 is 12/31/1899, 2 is
01/01/1900, -1 is 12/29/1899, and so forth. 1.1 is 2:24 AM, 12/31/1899.
When the operator entered the date as if it was time, Access interpreted it
as a time value on day 0 (12/30/1899). If you format the field as General
Date you should see the "missing" numbers.
One way to get the numbers back (after backing up the database) is to create
a select query that filters for 12/30/1899, then use something like the
following in an empty column in query design view:
DateFix: Replace(CStr(Format([datefield],"hh:nn:ss")),":","/"))
If there are just a few dates it may be simplest to copy the resulting value
to the correct field. If there are a lot of records, you can run a
make-table query. In the new table, delete the old date field and rename
DateFix with the same name as the old date field. Create another make-table
query in which you select dates other than 12/30/1899. Finally, run an
Append query to merge the two tables.
I expect there is a simpler way to go about that process, but I tried
without success to create an update query. I understand why the situation
occurred, but I hope somebody jumps in with a better idea for making it
right.

 

[BruceM]

By the way, if you post to more than one group, include all of the groups in
the address line of a single message (cross-posting) rather than sending
separate messages (multi-posting). It is good that you mentioned it, but it
would be far better to cross-post.

 

[John W. Vinson]

Hi,

I know the subject line is too confusing. I didnt know how to explain
this so I used what I want.

I had some one do some data entry work for me sometime back. Now this
guy, used his own brains at added time in the format of MMD:YYYY
instead of MM/DD/YYYY. Now once that is done, as the field was set to
store date, it converted the MMD:YYYY to some date, other than the
actual one. So I now I have completely new date with me. Now I need to
take it back to original date. And I have no clue on how to do it.

e.g.

Actual Date - 04/08/06
Entered As - 04:08:06
Visible As - 12/30/1899

You can use an Update query:

UPDATE tablename
SET datefield = DateSerial(Second([datefield]) + 2000, Hour([datefield]),
Minute([datefield])
WHERE [Datefield] BETWEEN #12/30/1899# AND #12/31/1899#);


John W. Vinson [MVP]