min max fuctions on a date field returning unexpected results

[JeffChr]

I am trying to return the min date value from one field and the max from another using the following SQL

SELECT tIHS.[VP: Production Brand], tIHS.[VP: Global Nameplate], tIHS.[T: Program], Min(tIHS.[VP: SOP (Start of Production)]) AS [MinOfVP: SOP (Start of Production)], Max(tIHS.[VP: EOP (End of Production)]) AS [MaxOfVP: EOP (End of Production)]
FROM tIHS


GROUP BY tIHS.[VP: Production Brand], tIHS.[VP: Global Nameplate], tIHS.[T: Program];

SOP and EOP are date fields. I am not sure what is being returned but it's not min and max. I think it is just returning data from the first row of data rather than looking at all rows for the min and max. There are 3 qualifying rows.

Does min and max not work with dates ? Thank you for your feedback!

[JoeM]

Are your dates stored as a Date data type in your underlying table, or as Text?

[ItsMe]

Are you trying to collect user input as a parameter? I believe this will be interpreted as text and, therefore, will not be sorted as a date.

Perhaps there is another approach that can be taken. It will be difficult to validate the text as a valid syntax within a query. You can cast text strings to a date though. Maybe use the CDate() function.
https://msdn.microsoft.com/en-us/lib...ectionSection3

[JeffChr]

Thanks. I figured it out. Human error. The db was working fine. (One of those days!)

[orange]

Did you want to share your solution with readers?

[JeffChr]

Sorry, I thought I did. There was nothing to fix. I was reading the results incorrectly. Human error, ie; Me.

[orange]

Thanks for clarifying. I thought you may have had to "tweak" something.