Expression Incorrect or Too Complex Err-3071

[ckirmser]

Question for anyone out there...

I have a query that has been working like a champ for several months. But, suddenly last week, it stopped, giving me an Incorrect or Too Complex error (#3071).

I've narrowed it down to one field that is causing the problem; a formula that takes a date, breaks it down into its component DMY values, adds 1 to the year and then puts it back to a date.

The error comes up whether I run this expression as part of a larger query or all by itself.

Funny thing is, though - aside from the fact that it was working just fine up until about Tuesday of last week - I have the exact same expression in another field in the same query that works fine, the only difference is that it adds 2 to the year, rahter than 1.

Here's the code that works;

2yr_svcpaydate: CDate(format(Month([Active Personnel]![svcpaydate]),"0")+"/"+format(Day([Active Personnel]![svcpaydate]),"0")+"/"+format(Year([Active Personnel]![svcpaydate])+2,"0"))

Here's the code that fails;

1yr_adjpromdate: CDate(format(Month([Active Personnel]![adjpromodate]),"0")+"/"+format(day([Active Personnel]![adjpromodate]),"0")+"/"+format(year([Active Personnel]![adjpromodate])+1,"0"))

The only difference - aside from the label - is the failed code adds 1 to the year and the working code adds 2.

Now, if I leave off the CDate() part, both work fine. But, I'm left with a string result, rather than a date. I might be able to live with that, but I can see no reason why this function worked prior to last Tuesday and then suddenly went belly-up.

I've tried reinstalling Access, but no dice.

Any ideas?

 

[boblarson]

Why break it down and add one to the year like that. Just use DateAdd:

DateAdd("yyyy", 1, [YourDateField])

Much simpler.

 

[wazz]

did you know you can add a year like this?:
dateadd("yyyy",1,date())

replace date() with your own date.

add 2 years with
dateadd("yyyy",2,date())

hth.

 

[ckirmser]

Why break it down and add one to the year like that. Just use DateAdd:

DateAdd("yyyy", 1, [YourDateField])

Much simpler.

Well, sure, fine, be that way...

Never thought of that, so I'll try it.

But, the problem I'm having might be indicative of something more fundamental going wrong. I shouldn't be getting that error, yet I am. It makes me wonder, what other functions might go south...

 

[boblarson]

Well, sure, fine, be that way...

Never thought of that, so I'll try it.

But, the problem I'm having might be indicative of something more fundamental going wrong. I shouldn't be getting that error, yet I am. It makes me wonder, what other functions might go south...

I wouldn't worry too much though. If you use the RIGHT functions instead of long, drawn out ones, then it is likely going to help.

 

[ckirmser]

Danke, boblarson and wazz. That'll make my query less complex, but it wasn't too complex for the system until last Tuesday. D'ya have any ideas on why it suddenly stopped working?

 

[boblarson]

Another function you may want to look into for future use is the

DateSerial ( Year, Month, Day)

function

So you can do things like

DateSerial(Year(Date())+1, Month(Date()), 1)

which would, if today was August 8, 2009, return August 1, 2010. (just used as example)

 

[boblarson]

Danke, boblarson and wazz. That'll make my query less complex, but it wasn't too complex for the system until last Tuesday. D'ya have any ideas on why it suddenly stopped working?

Hard to say, but it could have to do with the data - something in the data.

 

[ckirmser]

Well, the DateAdd works wonderfully. Of course, I knew about it all along - yeah, that's th' ticket...

I thought it might be the data, so I ran the query using a dataset that worked previously. It still failed. But, I figure, if it's bad data, it'd fail the DateAdd function, too, and it didn't.

Oh well, I'll run with this and wait to see if anything else happens.

Yeah, I knew that all the time...

 

[boblarson]

CDate is not the best to use most of the time. It can fail miserably where DateSerial, or another function will work. Just something to be aware of.

 

[ckirmser]

CDate is not the best to use most of the time. It can fail miserably where DateSerial, or another function will work. Just something to be aware of.

Thanx!

Maybe it was just some fluke of CDate. Maybe the current dataset had just the right combination of values, number of records, whatever else, to cause it to flop in that instance.

 

[cjhughson]

I don't think it's a fluke. I've been researching this error too (I'm experiencing it right now), and I see similar stories going back to 2004 or earlier... but no definitive answers as to what caused the problem. I've been using Access since version 2 (1995), but this is my first DB using Access 2007 and the first time I've seen this error where it isn't some simple syntax or referencing error (at least not that I can see).

I have a report with a subreport that adds up totals (Sum and Choose functions, no dates) which are referenced in further calculations on the main report. The report worked for a few weeks, now it doesn't. The subreport does't show in the print preview, and the calculated fields on the main report show #Error. I am the only person in the company that can change anything (everyone else uses the runtime version), and nothing has changed.

Examples of the calcs in the Report footer of the subreport (name =control source)
tbCreditRequested =Sum([Qty]*[UnitPrice])
tbCreditApproved =Sum([Qty]*Choose([CreditApproval],[UnitPrice],0,0,0,[UnitPrice]))
tbPercentDenied =IIf([tbCreditRequested]>0,1-([tbCreditApproved]/[tbCreditRequested]),0)

Not very complex, right? No problem with the syntax, right? The data types are correct. Is there any issue with using values calculated in other controls by referencing the other control's name?

I really don't see where the problem is. I don't think the expressions could be any less complex. Any help would be appreciated.

Thanks,
Chris

 

[boblarson]

Looks like you could be getting hit by NULL fields somehow. You might want to encapsulate your fields with Nz([FieldName],0) on all of the calculations (remembering to do it on the field BEFORE the aggregate function or another function like adding or subtracting).

 

[cjhughson]

Good suggestion. Tried it. Didn't work. I do think the problem is in the data now though (or how I handle it). I tested a few more records and some work and some don't. I'll keep digging and post my findings...

 

[cjhughson]

Good call Bob!

The problem wasn't in the expressions on the reports after all, but in an expression in the underlying query. Null values would cause the expression to fail and cause the errors. The reason it wasn't found before was that the report typically isn't run in cases where the culprit field is left blank, since the report was created specifically to communicate information pertaining to that field, in this case the customer's SKU number. If the customer doesn't have their own SKU number for a part, they probably wouldn't be interested in the report anyway.

Let this be a lesson to all of us against only testing "normal" use cases!

 

[boblarson]

Glad you found the problem Those can be a bear to try to find.