change a field into date format

[Guest]

I have a table that is provided from higherup that gives me an order header
date in this format 000070207 where the last six digits are the actual
shipping date. I want to get rid of the three left 0's and format the date is
something like mm/dd/yy. Everything I have tried doesn't do it, frankly
because I don't what I'm doing. I would appreciate any help you may give me.

 

[John W. Vinson]

I have a table that is provided from higherup that gives me an order header
date in this format 000070207 where the last six digits are the actual
shipping date. I want to get rid of the three left 0's and format the date is
something like mm/dd/yy. Everything I have tried doesn't do it, frankly
because I don't what I'm doing. I would appreciate any help you may give me.

Add a new Date/Time field to the table; run an Update query updating it to

CDate(Format(Right([OrderHeaderDate], 6), "@@/@@/@@"))


John W. Vinson [MVP]

 

[Marshall Barton]

I have a table that is provided from higherup that gives me an order header
date in this format 000070207 where the last six digits are the actual
shipping date. I want to get rid of the three left 0's and format the date is
something like mm/dd/yy.

Try this kind of expression as a calculated field in your
query:

CDate(Format(Mid([order header date], 4), "@@\/@@]/@@"))

If that's not formatted the way you want it, select the
desired format in the form/report text box's Format property
or, if you are exporting the query, set the field's Format
property.

 

[John Spencer]

If the DateField ALWAYS has data in it and the last six can always be
interpreted as mm./dd/yy date then you should be able to use

DateValue(Format(Mid(DateField,4),"@@/@@/@@"))

I would check the value first to make sure it can be interpreted as a date.
So the entire expression becomes:
IIF(
IsDate(Format(Mid,(DateField,4),"@@/@@/@@")),DateValue(Format(Mid(DateField,4),"@@/@@/@@")),Null)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

I have a table that is provided from higherup that gives me an order header
date in this format 000070207 where the last six digits are the actual
shipping date. I want to get rid of the three left 0's and format the date is
something like mm/dd/yy. Everything I have tried doesn't do it, frankly
because I don't what I'm doing. I would appreciate any help you may give me.

Add a new Date/Time field to the table; run an Update query updating it to

CDate(Format(Right([OrderHeaderDate], 6), "@@/@@/@@"))


John W. Vinson [MVP]

John
I placed your formula in the update to field an received a "Type conversion
error" and no records updated.

 

[Guest]

I have a table that is provided from higherup that gives me an order header
date in this format 000070207 where the last six digits are the actual
shipping date. I want to get rid of the three left 0's and format the date is
something like mm/dd/yy.

Try this kind of expression as a calculated field in your
query:

CDate(Format(Mid([order header date], 4), "@@\/@@]/@@"))

If that's not formatted the way you want it, select the
desired format in the form/report text box's Format property
or, if you are exporting the query, set the field's Format
property.

Marsh
I receive an #Error in the Expression1 field with this statement.

 

[Guest]

John where should I put this in the query?

If the DateField ALWAYS has data in it and the last six can always be
interpreted as mm./dd/yy date then you should be able to use

DateValue(Format(Mid(DateField,4),"@@/@@/@@"))

I would check the value first to make sure it can be interpreted as a date.
So the entire expression becomes:
IIF(
IsDate(Format(Mid,(DateField,4),"@@/@@/@@")),DateValue(Format(Mid(DateField,4),"@@/@@/@@")),Null)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Marshall Barton]

I have a table that is provided from higherup that gives me an order header
date in this format 000070207 where the last six digits are the actual
shipping date. I want to get rid of the three left 0's and format the date is
something like mm/dd/yy.

Try this kind of expression as a calculated field in your
query:

CDate(Format(Mid([order header date], 4), "@@\/@@]/@@"))

If that's not formatted the way you want it, select the
desired format in the form/report text box's Format property
or, if you are exporting the query, set the field's Format
property.

Marsh
I receive an #Error in the Expression1 field with this statement.

I had a fat finger typo in there. Trying again:

CDate(Format(Mid([order header date], 4), "@@\/@@\/@@"))

 

[Guest]

I receive a #Error with this expression also. The table that the query comes
from has this formated as a text field if that means anything.

Skip Bisconer wrote:
I have a table that is provided from higherup that gives me an order header
date in this format 000070207 where the last six digits are the actual
shipping date. I want to get rid of the three left 0's and format the date is
something like mm/dd/yy.


Try this kind of expression as a calculated field in your
query:

CDate(Format(Mid([order header date], 4), "@@\/@@]/@@"))

If that's not formatted the way you want it, select the
desired format in the form/report text box's Format property
or, if you are exporting the query, set the field's Format
property.

Marsh
I receive an #Error in the Expression1 field with this statement.

I had a fat finger typo in there. Trying again:

CDate(Format(Mid([order header date], 4), "@@\/@@\/@@"))

 

[Marshall Barton]

Double check that you did not inadvertantly name the text
box the same as then order header date field used in the
expression. If you did, then change the name of the text
box to something else (e.g txtOrderDate).
--
Marsh
MVP [MS Access]

I receive a #Error with this expression also. The table that the query comes
from has this formated as a text field if that means anything.

:
Skip Bisconer wrote:
I have a table that is provided from higherup that gives me an order header
date in this format 000070207 where the last six digits are the actual
shipping date. I want to get rid of the three left 0's and format the date is
something like mm/dd/yy.


Try this kind of expression as a calculated field in your
query:

CDate(Format(Mid([order header date], 4), "@@\/@@]/@@"))

If that's not formatted the way you want it, select the
desired format in the form/report text box's Format property
or, if you are exporting the query, set the field's Format
property.


Marsh
I receive an #Error in the Expression1 field with this statement.

I had a fat finger typo in there. Trying again:

CDate(Format(Mid([order header date], 4), "@@\/@@\/@@"))

 

[John Spencer]

Where do you need it?

Are you trying to UPDATE existing records? Are you trying to INSERT new
records? Or are you just trying to display existing records with a date
value?

In a SELECT (display the records) just stick the expression in as a new
field.



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================