max function on text field (1 Viewer)

[Pat Hartman]

There is absolutely no reason for you to import and store data that doesn't work. It is very easy to fix up this data as you import it and that solves ALL problems. You never have to worry about it again. If you don't fix the data at the source, you have to fix it EVERY SINGLE TIME you use it. Your choice.

Either make the field a date with the day part always being 1 or make a text field with yymm or yyyymm and make sure the month is zero filled so that it is always two digits.

 

[eshai]

There is absolutely no reason for you to import and store data that doesn't work. It is very easy to fix up this data as you import it and that solves ALL problems. You never have to worry about it again. If you don't fix the data at the source, you have to fix it EVERY SINGLE TIME you use it. Your choice.

Either make the field a date with the day part always being 1 or make a text field with yymm or yyyymm and make sure the month is zero filled so that it is always two digits.

I will explain more clearly. This table is a linked table in my database. The original table is replaced several times a month by the finance department. They simply delete the table and paste a new one. This is not in my hands, although I am working on a new financial system that will remove my dependence on them. Right now I need to withdraw the last payment the student paid. I have nothing to do with their mistake

 

[Pat Hartman]

OK, if you are not importing the table, I have an alternative suggestion.
BEFORE you do the following - turn OFF Name auto correct or this suggestion will not work
You can turn it back on when you're done if you are so inclined.

1. Rename the linked table to something__Linked.
2. Create a query that selects the something_Linked table and adds a calculated column to make either the date with day always = 1 or the string formatted as 202302, whichever you prefer.

That way, every place you are now using your old linked table name, you now have a calculated column with the data you need in it.

FYI, in case you don't understand why step 1 is necessary, it is because Queries and tables can be used interchangeably in most situations so you can never have a query and a table of the same name. This little trick, lets you solve the problem without changing all references to the old linked table name. You just add the new query with the new calculated columns and it will automatically be used wherever the old linked table was used. Now in the places where you need the yearandmonth, it will be available.