Caculated field in Microsoft Access query suddenly fails on different machine with different data-CodePudding

In the following query, calcValB suddenly started showing #Error for all records after changing the data in table. It seems it suddenly cannot recognize calcValA.

SELECT
  FilePath1,
  cfgVal1   InStr(Mid(FilePath1, cfgVal1), "\") AS calcValA,
  calcValA   InStr(Mid(FilePath1, calcValA), "\") AS calcValB,
  ...
 FROM
   Table1,
   Config
 WHERE ...

Value of cfgVal1 is 60; Len(FilePath1) is always greater than 80; FilePath1 contains at least one \ after position 60.

I can work it around using nested queries, but I wonder how this could happen.

What did I try:

executed the formulas in VBA – they work OK
recovered all the queries from the backup – errors remain
tested that the same query works on older copy of tables – it works without error
teared the expression down to parts that made it obvious that it is calcValA which leads to #Error if referenced
tried to modify the query to make calcValA = Len(FilePath1), calcValB = calcValA 5 – this one works
tried to supply third optional parameter into Mid(), set to 999 – still error

My current suspection is that the issue may be with Access running on computers with different locale setting. I am using Slovak and the colleague is using U.S. I already have seen that in Access, for example form section Časť1 (Part1) throws error on U.K. locale machines. (The funny thing is that the localized name is put there by default by Access!) But in this case I made sure that the query is 7-bit ASCII only.

Sample data:

Table1.FilePath1 (2 records)

a:\a111111\aaaaaa\aaaa\aaaaaaaaaaa\aaaaaa\aaaa aaaaaaa\aa-1\aa\aa1111111\aaaaaaa\aaaaaaa.aaa
a:\a111111\aaaaaa\aaaa\aaaaaaaaaaa\aaaaaa\aaaa aaaaaaa\aa-1\aa\aa1111111\aaaaaaaa\aa1111111.aaaa

Config.cfgVal1 = 61 (there is a single record in the table)

The problm is, the quaery and data work on my machine, they don't work on colleagues' machine.

CodePudding user response：

Compact and Repair the database.

After carrying out Database Tools > Compact and Repair Database command, the query started working correctly again. Instead of #Error values, proper numbers started to appear in calcValB.

If you have the source table present in the database as a linked table (from another database), do the same operation with database file which actually contains the table (not only with the database file where the table appears as linked table).

After recovery from such an error, it is a good idea to check whether no records are missing in the table.