Max date from a concatenated field

[lmcc007]

I separated my date field into three number fields for Month, Day, and Year. My query no longer works that I used to get the Max date for EventDate when it was one field.

Below is the query:

SELECT Event.CompanyID, Max(Event.EventDate) AS [End Date]
FROM Event
GROUP BY Event.CompanyID;
​

How do I get it to work now?

 

[John Big Booty]

Can I ask why you are storing your date in three separate fields, rather than a single field

 

[lmcc007]

Can I ask why you are storing your date in three separate fields, rather than a single field

Sometimes the dates are incomplete--i.e., 10/2010 or 2010.

 

[John Big Booty]

Are these historic dates or current dates that are incomplete? If current dates why are only partial dates being recorded?

 

[lmcc007]

Are these historic dates or current dates that are incomplete? If current dates why are only partial dates being recorded?

historic dates

 

[Galaxiom]

Reassemble the dates from the three fields using DateSerial function.

You will have to deal with the nulls.

 

[lmcc007]

Reassemble the dates from the three fields using DateSerial function.

You will have to deal with the nulls.

That's the first thing I did, but received error message:

Data type mismatch in criteria expression.​

I assumed bececause it's a calculated field.


When the field is empty I get: Error#


How shall I handle that?

 

[John Big Booty]

In that case, I would consider giving those incomplete dates a nominal value of say Jan-1-Year (where year is the known partial year), and store your dates as a single field. What you are currently doing is really not within the bounds of good Normalisation. You can always break apart a date using the DatePart() function, but as you are finding it is much harder to reverse the process.

 

[lmcc007]

In that case, I would consider giving those incomplete dates a nominal value of say Jan-1-Year (where year is the known partial year), and store your dates as a single field. What you are currently doing is really not within the bounds of good Normalisation. You can always break apart a date using the DatePart() function, but as you are finding it is much harder to reverse the process.

I was doing that--that is, 1/1/2010--but he does not want that. What's written must be verbatim.

 

[Galaxiom]

In that case, I would consider giving those incomplete dates a nominal value of say Jan-1-Year (where year is the known partial year), and store your dates as a single field. What you are currently doing is really not within the bounds of good Normalisation.

I disagree. Normalization doesn't mean fabricating data by making incomplete records the same as otherwise valid data. Using Jan1 means that an accurate Jan1 record is imposible since it is designated as representing incomplete data.

It is not hard to make a date from the parts. The DateSerial function simply requires integers as arguments.

Make sure the fields in the table are integer datatype.

 

[John Big Booty]

I disagree. Normalization doesn't mean fabricating data by making incomplete records the same as otherwise valid data. Using Jan1 means that an accurate Jan1 record is imposible since it is designated as representing incomplete data.

It is not hard to make a date from the parts. The DateSerial function simply requires integers as arguments.

Make sure the fields in the table are integer datatype.

That's a fair point. However Jan 1 was simply an example. Given that we are dealing with historic data, you would choose a date that did not represent real data, so could be easily identified as representing an incomplete date, and then could be displayed as such using DatePart().

So I see this;

I was doing that--that is, 1/1/2010--but he does not want that. What's written must be verbatim.

As a display issue rather than a storage issue given that the user should not be interacting directly with the data at table level.

 

[Galaxiom]

As a display issue rather than a storage issue given that the user should not be interacting directly with the data at table level.

No, it is a storage issue since the Date datatype cannot hold an undefined month or day part.

However since the incomplete data is historical and new records are complete I would use a date field to store those dates. There are a number of ways to deal with recording incomplete values.

Use the time section of the date datatype to indicate the value has an unknown component. It has no effect on the date component.
eg 6:00 AM for unknown day and 6:00 PM for unknown month.

Be careful to choose time values that provide integral fractions in the time component of the date value or test them with plenty of margin for rounding errors using greater than or less then. Many time values do not work well with the equals operator.

If the time also needs to be used, an out of scope dates is an alternative. Access can hold dates well into the past and future. By subtracting 1000 years you could designate M/1/994 as indicating an unknown date in January 1994. Adding 1000 years could designate an unknown date in 2002 could be represented by 3002.

However these techniques mean processing every date value to check the range. An alternative is to hold the incomplete historical values in a different field. These records would have a null value in the date field. In a query Acess can very quicly detect the null and get the information from the other field with an IIF or NZ.

If it was just the day part that was unknown I would use a date datatype and enter the M/1/YYYY. In this column its meaning is understood as unknown day. Having unknown months too is a bit messy but you could use the out of range date technique here.

Another way is much like dates are stored in Access. Zero day is 12/30/1899.

Store an integer that represents the number of months since some convenient date before the start of data. It can be easily parsed to retreive the incomplete date. Use negative numbers to indicate just the year when month is unknown.

Implement the entire processing as a function that outputs the string with the question marks you require.

Years down the track when the historical data becomes irrelevant, the extra field, the null test and the function can be easily dropped as vestigial features.