=DMax("SaleDate","tblOrder","CustomerID=""" & [CustomerID] & """")

Double Double Quotes

Any reason why they are Text instead of Numbers?

"CustomerID" field is text defeat the purpose!

Many thanks - I'll try that!
The reason for the text instead of numbers I thought you may shout at me for that).
I am converting a database I use to manage my small business  to Access. I wrote it many years ago in Windows 98 days using Ffenics (used to be DataEase before the company split). It works well and I have developed it a lot over the years, but fear it may well stop working soon as each new version of Windows appears.
My customer form primary key field in Ffenics is the first 4 letters of the customer's surname and then a 4 figure number, so a length of 8 in total. I had to format this as a text. The same field appears as a foreign in the orders table.
I have over 7,000 customers and many thousands of orders, so keeping the fields as text seemed much easier than starting again with numeric autonumber fields!

You can still use an auto-number primary key even if you have a good reason to use a text as primary key. Just use both. You don't ever have to worry about the auto-number or what it's value is, or even use it, but it may be able to save you some trouble in some areas.

You could also use some update queries and update all your tables to the correct customerID as an autonumber and throw out the text primary key (or change it to customertext and just use it as a normal field inside of your database for data instead of being a key).

Replace Natural Key

Thanks Adam / Kevin
I think (it's a long time ago) I went down this route initially for ease of checking if a customer was already in the database. By searching "SCHW*" I could instantly see all the "Schwanz's, then just click on the correct one to see the full details. If it wasn't already in I knew the next number in the four number part of the code was the next new number and there could be no duplicates.
I couldn't see how I could replicate that ease with a plain numeric field.
What benefits would an autonumber field give me?
Would it be better to replace it? It doesn't sound right to have two primary keys...
Thanks for the link Kevin.