Strange error in calculated field in split database (1 Viewer)

DataBass

Registered User.
Local time
Today, 16:29
Joined
Jun 6, 2018
Messages
68
I have a a form with many calculated controls on it. The form works perfectly.

I recently split the database into front and back ends and installed it on a client's network. The front end is installed on each user's pc and the back end is on the server.

However, when the users open the form, one of these calculated controls, always shows #Type!. The other calculated controls work without error. If the form is refreshed with F5, the error goes away and the control then shows the correct value.

I initially thought that the control might be trying to perform the calculation before the data is loaded into the form, so I tried making the form requery and/or refresh on the Open, Load and Current events, but that didn't help.

I have tried duplicating this on my own network and I can't duplicate the error.

If I connect to the backend remotely using something like Teamviewer, I can't get the error. It only happens on the client's own network.

For what it's worth, here is the code for the controls:

The control which shows the error has a control source of:
Code:
=Nz([txtIncome]-[txtExpense],"")
The control source for txtIncome is:

Code:
=DSum("Amount","tblIncome","[ProjectID]=" & [Forms]![frmProjects]![ProjectID] & "AND Not([Date] Is Null)")
The control source for txtExpense is:
Code:
=DSum("Total","tblExpense","ProjectID=" & "Forms!frmProjects!ProjectID")
At this point I'm not even sure where to look to correct this problem. Does anyone have any idea of what might be going on here?

Thanks
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 16:29
Joined
Aug 30, 2003
Messages
36,118
Several thoughts, the first of which to solve your problem might be to add

Me.Requery

to the form's current event. I'd disambiguate this to

Nz([txtIncome]-[txtExpense], 0)

I'd simplify this to:

Code:
=DSum("Amount","tblIncome","[ProjectID]=" & [Forms]![frmProjects]![ProjectID] & " AND [Date] Is Not Null")

This I'm surprised works with the quotes, so that may be a typo. I'd have:

=DSum("Total","tblExpense","ProjectID=" & Forms!frmProjects!ProjectID)

You shouldn't have a field named Date, as it can conflict with the Date() function. Look up reserved words.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 16:29
Joined
Aug 30, 2003
Messages
36,118
Shoot, that's a case of not reading that you already tried that in the current event. Sorry. Try requerying just the control:

Me.ControlName.Requery
 

isladogs

MVP / VIP
Local time
Today, 23:29
Joined
Jan 14, 2017
Messages
18,186
Do all users have the same version of Access?
Try checking to see if any references are marked MISSING on client workstations.

Do some users have 64-bit Access and others 32-bit?
 

DataBass

Registered User.
Local time
Today, 16:29
Joined
Jun 6, 2018
Messages
68
pbaldy,

Thanks for the reply, I tried recalculating just the control also, I should have mentioned that.

At the end of the Nz function, I used "" instead of 0 because I didn't want to have a bunch of 0's on the form when there is no data. But I'll try 0 and see if that helps.

I'll also try simplifying the Null portion you pointed out. I think I may have tried that early on but it wouldn't hurt to try again.

And I'll try removing the quotes. You're right, that is weird. I'm surprised it works also.

Good ideas. Thanks
 
Last edited:

DataBass

Registered User.
Local time
Today, 16:29
Joined
Jun 6, 2018
Messages
68
Colin,
All users have Office 365, which I believe updates itself.

You bring up a good point regarding 32/64 bit. I developed the database with Access 2016 32 bit. I don't know what the users have on their machines.

I need to check this.
 

DataBass

Registered User.
Local time
Today, 16:29
Joined
Jun 6, 2018
Messages
68
Colin,

To check for MISSING references, where would I look for this? In design view where you would see the control source of a control?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 23:29
Joined
Feb 19, 2013
Messages
16,553
Not sure if this is correct anyway

Nz([txtIncome]-[txtExpense], 0)

means if either Income or Expense is null, show 0 - is that what is required?

I used "" instead of 0 because I didn't want to have a bunch of 0's on the form when there is no data.
If you use the value in later calculations, you will get an error if you use "" because you would be mixing text and numbers. So use 0 and then use the format property to show a blank when zero.

format property has 4 states, separated by a semi colon

positive;negative;zero;null
 

DataBass

Registered User.
Local time
Today, 16:29
Joined
Jun 6, 2018
Messages
68
Thanks for all of the input!

I tried all of your suggestions. All of them generally were improvements. The ultimate culprit ended up being the " " around the form reference. Changing this:

Code:
"Forms!frmProjects!ProjectID"
to this:

Code:
[Forms]![frmProjects]![ProjectID]
Did the trick.

Thanks again for the help.
 

Mark_

Longboard on the internet
Local time
Today, 16:29
Joined
Sep 12, 2017
Messages
2,111
SPECIAL NOTE:
DSum can return NULL.
=Nz([txtIncome]-[txtExpense],"") can have issues if TxtIncome OR TxtExpense is a null.

NULL - 17 = ???

You may want to wrap your DSums in NZ, just to make sure you don't try multiplying or dividing by NULL... ACCESS doesn't like imaginary numbers for some strange reason.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 19:29
Joined
May 21, 2018
Messages
8,463
Code:
And I'll try removing the quotes. You're right, that is weird. I'm surprised it works also.
Should not be so surprising.
"ProjectID=" & "Forms!frmProjects!ProjectID"
would create the where clause
Code:
"WHERE ProjectID= Forms!frmProjects!ProjectID"
Which is a valid where clause. Its kind of a circular way to do it because instead of passing the query the value you already have you are telling the query to find it. But I see people do it all the time.

The strange thing is why that is intermittent. It seems that the query used by the DSUM either cannot see the form or the field reference is returning null.
 
Last edited:

DataBass

Registered User.
Local time
Today, 16:29
Joined
Jun 6, 2018
Messages
68
How "ProjectID=" & "Forms!frmProjects!ProjectID" creates a valid 'where' clause is a bit over my head, but I completely agree that the fact that the error is intermittent and never occurs on my home network is VERY strange.
 

Users who are viewing this thread

Top Bottom