Question about Max expression

[mitchem1]

I am using the following expression to retrieve the latest year in a column of dates: Max(Right([EnteredDate],4))

This worked fine till we rolled over to the new year. The last two dates in the column are 12/23/2009 and 1/05/2010. I want the expression to return 2010 but I guess it still returns 2009 as it views 12/23/2009 as the Max value. Any ideas how I can fix this would be greatly appreciated. Thank you.

 

[ajetrumpet]

well you could always use DMAX, and then extract from there. maybe like this:

right(DMax("EnteredDate", "table"), 4)

 

[Brianwarnock]

Why are not just using Max([enteredDate])?
I am assuming that it is a Date field?

Brian

 

[mitchem1]

Thanks Aje, worked like a charm.

 

[Galaxiom]

In a query, the use of DMax is not the best option. Domain functions require a lot more processing than using the SQL function Max. Also Max will work queried directly into SQL Server. Domain functions are Access only.

If you just want the year of the most recent date then use:
Year(Max([entered date])

BTW.
Right, Left, Mid and Format functions all return strings.
Max on a string is the alpha order not the numerical order so values 1, 2, 11 will sort as 1, 11, 2 whereas function such as Day, Month or Year return a number which would sort numerically.

However it does not explain what was going on in your case since it still should have returned 2010.

 

[gemma-the-husky]

but the expression

Max(Right([EnteredDate],4))

is curious

a date is not a string, its a long, isnt it? so does this imply the OP is storing the data as text - or does it access implicitly type convert in a case like this.

I havent checked - just seems curious

 

[Brianwarnock]

Glaxiom and Dave
The poster didn't answer my question at post 3 so I guess he isn't going to respond again.

Brian

 

[Galaxiom]

but the expression

Max(Right([EnteredDate],4))

is curious

a date is not a string, its a long, isnt it? so does this imply the OP is storing the data as text - or does it access implicitly type convert in a case like this.

Essentially a date field is stored as a Long while being displayed as a date format. The string manipulation expressions do an implicit conversion of the displayed value to a string. Number fields are also implicitly converted to strings by these functions.

Arithmetic operators applied to sets of strings that are valid numbers will also perform an implicit conversion.

 

[gemma-the-husky]

Essentially a date field is stored as a Long while being displayed as a date format. The string manipulation expressions do an implicit conversion of the displayed value to a string. Number fields are also implicitly converted to strings by these functions.

Arithmetic operators applied to sets of strings that are valid numbers will also perform an implicit conversion.

aaah - useful to know - i tend not to assume (or even know) stuff like this, and i think i would have explicitly typed a date into a string variable first to do something like that.

vba has surprised me before though in similar things - i think to do with managing openargs, or referring to missing optional parameters - things like that

 

[Galaxiom]

i tend not to assume stuff like this,

Some developers recommend not letting Access take action based on assumptions and always use the type conversion functions to ensure the result. Much like defining variable types in VBA rather than relying on the Variant.

Like much automation in Access these features are useful if you are aware of them but unexpected errors can happen in odd places when you don't anticipate them.

For example. An input intended for a number could have an error with a letter "O" substituted for a zero. Relying on the automatic string to number conversion in an arithmetic expression will turn an arithmetic "+" into a concatenation, potentially resulting in a string that still masquerades as a number but is entirely wrong.

Of course a well designed input should have picked it up but it illustrates the kind of thing that can happen.

One to watch out for is the automatic date format recognition in the Month and Day functions. Despite the system date format, Access will silently accept a date that is invalid in the system format but valid in an alternative date format.