DMax returning Null

[Markb384]

Hi guys and gals

I have some code that appears to have broken. Fundamentally, I want to return a date value from a table in which it is the newest date that fulfils some criteria. As far as I am aware, it looks perfectly good, yet it is returning a null value.

My table is structured as below...

Equipment ID	Survey Date	Norm Baseline	Norm Field Size	Norm Groups	Others....
ID.A	1/5/2014	No	40	12
ID.B	1/4/2014	Yes	40	11
ID.A	1/3/2014	Yes	40	12
ID.A	1/2/2014	No	40	12
ID.A	1/1/2014	Yes	40	12

and if the user selects the 1/5/2014 for ID.A, I want to return 1/3/14. If the user selects 1/2/14, 1/1/2014 is returned. I have some other code which catches if the user selects a date with Yes for Norm Baseline already, as well as forcing the user to make sure there is a Yes in the Norm Baseline column when a new ID is added for the first time.

My code is...

'create a string to determine the closest baseline date to the measurement date for that field size
            
basedateString = "[Equipment ID] = '" & equipID & "' AND [Norm Baseline] = True AND format([Survey Date],""ddmmyyyy"") < " _
                & Format(sdate, "ddmmyyyy") & " AND [norm field size] <> null"

'create a new string for DLookup criteria using that date
basedate = DMax("[Survey Date]", "[results_hutt_dig]", basedateString)

Using the Locals Window, when I choose 1/5/14, I can easily see that the basedateString is equal to

"[Equipment ID]" = 'ID.A' AND [Norm Baseline] = True AND format([Survey Date],"ddmmyyyy") < 01052014 AND [norm field size] <> null"

which looks okay to me. Yet it returns null to basedate. However, an earlier value I entered works okay, and appears to be identical in structure.

It is important to note that the Survey Date column might not be chronological and that different IDs will be littered through in any order.

Any ideas?

Thank you

[Markb384]

Just added another line to the table (2/5/14). It also debugs out, unless I convert 1/5/14 to a Yes in Norm Baseline, in which case it works perfectly. To me, this suggests that the equipment IDs need to be consecutive in the table, which is definitely far from ideal...