Access 2010 InfoPath Data Collection Export Fails Due to Date Format

[raghureddy02]

I created an Access 2010 database that has multiple data collection (InfoPath) forms that were generated from Access and have been in use for about 1.5 years. Starting in 2013 (for about a week now), the submitted data fails to Export due to a "data type conversion error" with the date fields. Prior to 2013, the date string returned in the InfoPath form looked like this: "2013-01-07T00:00:00", but now it looks like this: "2013-01-07T00:00:00-05:00". The time zone is appended to the string and it kills the Outlook Export feature.
To test this, I created a new database with one table and one date time field. I generated an InfoPath template and emailed it to myself. I entered the date into the template and submitted it (tried manually entering the date as well as using the date time picker control - made no difference). The InfoPath template now contains "2013-01-07T00:00:00-05:00" and will not Export from Outlook to Access. I tried manually pasting the string into the Access table and it would take it, but would show "1/7/2013 5:00:00 AM" in the date time field (which isn't correct either but at least it took). Note: This problem has appeared at my office (Win 7 with Office 2010), but my testing was done on my personal laptop using Win 8 with Office 2010.
It looks like Microsoft has created a bug and now all of my data collection forms are unusable. Any help will be appreciated.

[ItsMe]

It looks like the single field is returning a time range in the newer version. If there is not a way to adjust the formatting before export then you will have to retrieve the field as a string and parse it so it will be a valid date.

MM/DD/YYYY HH:MM:SS

[raghureddy02]

It looks like the single field is returning a time range in the newer version. If there is not a way to adjust the formatting before export then you will have to retrieve the field as a string and parse it so it will be a valid date.

MM/DD/YYYY HH:MM:SS

Thanks for you reply i have used CDate(Format([Anual Leave],"dd/mm/yy")) AS Expr1 to covert the text to date but while filtering by month or week on query im getting data type mismatch in the criteria expression

please help me out

[ItsMe]

You are going to have to treat the value as text until after you format it correctly. The function you are using is looking for a date, just as much as the date field in a table is looking for a date. I am not familiar with your import process. If I have the opportunity I will use a string variable and parse the while the value is held in the variable.

After it is formatted you can place it in a field of Date data type.

If you do not have the option to hold tha value in a string variable during the import process then you can import the value into a field of text data type. After the import is complete you can clean up the data by running a custom function that will update your desired Date column by parsing the fields in the Text column.

I use both approaches. Be careful with the order of the month vs. date and also if the time value is a 24hr format or requires AM/PM