Date Format

[phong919]

Hello

Can someone help format the date correctly? I'm currently linking a flat file in access and then creating a table afterwards. The date comes in the following format below:

i.e. 30102006 - txt type

I wanted the date to look like - mm/dd/yy. Thanks for the help.

 

[rahulgty]

use
yourtext=Format(yourtext,"dd/mm/yy")

Rahul

 

[phong919]

Can i write that in my query?

 

[The_Doc_Man]

Any Access function found in the Access Help pages under VBA functions can also be used in a query provided you can give it the right arguments.

 

[gemma-the-husky]

you probably need a field defined as a date type

and then set it as

=datevalue(left(mystring2) & "/" & mid(mystring,3,2) & "/" & mid(mystring,5,4))

you need to get a string that looks like a date nad then turn it into a date with datevalue ie datevalue(mydatestring)

to get the datestring you need to insert slashes to turn 30102006 into 30/10/2006.

If your excel sheet is treating these as numbers, you might have a bit more mainpulation to do, as eg 7/10/2006 will be stored as 7102006, not 07102006, and the string slicing will not work correctly.

 

[rahulgty]

"If your excel sheet is treating these as numbers, you might have a bit more mainpulation to do, as eg 7/10/2006 will be stored as 7102006, not 07102006, and the string slicing will not work correctly."

to solve this problem u can try:

iif(len(mystring)=8,(left(mystring2) & "/" & mid(mystring,3,2) & "/" & mid(mystring,5,4)),"0" &(left(mystring2) & "/" & mid(mystring,3,2) & "/" & mid(mystring,5,4)))

 

[phong919]

This file is not from excel, it's coming from a flat pipe delimited file. Thanks for the replies. Let me try and see if it works.