Access Table: Multiple data types in one field

[Mark44stfc]

Hello All,

I am in the process of converting excel files that have become too large into Access. I have stumbled across an issue when bringing one of my data tables into access.

Within the Excel I have a column that could either be a date or if there is no date is '#' which is text format.

I can run a macro to remove the '#' fields before importing into Access, however, I was wondering if there was a way to have a field in an Access table that could contain both Numbers & Text?

 

[Joe4]

I can run a macro to remove the '#' fields before importing into Access, however, I was wondering if there was a way to have a field in an Access table that could contain both Numbers & Text?

Each field can have only one format. "Text" fields can accept pretty much anything, but then bear in mind that all the dates will be treated as text and not dates, so you would need to convert them if you need to do any sort of date calculations or comparisons on them.

 

[Mark44stfc]

Each field can have only one format. "Text" fields can accept pretty much anything, but then bear in mind that all the dates will be treated as text and not dates, so you would need to convert them if you need to do any sort of date calculations or comparisons on them.

Thanks Joe......

I was hoping that would not be the case but will put it in as text then use a query to convert

 

[Joe4]

I was hoping that would not be the case but will put it in as text then use a query to convert

What you really have is a data/design issue. A well-designed database should never have mixed values like that in a single field.
Having well-defined data types in a database is actually a good thing. It helps protect the integrity of the database, and helps to keep invalid data out of it.

 

[Mark44stfc]

Query #Error - Text to date

Hello All,

I have a table which contains some text based fields.

One of the text based fields has either '#' or a Date. I am using the function CDate to convert back to a date format.

This works wonders for the cells where there is a date, however, where '#' appeared there is now '#Error'.


What is the best way to still show '#', I would assume using an iif statement but I cannot seem to get it right

Any help would be greatly appreciated

 

[Joe4]

Re: Query #Error - Text to date

I merged your two threads together. Since this is really the same issue, let's keep it all in one thread.

Maybe something like this:

IIf([MyField]="#",Null,CDate([MyField]))

 

[HiTechCoach]

Re: Query #Error - Text to date

When importing data for a conversion I always import the data to a work/temp table. This allows me to have a chance to data clean up and validate the data. Once the data is ready and validated will I do the final append to the production table. I NEVER import directly to the production table.

In your case I would import the the date column as a text data type. I would them add a new date column with the date/time data type. Next run an update query to copy the date to the new data column only for the records that have a valid date. This new date column will be used when appending the record to the final production table.