Make table query

[scoe]

Hi, hope someone can help.



I am creating a make query table of three columns, [ID], [Name] and [date]. To get to this stage there has been several queries so the date column is in text format. I now need to make a table but revert the [date] field back to a short date format and I'm not sure how to do this.

Any ideas would be greatfully appreciated.

Thank you.

[lfpm062010]

I would try to stay away from the access reserved word like "date". Here is a list of reserved word used in access.
http://support.microsoft.com/kb/321266/en-us

To answer your question, use "CDate" on your date. It will convert the date string back to actual date type.
SELECT [ID], [Name], CDate([date]) FROM YourTable .....

OR ("#DateString# should work as well. I have not tested this).
SELECT [ID], [Name], #[date]# FROM YourTable .....

[scoe]

Thank you CDate worked well all I have to do is get it to keep the field name as it has brought it across as Expr1002.

[lfpm062010]

In case you did not know. You can changed that "Expr1002" to anything you want.

In "design view", it should be something like "Expr1002: CDate....". You can changed to something like "NewDate: CDate....". Then the "NewDate" will be your new column name.

[scoe]

Thanks for that, I've done that before but didn't think to revert back to the design view again