Date Converstion

[ChristopherL]

Hiho!
Looking for a solution for this problem for microsoft Access!



When I am importing a date value it mixes up the year/month/day value and enters the year as date and the day as the year.
Anyone know the conversation for this?

I've tried with
CorrDate: Format([REPORT_DATE];"yyyy/mm/dd") but this only gives me the same thing back..
The date that I am trying to import is 2013-07-02 but it gives me 2002-07-13 :/

[JoeM]

How are you importing it?
When you are importing it, set up the Import Wizard/Template to import the dates in YMD format.

[ChristopherL]

I am importing .txt file! When I am going through the link text wizard I can only set it as date/time and it doesnt let me decide the format.
It just enters as 02/07/13. :/

[ChristopherL]

I tried CorrDate: Format([REPORT_DATE];"dd/mm/yy")
then Date: CStr([CorrDate]) so I made it to a string, now its a string that says 13-07-02 but I believe access thinks it format is dd/mm/yy still when it should be yy/mm/dd

[ChristopherL]

How are you importing it?
When you are importing it, set up the Import Wizard/Template to import the dates in YMD format.

it looks like this.. Should be 2013-07-02

[JoeM]

In the Import Wizard, you need to click the Advanced button. Under there, there is a selection for Date Order in which you can tell it the order of the date fields coming in.

[ChristopherL]

In the Import Wizard, you need to click the Advanced button. Under there, there is a selection for Date Order in which you can tell it the order of the date fields coming in.

I have two more date columns in the .txt file, those two are however placed normally, is there a way that I can do that seperately for just the report date?

[JoeM]

No, the date format applies to ALL date fields in your file.
If you have one date format that is different than all the rest, your best bet may be to import that field as a Text field. Then you can use String and Date fields to convert it to a date after import, i.e.
DATESERIAL(LEFT([DateField],4),MID([DateField],6,2),MID([DateField],9,2))

[ChristopherL]

No, the date format applies to ALL date fields in your file.
If you have one date format that is different than all the rest, your best bet may be to import that field as a Text field. Then you can use String and Date fields to convert it to a date after import, i.e.
DATESERIAL(LEFT([DateField],4),MID([DateField],6,2),MID([DateField],9,2))

Worked perfectly! Thank you for your patience and knowledge!
Do you mind if I ask another question?
How do I pick out the latest date and only those in a select without actually doing the certain date as a criteria?
I'm trying to just go total-> max but that doesnt do much :/

[JoeM]

Create an Aggregate Query that pulls out the Max Date. Do this by only adding the Date Field to the query, clicking on the Totals button, and changing the Totals line from "Group By" to "Max".
Now, create a new query joining your original table and this Aggregate Query together on the Date Field.