Query formula crashes Access

%3CLINGO-SUB%20id%3D%22lingo-sub-1361017%22%20slang%3D%22en-US%22%3EQuery%20formula%20crashes%20Access%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1361017%22%20slang%3D%22en-US%22%3E%3CP%3EHas%20anyone%20else%20experienced%20Access%20crash%20when%20opening%20a%20query%20with%20InStr%20in%20a%20formula%3F%20I%20am%20not%20sure%20when%20this%20started%2C%20and%20it%20took%20trial%20and%20error%20to%20associate%20sudden%20crashing%20with%20using%20this%20function%20in%20a%20formula.%26nbsp%3B%20Possibly%20its%20something%20else%3B%20this%20is%20just%20my%20empirical%20observation.%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20longstanding%20query%20formula%20fields%26nbsp%3B%20%26nbsp%3Bsuch%20as%20%3CSTRONG%3ELeft(%5BIIDesc%5D%3CFONT%20color%3D%22%23FF0000%22%3E%2CInStr%3C%2FFONT%3E(1%2C%5BIIDesc%5D%2C%22(%22)-1)%20AS%20Task%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3ENo%20longer%20working.%26nbsp%3B%20Access%20closes%20with%20no%20warning%20or%20error%20message%3B%20somthing%20must%20be%20overrunning.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20in%20Build%2012624.20520%20Microsoft%20365%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1361017%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1361108%22%20slang%3D%22en-US%22%3ERe%3A%20Query%20formula%20crashes%20Access%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1361108%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F261199%22%20target%3D%22_blank%22%3E%40early_steve%3C%2FA%3E%26nbsp%3BThere%20are%20a%20couple%20of%20possibilities.%20One%20is%20corruption%20in%20the%20accdb.%20That%20might%20be%20corrected%20by%20compiling%20the%20VBA%20and%20doing%20a%20compact%20and%20repair%20on%20the%20accdb.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20other%20is%20missing%20references.%20Check%20those%20in%20your%20accdb.%20If%20one%20is%20flagged%20as%20%22MISSING%22%2C%20that'll%20be%20the%20culprit.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22References.jpg%22%20style%3D%22width%3A%20445px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F189170i0CD5F01DE56A7E0E%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22References.jpg%22%20alt%3D%22References.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1361963%22%20slang%3D%22en-US%22%3ERe%3A%20Query%20formula%20crashes%20Access%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1361963%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F46682%22%20target%3D%22_blank%22%3E%40George%20Hepworth%3C%2FA%3E%26nbsp%3BGood%20suggestions.%20I%20should%20have%20mentioned%20that%20in%20investigating%20the%20issue%2C%20I%20tried%20a%20number%20of%20system%20type%20things%2C%20including%20checking%20for%20Office%20updates%2C%20creating%20a%20blank%20database%2C%20importing%20everything%2C%20recompiling%2C%20relinking%20all%20linked%20tables%2C%20and%20updating%20all%20references.%26nbsp%3B%20%26nbsp%3B%26nbsp%3BI%20also%20copied%20one%20of%20the%20tables%20into%20a%20fresh%20temp%20table%20separate%20from%20the%20rest%20of%20the%20data%20structure%20and%20ran%20one%20of%20the%20queries%20on%20that.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20are%20giving%20me%20an%20idea%3A%20maybe%20I%20need%20to%20troubleshoot%20of%20reinstall%20Office.%20More%20likely%2C%20however%2C%20if%26nbsp%3B%26nbsp%3Bno%20one%20else%20has%20had%20this%20issue%2C%20then%20I%20will%20finish%20skirting%20around%20the%20handful%20of%20queries%20that%20crashed%20the%20system.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Has anyone else experienced Access crash when opening a query with InStr in a formula? I am not sure when this started, and it took trial and error to associate sudden crashing with using this function in a formula.  Possibly its something else; this is just my empirical observation.   

 

I have longstanding query formula fields   such as Left([IIDesc],InStr(1,[IIDesc],"(")-1) AS Task

No longer working.  Access closes with no warning or error message; somthing must be overrunning.

 

This in Build 12624.20520 Microsoft 365   

 

 

 

 

2 Replies
Highlighted

@early_steve There are a couple of possibilities. One is corruption in the accdb. That might be corrected by compiling the VBA and doing a compact and repair on the accdb.

 

The other is missing references. Check those in your accdb. If one is flagged as "MISSING", that'll be the culprit.

References.jpg

 

 

Highlighted

@George Hepworth Good suggestions. I should have mentioned that in investigating the issue, I tried a number of system type things, including checking for Office updates, creating a blank database, importing everything, recompiling, relinking all linked tables, and updating all references.    I also copied one of the tables into a fresh temp table separate from the rest of the data structure and ran one of the queries on that. 

 

You are giving me an idea: maybe I need to troubleshoot of reinstall Office. More likely, however, if  no one else has had this issue, then I will finish skirting around the handful of queries that crashed the system.