Delete Query removing Smallest values

[MTSPEER]

DELETE *
FROM TRR


WHERE TRR.TransactionDate Not In (SELECT DMax("TransactionDate","TRR","HICN='" & [HICN] & "'") AS MaxOfField2
FROM TRR);

[JoeM]

Hmmm...

Nothing stands out there. You don't have any blanks, nulls, or odd characters in the HICN field, do you?

I know that you said that it is sensitive data you cannot post. Are you are able to recreate the situation with the simple example we were working on?
If not, I am afraid that there isn't much more I can do without seeing the database and/or data.

[MTSPEER]

I ran the trim function on both fields to make sure there are no spaces. But an example is like this:

Field1 Field2
6851851D 20170508
6851851D 20170125

[JoeM]

I entered those two records into the database, and ran the query code we came up with in post #7, and it correctly deleted the record with the 20170125 value.
Does it not do that for you, if you try it in our example database?

[MTSPEER]

Yea I have no idea why. My mind is blown.I don't understand. I changed the field to be a number field also.

[JoeM]

I suspect some sort of data issue, but cannot really say without seeing the actual database.

[MTSPEER]

Should I change it from being a long integer? But im sorry you have helped so much. Thank you

[JoeM]

Sure. Maybe try Single or Double.

If that does not help it, what you might want to try doing is to export the Table and Query to a new database, and then delete all the data from the Table. Then try to set-up some records which resemble the format of your actual data, but don't give anything anyway. If that issue still exists when you try the query, then you can upload this "dummy data" database here for us to take a look at.

I cannot download it from my current location (corporate security policies), but can take a look at it tonight when I am home.