Why can't I link these date fields in a query?

[tangcla]

I'm having difficulty linking these two tables' date fields in the query qryDivVolShiftCount - could someone please tell me what I'm doing wrong?

 

[kevlray]

In looking at the tables and queries. It appears that Access is defaulting the Month field (derived from a formula) as a text since the formula IIf([Delv Date1] Is Null,"",DateSerial(Year([Delv Date1]),Month([Delv Date1]),1)) will return a empty string if [Delv Date1] is Null. I think you will need to return a date on both sides of the iif; I tried changing the formula to this:

IIf([Delv Date1] Is Null,DateSerial(1900,1,1),DateSerial(Year([Delv Date1]),Month([Delv Date1]),1))

and it appears to work. I do not know if the results will work for you.

 

[tangcla]

Thanks, seems to have worked!