Date value with a Text suffix

[David Ball]

Hi,
I have a Date field that shows forecast dates. In my organisation, and throughout the world of construction, people are used to seeing an actual date (the task has already been completed) with an “A” suffix (e.g. 25-Feb-2015 A).
My problem is that I need “real” dates when the dates are not yet actuals, so that I can use them in calcs, and I need the dates with suffixes, for actuals, in the same column.
I am importing the real dates as Date datatype and the actuals as Text. I want to combine them into one column with a query but then run up against the problem of having a Text field reject the dates or a Date field reject the text.
Is there any way in Access to show both these in the same field?

Thanks very much

Dave

 

[pbaldy]

I would store the date and text separately and concatenate for presentation to the user.

 

[David Ball]

Thanks pbaldy.

I have set up the "real" dates in one field of a query and the text dates in another. I have used the formula below to bring them into the same column for presentation:

Display: IIf([TextDates] Is Not Null,[TextDates],[Dates])

It all works great except that I need my "real" dates in 13-Sep-15 format and in the new column they come out in 13/09/2015 format. And I can't change the format in the property sheet.

Can I modify my formula to get the dates in Medium format?

Thanks very much

Dave

 

[David Ball]

Found it! I used "Medium Date" in my formula. Works great.

Display: IIf([TextDates] Is Not Null,[TextDates],Format([Dates],"Medium Date"))

 

[pbaldy]

Glad you got it sorted, though I was thinking differently.