Selection query with MAX(textfield) return null value if the matching record more than 2501 records

[Chlaris]

Dear all,

I have a problem with max function in query.
I found the same problem with mine in
http://www.eggheadcafe.com/software/aspnet/33472096/selection-query-with-max.aspx

How to solve this ?
Thanks.

Chlaris

 

[John Spencer]

Can you post the SQL of your query that is NOT working?


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Chlaris]

I also run Dmax function in debug window and it returns null value

?DMax("InvoiceNo","tblInvoice","InvoiceNo Like '" & "INV/*/01/2009" & "'")

It will return null value if the matching records more than 2501 records.

But if I change the code to :

?DMax("InvoiceNo","tblInvoice","InvoiceNo Like '" & "*INV/*/01/2009*" & "'")

It will return the right value.

Thanks.

 

[techrat]

I am not sure what the 2501 records may have to do with your specific
problem but it appears that you may have leading blank spaces in the
field you are applying the criteria to. This would explain why adding
the asterisk in front of "INV" allows the correct number of records to
be returned.

Sometimes leading characters or spaces in a string can be hard to see
visually.

Hope this helps.

 

[Chlaris]

I have checked the database, all rows have same number of characters.
You can see the same problem which facing by the other user at
http://www.eggheadcafe.com/software/aspnet/33472096/selection-query-with-max.aspx
Thanks.

 

[John Spencer]

How about the following? Does this return records?
DMax("InvoiceNo","tblInvoice","InvoiceNo Like '" & "INV/*/01/2009*" & "'")

If so, are you querying against an SQL database? AND if you are check to see
if the field is defined as VarChar type or Char type.

VarChar fields trim trailing spaces.
Char fields are always the length of the defined field length. So if you have
a Char field defined as a length of 22 and enter "A" in the field the
remaining 21 slots are padded with spaces (or perhaps nullstring character - I
don't recall for sure which).

Also even with native Access tables, it is possible to have trailing spaces
(or other characters) if you populate the field using VBA or import from
external sources that do allow trailing spaces (Excel).

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Chlaris]

Hello,

I dont' have any record with leading blank spaces.
I can send the sample database for you to check.
Now this problem occurs again.
Thanks.

I can