Query Too Complex On Certain Computers

[Dharty]

I have an issue with an Access database which I set up to manage the total number of months worked by an employee over multiple temporary contracts with any time in a specific period subtracted. This was due to a necessity to control exactly how many months temporary staff had been employed by the company due to legal reasons (and there was a "discounted period" in which months worked did not count towards this maximum number of months a temporary employee can work).

The standard VBA funtions were not accurate enough (datediff etc) so I had to create customer functions in VBA that were accurate to 1 day. In the end the DB worked and everything was good.

For some reason the database did not work on all computers - error 3071 "this query is either incorrectly written or too complex to evaluate" appeared when running the bugger queries. I assumed that once the office packages were upgraded that these messages would stop appearing and over time all users would be able to use it.

However this didn't happen - there are in fact more computers which cannot view the complex queries after upgrading (although my machine has also been upgraded and it works absolutely fine.

I have checked:

- All computer are using Windows 7 v 6.1 Build 7601 SP1
- The VBA reference libraries seem to be different. My machine has Microsoft Access 15.0 Object Library & Microsoft Office 15.0 Access Database engine Object Library whereas the computers which can't run it seem to have version 12.0 of both these libraries.

I have tried:

- Updating the reference libraries for the computers with 12.0 however 15.0 doesn't appear to be available nor do I know how/if I should/if I can download said 15.0 libraries and install them on the machines where the DB doesn't work.

- As there are multiple quite complex calculated fields which are then summed to get to the final number, I tried extracting the complicated calculations to VBA custom functions. these custom functions work on my computer but don't work on the computers which cannot open the complex queries (I get a simple "#Error" value.

I have attached the code and some images to this post in case its helpful. I would really appreciate some insight in to why this is happening as after a day of googling, forum scavenging and documentation through-combing I've come up with zip.

Thanks a lot in advance for your help!

 

[Old Man Devin]

Those computers that only have the 12.0 objects available on them: are they running the same access version as you, with your 15.0 ones available?

There are limitations to access that have been gradually lifted with each new version, so the newer versions are more likely to work, which may be what you are experiencing here. The ones with 12.0 only are probably stuck with the limitations of Access 2007, but you said they have upgrade to the same access version as you?

 

[Dharty]

Some of the computers are using Access 2013, some of them Access 2010 and some Access 2007. The access version doesn't seem to correlate to the issue. Also the latest versions are on brand new computers so have not been upgraded to 2013, they've been installed directly to 2013.

Do you have any idea how I could install the 15.0 references if they don't already exist on the reference library list?

 

[Old Man Devin]

Some of the computers are using Access 2013, some of them Access 2010 and some Access 2007. The access version doesn't seem to correlate to the issue. Also the latest versions are on brand new computers so have not been upgraded to 2013, they've been installed directly to 2013.

Do you have any idea how I could install the 15.0 references if they don't already exist on the reference library list?

If they have Access 2013, I would have expected them to have the 15.0 references, as they are part of the program. I have no idea if it's possible to get the references in another way, sorry. You should probably google that one to see if anyone else has tried it.

The alternative of course is to try and make that query field less complicated! Possibly using other queries to do parts of the calculation, then linking them into your main query. Or trying to optimise the code routine you are using.

 

[Dharty]

Thanks a lot for your advice. I would still like to know the reason why because I don't feel comfortable making Access tools when I can't guarantee that they will work on multiple machines.

I will definitely try what you're suggesting but does anybody have any other ideas?

 

[Rx_]

We create very complex queries with complex formulas. This is done using SQL Server as the back end. Although, much of the queries are done in the front end.
The deployment is done on Citrix (much like a Remote Terminal).
There is only one Access production copy posted to a network folder. When each uer logs onto Citrix, a copy is put in that users folder and launched. The Citirx Application Server (running Access and Office were full automation is used) is exactly the same version as the developers version.

Each user just needs to install the ICA client. In Citrix (or termote terminal) only the 1/64th of the screen that changes is sent, plus keystrokes and mousemovements. This has allowed 1 56K modem to support 2 (and even 3) concurrent Access users. The bandwidth is very small. Not even a Java application can touch the low bandwidth. That is because all of the processing is taking place on the Citirx application server. MS Access over ICA runs perfectlly on Apple or smart phones (of course, screen resolution must be adjusted for the size).
Security using ICA is greatly enhanced as the Network server controls all usage. Plus the ICA traffic is more than encrypted as it only sends changes.

A client doesn't need to have MS Access installed. Doesn't need the IT staff constantlly upgrading, updating client computers. The security overhead is smaller, and the distribution overhead is much smaller.
People who quote the cost typically can't associate loss of time, loss of data or people's efficiency in dollars. If they did, they would switch to this virtualized delivery system.

 

[Cronk]

Dharty

Are you sure you do not have null dates that you are feeding to your query?

 

[Dharty]

Thanks Rx, though to be honest I think that kind of solution would be out of my capabilities to provide (and to maintain).

Cronk - yes I am absolutely positive that there are no null fields causing the #error. The same code (the same database in fact) works perfectly on other computers.

Old Man Devin - I tried to remove the complicated calculated fields in to separate queries but even the following is supposedly too complex

D1_Same_Or_Bigger_Than_Discount_Window: IIf([Contract Day 1 Within Rolling Window]<=[Forms]![FRMhome]![Disc_Start_Date] And [Contract End Date]>=[Forms]![FRMhome]![Disc_End_Date],getMonths([Forms]![FRMhome]![Disc_Start_Date],[Forms]![FRMhome]![Disc_End_Date]),0)

I do not believe this is too complex. I think there is something funky about the system config maybe or the reference libraries.

I appreciate the help ^_^ : any other ideas?

 

[JHB]

...
I appreciate the help ^_^ : any other ideas?

I would try to run the query on a small amount of data - 5-10 records/rows.
If it doesn't help, I would take out all the calculations from the query, and then add them one after one, to see which one gives the error.
And ofcause it should be done one a copy of you database - Front-/Backend.
Instead of referring to a control on a form, I would use hard coded values in the "testing stage".
And I don't like field names with spaces in!

 

[Dharty]

I'll look into all of those ideas! Thanks!

I'm also going to ask IT to re-install Access on one of the computers which doesn't run it as the 15.0 libraries should be present according to Old Man Devin.

I thought spaces in field names were fine if surrounded by square brackets (?)...

 

[Dharty]

I think I may have uncovered the root of the problem though I haven't yet come up with solution!

So painstakingly going through the calculated fields one by one and in each one swapping out the variables for hardcoded values, it seems that the error comes from when the calculated field refers to a drop down box on a form (remember the discounted period of months? There is a period start date and a period end date which can be selected on the home page).

When the selected date in the drop down box is referred to in a query, it is not passing a date which can be manipulated in the queries. The list boxes contain string dates formatted as "long dates".

I guess my options are:

1) Create some constant values in the module (user loses capacity to change this date)
2) Change the way the user selects the dates

Any ideas?

 

[JHB]

My first thought would be to put the dates selected in the dropdown box in some hidden text controls on the form.
I do not have good experience with retrieving data from drop down boxes in queries.
Or store/ retrieve the selected dates in the drop down box, in variables in a module, using some UDF.

 

[Dharty]

In the end I just changed the drop down box to a text box and the home page's on load event filled them with only numeric data (it seems that the long date which was pre-filled with a date including "September" wasn't registering as a date when opened on a computer with a non English system config. This is especially confusing as you can have the windows system config language as Spanish but then have the Spanish localised text displayed. Painful but at least its sorted.

Big thanks to all the helpers here