My Google-Fu is weak and I've yet to find something that answers this one.
This has a lot of back story, you're really a saint if you readl through all this, but want to provide as much info as possible:
The summary is, I've got a monster MSAccess query, that had been working until recently (bear with me
)
IIf(Nz([Record_Table].[Record_OnGoing]),"False",IIf(Nz([Record_Table].[Record_DestroyedDate],"")>"","False",IIf(Eval(Nz([Retention_Table.Retention_Qualifier]) Not In ("ACT","CREATE","CY")),"False",IIf(Eval(Nz([Retention_Table.Retention_Qualifier])="ACT" And Nz([Record_Table.Record_AIM])=1),"False",IIf(Eval(Nz([Retention_Table.Retention_DestructionMonths])>0 And (DateAdd("m",Nz([Retention_Table].[Retention_DestructionMonths]),IIf(Nz([Retention_Table].[Retention_Qualifier])="CY",DateValue("1/1/"+Year(Nz([Record_Table].[Record_StartDate]))),IIf(Eval(Nz([Retention_Table].[Retention_Qualifier])="ACT" And Nz([Record_Table].[Record_AIM])=2),Nz([Record_Table].[Record_EndDate],[Record_Table].[Record_StartDate]),IIf(Nz([Retention_Table].[Retention_Qualifier])="CREATE",Nz([Record_Table].[Record_EndDate],[Record_Table].[Record_StartDate]),[Record_Table].[Record_StartDate])))))>Now()),"False","True")))))
You're still here? AWESOME.
Okay, I've isolated the Data Type Mismatch to this line:
DateValue("1/1/"+Year(Nz([Record_Table].[Record_StartDate])))
If I isolate this, I get an #error all the way through the query.
If I pull [Record_Table].[Record_StartDate] into Excel, all the fields check as dates (between 1/1/1930 - today), with no blanks.
DateValue("1/1/"+2000)
returns 1/1/2000
Year(NZ([Record_Table].[Record_StartDate])) returns the proper year!
If I put Year(NZ([Record_Table].[Record_StartDate])) in as its own expression, and then add it to the DateValue command like, DateValue("1/1/"+ expr1) I get an #error.
I'm at a total loss here, but I'm pretty sure this is the line causing my datatype mismatch. Solutions to this will get you mega Karma!
This has a lot of back story, you're really a saint if you readl through all this, but want to provide as much info as possible:
The summary is, I've got a monster MSAccess query, that had been working until recently (bear with me
)IIf(Nz([Record_Table].[Record_OnGoing]),"False",IIf(Nz([Record_Table].[Record_DestroyedDate],"")>"","False",IIf(Eval(Nz([Retention_Table.Retention_Qualifier]) Not In ("ACT","CREATE","CY")),"False",IIf(Eval(Nz([Retention_Table.Retention_Qualifier])="ACT" And Nz([Record_Table.Record_AIM])=1),"False",IIf(Eval(Nz([Retention_Table.Retention_DestructionMonths])>0 And (DateAdd("m",Nz([Retention_Table].[Retention_DestructionMonths]),IIf(Nz([Retention_Table].[Retention_Qualifier])="CY",DateValue("1/1/"+Year(Nz([Record_Table].[Record_StartDate]))),IIf(Eval(Nz([Retention_Table].[Retention_Qualifier])="ACT" And Nz([Record_Table].[Record_AIM])=2),Nz([Record_Table].[Record_EndDate],[Record_Table].[Record_StartDate]),IIf(Nz([Retention_Table].[Retention_Qualifier])="CREATE",Nz([Record_Table].[Record_EndDate],[Record_Table].[Record_StartDate]),[Record_Table].[Record_StartDate])))))>Now()),"False","True")))))
You're still here? AWESOME.
Okay, I've isolated the Data Type Mismatch to this line:
DateValue("1/1/"+Year(Nz([Record_Table].[Record_StartDate])))
If I isolate this, I get an #error all the way through the query.
If I pull [Record_Table].[Record_StartDate] into Excel, all the fields check as dates (between 1/1/1930 - today), with no blanks.
DateValue("1/1/"+2000)
returns 1/1/2000
Year(NZ([Record_Table].[Record_StartDate])) returns the proper year!
If I put Year(NZ([Record_Table].[Record_StartDate])) in as its own expression, and then add it to the DateValue command like, DateValue("1/1/"+ expr1) I get an #error.
I'm at a total loss here, but I'm pretty sure this is the line causing my datatype mismatch. Solutions to this will get you mega Karma!