max function on text field (1 Viewer)

[eshai]

Hi
I have a problem I am trying to filter a query using the MAX function I need to pull results of the latest month and year in the records the year field is the number the month field is text (problem I know) in months 10 11 12 I get one record of each year in all the other months I Gets full results. The main data sheet consists of several queries. How do you solve it?

 

[MajP]

Change Max([TheMonth]) to
Max (val([TheMonth]))

val will convert the string to a numeric

 

[eshai]

Change Max([TheMonth]) to
Max (val([TheMonth]))

val will convert the string to a numeric

Thanks I get a mismatch error where do I put the expression in the query

 

[MajP]

What is your current query?

 

[eshai]

What is your current query?

A simple query is built from five fields with grouping by

 

[Gasman]

A simple query is built from five fields with grouping by

No
Show the code and state what type the fields are, numeric, string etc

 

[ebs17]

the month field is text (problem I know)

What is stopping you from solving the known problem?
If you calculate the month from a date:

Format(MyDate, "m") => Month(MyDate)

If the value comes from a table field, change the data type there to number.

 

[eshai]

No
Show the code and state what type the fields are, numeric, string etc

there is no code just a simple query with text field for the months and i can't change the field to number

 

[ebs17]

i can't change the field to number

Then wash the head of the person who gives you such a thing.

If simple things like this cause a problem, then find another hobby.

 

[eshai]

Then wash the head of the person who gives you such a thing.

If simple things like this cause a problem, then find another hobby.

what? Are you aware of what you are answering? The table is a linked table on a server on another database that I have no control over. I am only linked to the table and only now that I was asked for additions to my software I notice it

 

[Pat Hartman]

Using just the max month doesn't work once you have multiple years of data. You need to use max YearMonth.

 

[eshai]

Using just the max month doesn't work once you have multiple years of data. You need to use max YearMonth.

its work fine from 1-9 but 10-12 are read as different values

 

[Pat Hartman]

As I said, month alone never works. And if your month is a string, it needs to have a leading 0 so it is not 1-9 it is 01-09

Your code will break in January unless you include year. Because 12 is greater than 01. Therefore you need 2401 to make it greater than 2312.

 

[ebs17]

The table is a linked table on a server on another database that I have no control over

It's not uncommon for others to make errors too. You can point this out to them.

If it's just the data type error, you can correct it via short official channels:

? CLng("12")
12

 

[eshai]

As I said, month alone never works. And if your month is a string, it needs to have a leading 0 so it is not 1-9 it is 01-09

Your code will break in January unless you include year. Because 12 is greater than 01. Therefore you need 2401 to make it greater than 2312.

They set the field as a short text and set the months 1-12 in it. This information comes from the bank to another database. I just pull the data from the table. Group by MAX works from 1-9. What value does he see in 10-12? Which means I have to write code that makes the digits 10-12 bigger than 1-9. That is, 10 can be 9.1, etc. and then in the form write a code that will change 9.1 to 10 back? This query is one of 6 queries that have a relationship and are opened in a form

 

[Gasman]

Which means I have to write code that makes the digits 10-12 bigger than 1-9. That is, 10 can be 9.1, etc. and then in the form write a code that will change 9.1 to 10 back

Absolute nonsense.
If you make the values numeric, they will behave correctly. So just convert the text to numeric as another field and use that instead.

 

[MajP]

Group by MAX works from 1-9

As everyone has stated several times, simply convert that field to a numeric and instead of Max(SomeTextField) take the max of the converted field.

Max(clng([SomeTextField]))
Max(val([SomeTextField]))
Max(dateSerial([YearField],clng([SomeTextField]),1)

To do this in the designer. Simply create a new field
ConvertedMoth:clng([SomeTextField])

Now apply the aggregate and take the max of the converted field.

 

[Josef P.]

[ Max(val([TheMonth])) ]

Thanks I get a mismatch error where do I put the expression in the query

Are there data records that have Null in the month field?
=> Max(iif(TheMonth IS NULL, Null, Cint(TheMonth)))

or another way so that it remains a string (for whatever reason):
Max(right('0' + TheMonth, 2))

 

[Pat Hartman]

This information comes from the bank to another database.

FIX the problem when you import the data!!!!!!!!!!!!!!!!

Gasman, unless you use two separate fields, which gets awkward, the field needs to be a string so it can be concatenated with year as in --- 2401 or 2312. That makes next January greater than December.

@eshai You completely misunderstood my explanation. You don't change the two digit numbers, you don't make artificial values in the single digit numbers, you simply add a leading zero to the single digits. 09 is < 10. but 9 is > 10. WHY? Because strings, which is what you have, are compared left to right, character by character. So 9 is > 1, no further comparison is necessary. When you compare numbers, the comparison is done by magnitude. Logically, the numeric values are aligned at the decimal point and padded left and right with zeros to make the fields equal in length. Then a string compare would work. So, you have 4332.4 and 93.56. If you use a string compare without the zero fill. 4 is < 9 and so 93.56 is greater than 4332.4. However, if you pad the numbers with zeros. you and up with 432240 and 009356. Now 4 is> 0 so the larger number is correctly identified.

OR convert to an actual date using the first day of the month as MajP suggested.

 

[eshai]

FIX the problem when you import the data!!!!!!!!!!!!!!!!

Gasman, unless you use two separate fields, which gets awkward, the field needs to be a string so it can be concatenated with year as in --- 2401 or 2312. That makes next January greater than December.

@eshai You completely misunderstood my explanation. You don't change the two digit numbers, you don't make artificial values in the single digit numbers, you simply add a leading zero to the single digits. 09 is < 10. but 9 is > 10. WHY? Because strings, which is what you have, are compared left to right, character by character. So 9 is > 1, no further comparison is necessary. When you compare numbers, the comparison is done by magnitude. Logically, the numeric values are aligned at the decimal point and padded left and right with zeros to make the fields equal in length. Then a string compare would work. So, you have 4332.4 and 93.56. If you use a string compare without the zero fill. 4 is < 9 and so 93.56 is greater than 4332.4. However, if you pad the numbers with zeros. you and up with 432240 and 009356. Now 4 is> 0 so the larger number is correctly identified.

OR convert to an actual date using the first day of the month as MajP suggested.

You described it as clearly as possible, this is exactly my problem that 10 is called 01 and is therefore smaller than 9. I can't change the field, but I understood from your words what I can try to do. Thanks, I'll try it later