Setting that's causing Access to crash

Hi Experts,

I'm trying to set a control source of a text box as follows and thats causing Access to crash, what can be the cause/solution?
=nz([TextTotalMoneyAvail],0)-(nz(([Housing].[column](2),0)*[WeeksOfContract])+nz([TravelTo],0)+nz([TravelFrom],0)+nz([OtherMalprPhysical],0)+nz([HealthInsurance],0))/117*100

Open in new window

LVL 6
bfuchsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Try this:
 =(Nz([TextTotalMoneyAvail], 0) - (Nz([Housing].[Column](2), 0) * [WeeksOfContract]) + Nz([TravelTo], 0) + Nz([TravelFrom], 0) + Nz([OtherMalprPhysical], 0) + Nz([HealthInsurance], 0)) / 117 * 100

This compiles in VBA ... your post does not
0
bfuchsAuthor Commented:
Hi Joe,

Now I get #Name? on that box.
fyi- it was working fine until I added the * [WeeksOfContract], now even when I remove that it crashes.
Thanks,
Ben
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Well #Name means .... Access cannot resolve one or more field names
By crashes now ... do you mean #Name ?
Or what do you mean by crashes ??
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Try changing this segment
from
 (Nz([Housing].[Column](2), 0) * [WeeksOfContract])

to

 ( Nz([Housing].[Column](2), 0) * Nz([WeeksOfContract],0) )
0
bfuchsAuthor Commented:
that doesn't work either.

see attached what crashes means..

I will log off now, will resume tom.

Thanks,
Ben
untitled.png
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Oh ... THAT kind of crash :-(
OK ... well ...
Try a Compact & Repair.
And a Decompile ...
1
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Get the details out of the event log and post here.  That may give a clue as to what the error is.

 I'd also make sure your patched to current revs.  

 The back handed way to work into this is to test small parts of the statement one at a time (ie. first half, then second half, then the /117*100 until you find where the problem is.   Along that line, consider leaving the controlsource blank for testing and set the value in VBA in code in the OnCurrent event or an AfterUpdate event.  This will let you breakpoint the line and inspect values that are going into the calculation.   That's what I typically do as I am not a big fan of setting complicated control sources.  I'd rather push the data into an unbound control.

 Because of what Joe has already walked you through, at a guess it is probably some type of data conversion error.  

Jim.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bfuchsAuthor Commented:
@Joe,
I tried that and didn't help, not sure why you're suspecting something wrong with the app as everything else works fine, including this form, when I remove that property setting..?

@Jim
I have the latest Win/Office patches installed.
The below is whats showing under event viewer/application, is it of any help?

Faulting application name: MSACCESS.EXE, version: 11.0.8204.0, time stamp: 0x4763166b
Faulting module name: msjtes40.dll, version: 4.0.9756.0, time stamp: 0x49246e49
Exception code: 0xc0000005
Fault offset: 0x0000d4a0
Faulting process id: 0x5bfc
Faulting application start time: 0x01d2989041a0278e
Faulting application path: C:\Program Files (x86)\Microsoft Office\OFFICE11\MSACCESS.EXE
Faulting module path: C:\Windows\SysWOW64\msjtes40.dll
Report Id: 9d024e78-0504-11e7-afac-64006a53b03a

set the value in VBA in code in the OnCurrent event or an AfterUpdate event.  This will let you breakpoint the line and inspect values that are going into the calculation.
This happens to get me somewhere..see attached.

Now my question, in this case where only the first control has a value while the rest are null, can you see what is wrong with my code?

Thanks,
Ben
untitled.png
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Ah...check your data types....your trying to stuff something into a smaller field (like a long into an integer).

 You either need to change data types, or do some explicit data typing (i.e   CCur(Nz([Housing].[Column](2), 0) )

Jim.
1
bfuchsAuthor Commented:
Ah...check your data types....
Again you got this..see attached.
Now back to the main issue, what is preventing this from fitting into the ControlSource, as each text box should be a variant, no?

Thanks,
Ben
untitled.png
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<should be a variant, no?>>

 Have no idea what Access does internally in a control.   A "variant" is something in VBA, not in Access.

 I just caught that initially you had dimmed "i" as an integer, so that's where the overflow came in.

 Try taking it as it stands now and pushing it into the control.

Jim.
0
bfuchsAuthor Commented:
when I do MyTextBox=v it accepts.
However when I assign the whole calculation as ControlSource it gives me the attached error.
See immediate window.

Thanks,
Ben
Untitled1.png
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
As I said, I would not set the controlsource but leave it blank and push the value of the calculation into the control.   What I do is write one procedure to update all the calculated controls, then call that from OnCurrent and any AfterUpdates as needed (usually call it RefreshDisplay() ).

 I have always steered clear of letting Access figure out complex expressions.

 Also, that CCur() call was just an example.   I don't know if that's what you actually need on that field.

Jim.
0
bfuchsAuthor Commented:
The problem is, this is a form with dozens of calculated controls, and I cannot do it just for this one as one depends on the other, its either I find the issue or will have to change all.
I have always steered clear of letting Access figure out complex expressions.
Besides for debugging, do you see other advantages from doing it by code?

Also, that CCur() call was just an example.
They're all defined in SQL as money, so that would not hurt.

Thanks,
Ben
0
bfuchsAuthor Commented:
Hi Jim,

Just updating..
At the moment I was able to get this below working w/o crashing
=Nz([TextTotalMoneyAvail],0)-(CCur(Nz([Housing].[Column](2),0))*Nz([WeeksOfContract],0))+Nz([TravelTo],0)+Nz([TravelFrom],0)+Nz([OtherMalprPhysical],0)+Nz([HealthInsurance],0)/117*100

Open in new window

Any idea whats doing the trick?

Thanks,
Ben
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"not sure why you're suspecting something wrong with the app as everything else works fine, including this form, when I remove that property setting..?
"
Well ..I've seen C&R and/or Decompile fix exactly those kinds of issues.
0
bfuchsAuthor Commented:
At least its working now..Thank you my experts!
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
You are welcome :-)
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Besides for debugging, do you see other advantages from doing it by code?

 Being able to do *anything* to get a value into a control.   But even if it was just the debugging piece, it would be worth doing IMHO.

 With calculations in controls, you can never be sure of what's going on and when you might have bad data.

 Depending on what I need, I may do the calc in a query.   But if it's anything beyond a few factors and basic math, or more than one or two calc's that I need, then I move it all to VBA code.  
 
  Overall, it's pretty rare that I do a calculated control in Access.

Jim.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.