Hi micang,
There are few solutions I would try. But before trying any of them, keep this in mind:
Access stores and displays dates in two totaly different ways. The short format date you mentioned, 23/04/97, can be manipulated purely by changing the default display settings on your pc (on XP: Start>>Control Panel>>Regional and language options>>customize>>date) Here you can change the short date format (or long date format) to anthing you desire. (I always change this to dd/MM/yy after XP installation cause it defaults to american date and time format of MM/dd/yy )
Here you can also add any date seperator of your choice "/" or "-" etc. This will then display any fields you have set in access to DATE and the FORMAT you have selected (short, long, medium or whatever). This is purely display settings and does not mean that your DATE value is stored in this format!
(right... onto a solution, after keeping that in mind!)
You could try, the quick and dirty
: (you'll have to test this)
Setup a 25 field table,make the 7 corrsponding fields DATE fields, the rest text.
Import your 25 column txt file into the table.
Make a form with the form wizard, and display your table in datasheet format. You can go into the Design View and click the Properties of the DATE fields and change the FORMAT to SHORT, MEDIUM or whatever you need. This might display what you need, keeping in mind your PC's Regional and language options. You'll have to fiddle, I have not tested this.
Option two, with TEXT manipulation and forcing the storage of the "/" would be to essentially "build" your own field from the imported one. A long process, but you'll get exactly what you want. The summarised steps are:
Make a 25 field table with all fields text values. Add another additioanl 7 text fields.
Make a form again with all your fields displayed.
In Design View, Change your first blank field's data properties control source to give you the desired result of first date column.
Off hand I don't know the string, but i do know it's a colletion of strings from the HELP file (press F1 and search for "examples of expressions" -never underestimate the help in Access, I use it all the time)
You'll have to take the first 2 characters of the field (In the help file this is under >>Examples of expressions used in calculated fields>>Manipulate text values. ) Here it gives an example of how to obtain the first two characters of a string.
Then you will have to add the "/" this is done by placing: & "/" (include the quotes) after the first 2 characters. And then again, you will have to get the next 2 characters, and repeat the process by adding another slash etc. Until the field is "built" exactly like you want it.
AFAIK you can only store your result as a TEXT field cause it will not be a true DATE field, cause you have just "hardcoded" the seperator, "/" into it.
The third option , which I'm not really clued up on is by using the Datepart() function. (just type that in the access help and read about some examples) This may also be a way of "telling" acccess, which part of 240497 is the day(dd), and which part is the month(MM), and which part is the year(YY). you could maybe use this inconjunction with option 2.
There might be any easier way which I am overlookng, but thats what i would try. I Hope this points you in the right direction
Sign up with Google