#Error while trying to create DateValue in Access

Jan 16, 2013
Reaction score
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:


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.


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! :)


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question