Convio Date Format and MS Access (m/d/yy)

Mathew Grimm

Has anyone successfully imported/linked a Report Writer export containing dates in Access?

The Convio format of m/d/yy seems to confuse Access. I've tried every combination I can think of in the options, but no luck so far.

I would think that the options would be:

Date Order = MDY

Date Delimiter = /

Four Digit Years = Unchecked

Leading Zeros in Date = Unchecked

I suppose I can import as text and manipulate from there, but wanted to check with the community first.

Thanks,

Mathew

Mathew Grimm

This is referring to dates such as Creation Date and Opt-Out Date. I've found if you import the Convio date format as text, you can use the Access CDate function to force it into usable data. I'd still love to figure out a way to link the data in Access with it already recognizing that's a date field.

Corey Pudhorodsky

Mathew Grimm:

This is referring to dates such as Creation Date and Opt-Out Date. I've found if you import the Convio date format as text, you can use the Access CDate function to force it into usable data. I'd still love to figure out a way to link the data in Access with it already recognizing that's a date field.

The process that you are using to read the data as character data first is similar to processes that I use when importing the date field.  In fact, I will sometimes import to Excel first and then pull the data into Access to avoid that and other import errors that Access commonly generates when importing external data.

Mathew Grimm

Corey Pudhorodsky:

The process that you are using to read the data as character data first is similar to processes that I use when importing the date field.  In fact, I will sometimes import to Excel first and then pull the data into Access to avoid that and other import errors that Access commonly generates when importing external data.

Perhaps Convio could use a more widely used (standardized?) date format?

Corey Pudhorodsky

Mathew Grimm:

Perhaps Convio could use a more widely used (standardized?) date format?

Actually, even according to Microsofts documentation: http://msdn.microsoft.com/en-us/library/az4se3k1.aspx  I think our exported date format is standard format that should be accepted.  I think this may be more so a limitation in the import tool in Access. I can enter a feature request for you if you'd like.  What would be your prefered format for date/time fields or the options that you'd like us to consider?

Mathew Grimm

Corey Pudhorodsky:

Actually, even according to Microsofts documentation: http://msdn.microsoft.com/en-us/library/az4se3k1.aspx  I think our exported date format is standard format that should be accepted.  I think this may be more so a limitation in the import tool in Access. I can enter a feature request for you if you'd like.  What would be your prefered format for date/time fields or the options that you'd like us to consider?

How about this?

http://en.wikipedia.org/wiki/ISO_8601#Calendar_dates

Corey Pudhorodsky

Mathew Grimm:

How about this?

http://en.wikipedia.org/wiki/ISO_8601#Calendar_dates

Great!  I have a request entered for you.  You can track it in the future with ID number: FR-17584

Mathew Grimm

Mathew Grimm:

How about this?

http://en.wikipedia.org/wiki/ISO_8601#Calendar_dates

Thanks!