How can I change imported 20050131 format to 1/2005 format

  • Thread starter Access Newbie looking for help
  • Start date
A

Access Newbie looking for help

I imported some data that is in the Year Month Day format and want to change
it to month/year format can this be done??????
 
A

Access Newbie looking for help

Ofer thank you for your help. the colum that has the year month day is
titled Date.

As a result, in my query, I added a column and typed the following next to
each catagory:

Field Name: Expr1: [DATE2]
Table: 22 since this is the name of my table
Total: Expression (i selected this from the drop down menu)
Sort: Assending
Show: I left this unchecked
Criteria: Mid([Date],5,2) & "/" & Left([Date],4)


when I click on the datasheet icon I am prompted to enter a data value for
22.date2. If I enter 1/2005 the data sheet shows only heading but not data.
What am I doing wrong.

Your help is greatly appreicated.
 
O

Ofer

When you use the format, the filter looks for a string that valued as 01/2005
and not 1/2005.
If you want to let the user enter both 01/2005 or 1/2005 and still filter on
the date try this

Where Mid([Date],5,2) & "/" & Left([Date],4) = format( [Enter Date
here:],"mm/yyyy")

I also would recomand changing the date field name to something else, date
it a reserved name in Access.

Access Newbie looking for help said:
Ofer thank you for your help. the colum that has the year month day is
titled Date.

As a result, in my query, I added a column and typed the following next to
each catagory:

Field Name: Expr1: [DATE2]
Table: 22 since this is the name of my table
Total: Expression (i selected this from the drop down menu)
Sort: Assending
Show: I left this unchecked
Criteria: Mid([Date],5,2) & "/" & Left([Date],4)


when I click on the datasheet icon I am prompted to enter a data value for
22.date2. If I enter 1/2005 the data sheet shows only heading but not data.
What am I doing wrong.

Your help is greatly appreicated.
Ofer said:
Try this

mid(FieldName,5,2) & "/" & left(FieldName,4)
Click to expand...
Click to expand...
 
A

Access Newbie looking for help

Ofer, it worked, thank you for you help. I am going throug the Access 97
bible and am very good with Excel, however, I am being forced to send data to
access now because of Excel's 65k row limitation. Thanks again

Ofer said:
When you use the format, the filter looks for a string that valued as 01/2005
and not 1/2005.
If you want to let the user enter both 01/2005 or 1/2005 and still filter on
the date try this

Where Mid([Date],5,2) & "/" & Left([Date],4) = format( [Enter Date
here:],"mm/yyyy")

I also would recomand changing the date field name to something else, date
it a reserved name in Access.

Access Newbie looking for help said:
Ofer thank you for your help. the colum that has the year month day is
titled Date.

As a result, in my query, I added a column and typed the following next to
each catagory:

Field Name: Expr1: [DATE2]
Table: 22 since this is the name of my table
Total: Expression (i selected this from the drop down menu)
Sort: Assending
Show: I left this unchecked
Criteria: Mid([Date],5,2) & "/" & Left([Date],4)


when I click on the datasheet icon I am prompted to enter a data value for
22.date2. If I enter 1/2005 the data sheet shows only heading but not data.
What am I doing wrong.

Your help is greatly appreicated.
Ofer said:
Try this

mid(FieldName,5,2) & "/" & left(FieldName,4)

:

I imported some data that is in the Year Month Day format and want to change
it to month/year format can this be done??????
Click to expand...
Click to expand...
Click to expand...
 
Top