Solved Calculated UnBound Field Throwing #Error (1 Viewer)

BusyBeeBiker

New member
Local time
Today, 01:38
Joined
Jun 27, 2021
Messages
26
I have a system which logs Absences for Individuals - Holiday, Sickness, etc. General layout below.

Screenshot (1).png

What I want to do is to show No of Absences taken for different absence types, this is done by using Absence Type Filter (see Green Filter Combo field top left).

Field Name: cmbAbsenceCode - Unbound Combo Field - 2 fields - 1. Primary Key and 2 Absence Descriptor.

Don't have any problems filtering the records into their respective Absence Codes, but DO have a problem showing Days Absence taken (see field Holidays Taken) it's throwing an error code #Error.

This field is a calculated unbound field (txtTaken) with embedded expression:
=Sum(IIf([binAuthorised]=-1 And [binCancel]=0 And [fkAbsenceCodeID]=[Forms]![frmAbsence]![cmbAbsenceCode].[Column](0),[numDays],0))

I know what is causing it (highlighted RED) because If I replace it with the Absence Code for Holidays value 13 it works!!

Form structure above is Main Form (frmPersonnel) with an embedded Sub Form (frmAbsence) which contains the Absence reporting system and expression shown above.

I think it is probably something to do with how I am referencing cmbAbsenceCode.

Any pointers on this one would be appreciated.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 09:38
Joined
May 7, 2009
Messages
19,169
use DSum() against the table:

=DSum("numdays", "theTable", "binAuthorised=-1 And [binCancel]=0 And [fkAbsenceCodeID]=" & [Forms]![frmAbsence]![cmbAbsenceCode])
 

BusyBeeBiker

New member
Local time
Today, 01:38
Joined
Jun 27, 2021
Messages
26
Somethings happening, I am now getting a different error message #Name? (see screenshot) . Researching this points to a circular reference but in both holidays taken and Holidays left field I don't see any circularity (is that a word?)

Screenshot (4).png


Field - txtTaken : =DSum("numdays","tblAbsence","binAuthorised=-1 And [binCancel]=0 And [fkAbsenceCodeID]=" & [Forms]![frmAbsence]![cmbAbsenceCode])

Field - txtLeft : =([txtHolidays]+[numCFLieuDays])-DSum("numdays","tblAbsence","binAuthorised=-1 And [binCancel]=0 And [fkAbsenceCodeID]=" & [Forms]![frmAbsence]![cmbAbsenceCode])

Fields [txtHolidays] and [numCFLieuDays] are on the main form frmPersonnel, do I explicity have to reference them as such in field txtLeft?

 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 09:38
Joined
May 7, 2009
Messages
19,169
see table tblAbsence, do you have the fields you put in Dsum() on the table?
 

BusyBeeBiker

New member
Local time
Today, 01:38
Joined
Jun 27, 2021
Messages
26
Yes binAuthorised, binCancel and fkAbsenceCodeID are all fields in tblAbsence (see attached)
Screenshot (5).png
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 09:38
Joined
May 7, 2009
Messages
19,169
only "numdays" is not in the table,
how do you calculate it, maybe you can plug the calculation of dsum().
 

BusyBeeBiker

New member
Local time
Today, 01:38
Joined
Jun 27, 2021
Messages
26
numDays is a calculated field that is plugged into the underlying SQL Statement for the form see below.

numDays: IIf(([fkHoursID]=1 Or [fkHoursID]=2) And [binAuthorised]=-1 And [binCancel]=0,0.5,IIf([fkHoursID]=3 And [binAuthorised]=-1 And [binCancel]=0,DateDiff("d",[dtmStartDate],[dtmEndDate])+1,0))

Are you saying that you can't DSum calculated fields and I must calculate the number of Absence Days which the field numDays represents somehow on the fly in the DSUM Calculation? If so can you nudge me in the write direction.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 09:38
Joined
May 7, 2009
Messages
19,169
if you are using Query, then in DSum(), use same Query:

=DSum("numdays","theQueryName","binAuthorised=-1 And [binCancel]=0 And [fkAbsenceCodeID]=" & [Forms]![frmAbsence]![cmbAbsenceCode])
 

bastanu

AWF VIP
Local time
Yesterday, 18:38
Joined
Apr 13, 2010
Messages
1,401
In your first post you say the frmAbsence is a subform of frmPersonnel yet you don't mention frmPersonnel in your expression. Please try:
=Sum(IIf([binAuthorised]=-1 And [binCancel]=0 And [fkAbsenceCodeID]=[Forms]![frmPersonnel]![frmAbsence].Form![cmbAbsenceCode].[Column](0),[numDays],0))

See http://access.mvps.org/access/forms/frm0031.htm for more info.

Cheers,
 

BusyBeeBiker

New member
Local time
Today, 01:38
Joined
Jun 27, 2021
Messages
26
In your first post you say the frmAbsence is a subform of frmPersonnel yet you don't mention frmPersonnel in your expression. Please try:
=Sum(IIf([binAuthorised]=-1 And [binCancel]=0 And [fkAbsenceCodeID]=[Forms]![frmPersonnel]![frmAbsence].Form![cmbAbsenceCode].[Column](0),[numDays],0))

See http://access.mvps.org/access/forms/frm0031.htm for more info.

Cheers,
Nope just comes back with #Error.

However if I replace [[Forms]![frmPersonnel]![frmAbsence].Form![cmbAbsenceCode].[Column](0) with 13 which is the code for Holiday Absence it works perfectly so it is something to do with how cmbAbsenceCode is expressed.

Is [[Forms]![frmPersonnel]![frmAbsence].Form![cmbAbsenceCode].[Column](0) expressed as a value. Row Source is :

SELECT tblAbsenceCode.pkAbsenceCodeID, tblAbsenceCode.txtAbsence FROM tblAbsenceCode ORDER BY tblAbsenceCode.txtAbsence;

With tblAbsenceCode.pkAbsenceCodeID being the Primary Key for the Absence Type Code.
 

Minty

AWF VIP
Local time
Today, 01:38
Joined
Jul 26, 2013
Messages
10,355
You can't refer to a column reference in a form reference in a query.
Only the bound value.
 

BusyBeeBiker

New member
Local time
Today, 01:38
Joined
Jun 27, 2021
Messages
26
You can't refer to a column reference in a form reference in a query.
Only the bound value.
OK understand that but why doesn't [[Forms]![frmPersonnel]![frmAbsence].Form![cmbAbsenceCode].[Column](0) above work.

Clearly the expression structure is valid as evidenced by hardwiring in a value code 13, but doesn't if expression above is used, weird.

Is it because the summated field is in the footer of the sub-form should it be:

[[Forms]![frmAbsence]![cmbAbsenceCode].[Column](0)
 

bastanu

AWF VIP
Local time
Yesterday, 18:38
Joined
Apr 13, 2010
Messages
1,401
Add a hidden textbox named txtAbsenceCode on the subform and make its control source = [cmbAbsenceCode].[Column](0). Now in your footer expression reference that control instead of the combo: Forms![frmPersonnel]![frmAbsence].Form!txtAbsenceCode

Cheers,
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 09:38
Joined
May 7, 2009
Messages
19,169
you can drop th Column(0) part if the Bound column of your combo is set to 1:

[Forms]![frmPersonnel]![frmAbsence].Form![cmbAbsenceCode]
 

BusyBeeBiker

New member
Local time
Today, 01:38
Joined
Jun 27, 2021
Messages
26
Thanks for both of those tips, I will do both.

Interestingly I found a different wrinkle on this as well, if you take the expression:

=Sum(IIf([binAuthorised]=-1 And [binCancel]=0 And [fkAbsenceCodeID]=[Forms]![frmPersonnel]![frmAbsence].Form![cmbAbsenceCode].[Column](0),[numDays],0)

and remove the reference to cmbAbsenceCode

=Sum(IIf([binAuthorised]=-1 And [binCancel]=0)

Not finished....

IT WORKS.

Assume the self filtering of the subform and the self selection of a particular type of Absence Code takes care of this part of the statement.

Now got a problem with Holidays Left Calculated field but will start a new thread if I can't sort it out.

Many thanks for all your input.

PS How do I mark as solved?
 

Users who are viewing this thread

Top Bottom