Date problems

[rupelo]

Well here is my problem. I have just started with this company and they
have this Access Database whish I have been working on. The previous DB
administrator made an error in my eyes by setting a field which is
supposed to be a date to text and had all the data entry clerks enter
the date like (yymmdd) with no seperators. I was wondering if there is
a wasy that I could get the date to the normal mm/dd/yy format .

Thanx
Rupelo

 

[Allen Browne]

Open the table in design view.
Add a new field named Date1, of type Date/Time.
Save. Close

Create a query using this table.
Change it to an Update query (Update on Query menu.)
Access adds an Update row to the grid.

Drag the new Date1 into the grid.
In the Update row, enter:
DateSerial(Left([d],2), Mid([d],3,2), Right([d],2))
substituting your field name instead of "d".

In the next column, in the Field row, enter:
Len([d])
against substituting your real field name.
In the Criteria row under this, enter:
6

Run the query (Run on Query menu.)
Check that the new date field is populated correctly.

Optionally, you can then delete the old text-date, rename the new Date/Time
field with the same name, and modify any queries, forms, or reports so they
handle a real date now instead of text.

Before you rename the new field to the old name, make sure the Name Auto
Correct boxes are unchecked under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

 

[Joseph Meehan]

Well here is my problem. I have just started with this company and
they have this Access Database whish I have been working on. The
previous DB administrator made an error in my eyes by setting a field
which is supposed to be a date to text and had all the data entry
clerks enter the date like (yymmdd) with no seperators. I was
wondering if there is a wasy that I could get the date to the normal
mm/dd/yy format .

Thanx
Rupelo

Note: There are times that using text for a date filed may be preferred.
If you are not going do any calculations on that date field there really is
no reason it need be date. However always look ahead. Is there any chance
in the future that you will want to do calculations?

 

[rupelo]

If by calculations u mean running querries to obtain records between
certain time frames... then the answer is yes, there will be
calculations done on it. I believe before that they didnt have any
querries or what not running on this field.

Rupelo

 

[rupelo]

I am getting a "Data type Mismatch in the Criteria Expression"

 

[John Vinson]

Drag the new Date1 into the grid.
In the Update row, enter:
DateSerial(Left([d],2), Mid([d],3,2), Right([d],2))
substituting your field name instead of "d".

Allen, I believe that DateSerial requires the full four-digit integer
year, does it not? I'd suggest using CDate if there's a mix of 20th
and 21st century dates:

CDate(Mid([datefield], 3, 2) & "/" & Mid([datefield], 5, 2) & "/" &
Left([datefield], 2))

If all dates in the table are 2000 or later, then you could use
DateSerial but you'ld need to add 2000 to the yy portion (and probably
use Val() to convert the strings to numbers):

DateSerial(2000 + Val(Left([d], 2)), Val(Mid([d], 3, 2)),
Val(Right([d], 2)))

John W. Vinson[MVP]

 

[Joseph Meehan]

If by calculations u mean running querries to obtain records between
certain time frames... then the answer is yes, there will be
calculations done on it. I believe before that they didnt have any
querries or what not running on this field.

In the format presented, I believe you could do the queries. I would
have to check on the zeros.

 

[Allen Browne]

A quick test here suggests that DateSerial() interprets a 2-digt year
correctly, John.

But I do have a non-US date format; don't know if that makes a difference.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Drag the new Date1 into the grid.
In the Update row, enter:
DateSerial(Left([d],2), Mid([d],3,2), Right([d],2))
substituting your field name instead of "d".

Allen, I believe that DateSerial requires the full four-digit integer
year, does it not? I'd suggest using CDate if there's a mix of 20th
and 21st century dates:

CDate(Mid([datefield], 3, 2) & "/" & Mid([datefield], 5, 2) & "/" &
Left([datefield], 2))

If all dates in the table are 2000 or later, then you could use
DateSerial but you'ld need to add 2000 to the yy portion (and probably
use Val() to convert the strings to numbers):

DateSerial(2000 + Val(Left([d], 2)), Val(Mid([d], 3, 2)),
Val(Right([d], 2)))

John W. Vinson[MVP]

 

[Allen Browne]

The data type mismatch could be caused by:
- Nulls
- Bad data in the text field (e.g. 06o101 where the letter oh is not a
zero.)

Add your 6 digit field to the query, and type this into the Criteria row
beneath it:
Format(Val(Nz([d],""), "000000"))
again replacing d with your field name.

If the error persists, you could try:
DateSerial(Val(Nz(Left([d],2),"")), Val(Nz(Mid([d],3,2),"")),
Val(Nz(Right([d],2),"")))

 

[rupelo]

It worked ...however it is only giving me one date, and it does not
match any of the dates in the previous date field

 

[Allen Browne]

Now you have the date working, go back to the first reply I gave you.

It explains how to set up an Update query to populate the true date/time
field.

Once you have run the query, you will have the date in the new field on each
row of your table.