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

J

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
 
C

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.
 
T

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.
 
J

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
 
C

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Selection query with MAX(textfield) and WHERE textfield LIKE 'mask 5
return fields associated with max value record 9
Searching a recordset to find the first record with a NULL value in acertain field 7
Criteria to return all records if selection from form is null 3
How to make unbound combo box select all if related query record is null 1
Retrieve only the record with the max value within more record withthe same FK 1
selecting the right 'MAX' record 3
Find Max Values and Return Null Value Too 1

Top