UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> String Functions Not Working In Access 2016, Access 2016    
 
   
bridgit
post Apr 30 2017, 06:42 AM
Post#1



Posts: 10
Joined: 30-April 17



I have two computers exhibiting the same problem, caused by recently installing Office 365, namely that simple string functions cause error messages.

For example, on my laptop (Windows 7, Access 2016), a simple Left([field],3) causes "The expression you entered contains invalid syntax"

On my desktop (Windows 10 64 bit, Access 2016 32 bit Office), I can do Mid([field],3,5) but the resulting string is the length of the string in [field] i.e. not 5 characters long. I can also do instr([field],"n"), and I can combine these two functions e.g. mid([field],instr([field],"n")) but not mid([field],1,instr([field],"n")). The error is "The expression you entered contains invalid syntax". I can do Left([field],3).

There are no missing references on either computer.

Has anyone had the same problem?

Thanks
Go to the top of the page
 
DanielPineault
post Apr 30 2017, 06:48 AM
Post#2


UtterAccess VIP
Posts: 5,453
Joined: 30-June 11



welcome2UA.gif

Have you tried to:

  • Perform a Compact and Repair
  • Decompile, Compact and Repair, Compile, Compact and Repair
  • Create a new blank db and then import everything into it
  • Restart Access
  • Shutdown and restart your PC, not hibernate or sleep, but rather a proper shutdown.
  • Update Access/Office to ensure you have all the latest fixes/patched/…
  • Perform an Office Repair through the Windows Control Panel

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...
Go to the top of the page
 
GroverParkGeorge
post Apr 30 2017, 07:44 AM
Post#3


UA Admin
Posts: 31,239
Joined: 20-June 02
From: Newcastle, WA


I closed your cross post in the Q&A forum.

Please don't post the same question in multiple forums. It doesn't increase the chances of getting a viable answer and can lead to confusion.

Thanks.

--------------------
Go to the top of the page
 
bridgit
post Apr 30 2017, 08:13 AM
Post#4



Posts: 10
Joined: 30-April 17



Thank you. I have tried compact & repair and restarting the computer (and Access). Since the behaviour was observed in a new Access file I would assume that importing into a new file is covered.

I have repaired Office. The behaviour is unchanged. Which leaves only the option of decompile. What is the easiest way to do this?

Thank you.
Go to the top of the page
 
ADezii
post Apr 30 2017, 09:01 AM
Post#5



Posts: 1,857
Joined: 4-February 07
From: USA, Florida, Delray Beach


Check your Library References to make sure that you do not have a MISSING indicator on any.
Go to the top of the page
 
DanielPineault
post Apr 30 2017, 09:13 AM
Post#6


UtterAccess VIP
Posts: 5,453
Joined: 30-June 11



In some extreme cases (actually I've ended up doing this for every one of my client with Office365!) a complete uninstall (using Option 2 from Uninstall Office 2016, Office 2013, or Office 365 from a PC) /reinstall of office can be required, but I have never heard of this issue before.

Your front-end has a Trusted Location defined for it?
Are all the PCs running x32 bit versions of Office/Access?

And I agree with ADezii, these symptoms scream missing VBA Reference Library (In the VBE, look under Tools - > References...).

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...
Go to the top of the page
 
bridgit
post Apr 30 2017, 09:14 AM
Post#7



Posts: 10
Joined: 30-April 17



Thank you. I have checked that. I think missing references are usually at the top of the list. In any case I have scrolled through the whole list.
Go to the top of the page
 
DanielPineault
post Apr 30 2017, 09:21 AM
Post#8


UtterAccess VIP
Posts: 5,453
Joined: 30-June 11



Just to confirm, on one of the problematic PCs, the VBA project compiles without any errors?

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...
Go to the top of the page
 
DanielPineault
post Apr 30 2017, 09:23 AM
Post#9


UtterAccess VIP
Posts: 5,453
Joined: 30-June 11



To decompile an Access database see:

Decompiling an Access Database
How to Decompile a Database

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...
Go to the top of the page
 
bridgit
post Apr 30 2017, 09:27 AM
Post#10



Posts: 10
Joined: 30-April 17



I can report that Mid([field],3,5) now actually returns 5 characters. This is a huge improvement from before (when the whole length of the field was return by this function). Whether this is an effect of repairing Office I can't say but ot certainly feels like it.

Unfortunately I still can't get Mid([field],1,instr([field],"n")) to work. It complains of a syntax error and highlights the instr part of the function. But Mid([field],InStr([field],"n"),8) works.

At the moment this is my only symptom (but it's bad enough).
Go to the top of the page
 
bridgit
post Apr 30 2017, 09:28 AM
Post#11



Posts: 10
Joined: 30-April 17



Thank you Daniel. Sorry, I forgot to mention that there is no code in this project. I created a new file.
Go to the top of the page
 
DanielPineault
post Apr 30 2017, 09:38 AM
Post#12


UtterAccess VIP
Posts: 5,453
Joined: 30-June 11



Well, we now know the library is not at fault.

So, I'd be focusing on the expression itself.

Mid([field],1,instr([field],"n"))


You don't account for the possibility of Null values, this could lead to issues.
How exactly are you using this expression, in what context?

Can you validate what InStr([field], "n") returns?
What is the fieldname involved? Could you be using a Reserved Word that can lead to weird behavioral headaches?

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...
Go to the top of the page
 
bridgit
post Apr 30 2017, 09:48 AM
Post#13



Posts: 10
Joined: 30-April 17



Thank you but I just used the word 'field' in this discussion. To test whether the problem is solved I simply create a new Access database, create a new table with the field 'field1' and capture a string in that field. I have naturally tried all my other files, but doing queries on various existing tables and fields and I get the same behaviour.

For example I have just created a new database with Table1 and field 'surname' and expression mid([surname],1,instr([surname],"i")). There is only one record and the value contains the letter i.

"You may have entered an operand without an operator"
Go to the top of the page
 
tina t
post Apr 30 2017, 02:59 PM
Post#14



Posts: 5,197
Joined: 11-November 10
From: SoCal, USA


i just tested the expression in A2003, and it worked for me. so it's not the expression itself. next is, how/where are you using it?

as a calculated field in a query? if so, pls show us the complete SQL statement.

as a calculated control in a form? if so, pls show us the complete expression in the control's ControlSource property.

in VBA? if so, pls show us the complete procedure.

if elsewhere, pls explain and show us.

hth
tina

--------------------
"you can't take the sky from me"
Go to the top of the page
 
bridgit
post May 1 2017, 12:23 AM
Post#15



Posts: 10
Joined: 30-April 17



Thank you. I am using the expression in a query. Apart from 'Expr1:' which appears automatically, mid([surname],1,instr([surname],"i")) is all there is. There are two of us looking at this. We both have many years' experience with Access. We can't quite believe it either. The fact that a similar (though worse) problem has appeared on another computer (laptop, which also recently had Office 365 installed on it) seems to suggest that the installation is the cause. When I repaired Office on the desktop machine there was an improvement in that mid([surname],3,5) would correctly return a 5-character long string instead of the rest of the string from the third character (but that may not be cause and effect).
Go to the top of the page
 
bridgit
post May 1 2017, 01:11 AM
Post#16



Posts: 10
Joined: 30-April 17



Thank you all so far. The laptop is no longer an issue - queries work with ; not ,. The other computer is definitely requiring , in the query - it used to use ; but has gone back to needing , since my change to Windows 10.
Attached is a screen shot: Attached File  AccessMidInstringProblem.jpg ( 32.03K )Number of downloads: 1
I use the queries for cleaning data.
Go to the top of the page
 
bridgit
post May 1 2017, 01:39 AM
Post#17



Posts: 10
Joined: 30-April 17



Hooray! My husband has just changed the list separator under Language settings from , to ; and ....
the query works now: Mid([surname];1;InStr([surname];"i")) With commas this used to get stuck on InStr.
Thank you all for offering advice and good luck to anyone with this trouble in future.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    16th December 2017 - 09:10 PM