Date in Text Files

[Eljefegeneo]

Format

I am importing a field from and old DOS based data base where dates and text are mixed in the same field. The field can have either one of more dates or text. That is the possible entries in the field are:
(1) 12/12/2013
(2) 12/12/2013, 12/1/2013 or some other delimiter other than a comma
(3) 6 CD’s
When I tried to import the field, I am finding that (1) results in a string of numbers which represents the date. The others are, of course, OK.
What I am trying to figure out is if the first type occurs and I have 41640 it will appear as 01/01/2014. So, can you have mixed field types in the same field by using some custom formatting with an if (of Iif) statement and what would that be?
Something like IIF(IsDate([FieldA], format([FieldA], “short date”), “@”)
I just can’t seem to get the right nomenclature or number of correct arguments.
And yes, I know I could split it into two types of fields, but I don’t think this is possible.

 

[MarkK]

When you Format() a date, you convert it to a string. Now, any date can be a string, but only very special strings can be dates. So dates are, in sense, more valuable than strings. This being the case, this doesn't seem that useful to me . . .

IIF(IsDate([FieldA], format([FieldA], “short date”), “@”)

. . . because here you go looking for dates, and when you find one you convert it to a string, and then you mix it back in with other strings.

I would do . . .

IIf( IsDate(FieldA), FieldA, CDate(0) )

. . . so there I have a function that always returns a Date. That causes me to increase the amount of order in my system.

And yes, you can create two, or more, fields from one. Consider how I use FieldA here to always return a date in one field . . .

MyNewDateField: CDate( IIf( IsDate(FieldA), FieldA, 0 )

. . . and then for the non-date data, I expose FieldA as a string . . .

MyNewStringField: IIf( Not IsDate(FieldA), FieldA, "")

. . . so both of those output fields consume data from FieldA, and expose that data in more ordered ways than FieldA was doing originally. And you could do . . .

MyNewNumberField: CSng( IIf( IsNumeric(FieldA), FieldA, 0) )

. . . which always returns a Single.

 

[Eljefegeneo]

Thank you for your quick reply. I am now able to convert the date string to a date, but since the final field is just going to be a text field, is there way of updating the field in question that now has text and date strings to be a text field showing both text strings and also dates as dd/mm/yyyy?

 

[Royce]

There is not enough details in your post, but it sounds like you are breaking normalization rules that will make your new Access data a headache.

Split your old data into two or more new fields, where each field holds only one type of data. Strings of dates should become mulitple records in a normalized related table.
It's easy to combine fields into one field in a report or form for display. It's a constant headache to work with non-normalized data, which is what your old table is. If you've never heard of "Normalization" you need to do some research. It's absolutely essential to good database design.

 

[MarkK]

If the source field AND the output field are strings, why bother processing the field? I don't understand what value is added to the data in this process, if at the end of the process you have not improved the specificity of the data.

Why expend effort to sharpen a knife that, when you are done, is no sharper?

 

[Eljefegeneo]

Thanks to both Royce and MarkK. I do realize the error of my ways. It is far better not to combine two different types of data formats. I am going to change the fields to add one, that is, one for text and one for dates and then combine them in a report.