DMax Question

[Melissa]

I am trying to find the maximum date using two criteria and I keep getting a
type mismatch. Here is the code...

x = DMax("DateChange", "tblState", "[State] = '" & strState & "'" And
"[DateChange] <= #" & Format(StartDate, "mm\/dd\/yyyy") & "#")

I declared x as a date and DateChange is set to be a date. The expressions
work seperately but not when I put the AND in. Any suggestions?

 

[Rick Brandt]

I am trying to find the maximum date using two criteria and I keep getting a
type mismatch. Here is the code...

x = DMax("DateChange", "tblState", "[State] = '" & strState & "'" And
"[DateChange] <= #" & Format(StartDate, "mm\/dd\/yyyy") & "#")

I declared x as a date and DateChange is set to be a date. The expressions
work seperately but not when I put the AND in. Any suggestions?

The word "AND" needs to be inside your quotes, not outside as you have it.

x = DMax("DateChange", "tblState", "[State] = '" & strState & "' And
[DateChange] <= #" & Format(StartDate, "mm\/dd\/yyyy") & "#")

 

[Andy Hull]

Hi Melissa

It looks like the AND in the criteria is outside of the string and it needs
to be inside.

Try removing the " before and after the AND

so you have...

x = DMax("DateChange", "tblState", "[State] = '" & strState & "' And
[DateChange] <= #" & Format(StartDate, "mm\/dd\/yyyy") & "#")

HTH

Andy Hull