Help with subtracting two dlookup fields (1 Viewer)

[gojets1721]

On a report, I have two fields using dlookups and they both show percentages.

I have a third field that is meant to calculate the difference between the two. I subtracted one field's name from another but am getting a #Type error.

Any suggestions on how to correctly do this?

 

[GPGeorge]

Lookup Fields in a table? Ouch. That's the hard road for sure. Most experienced Access developers would tell you that lookups, or combo boxes, are interface features, and interface features belong in interface objects, i.e in forms. Microsoft fell for the trap, and put them into Access tables, but that has almost always led to woe and sorrow for those who use them,

So, that's the big problem. However, we also need a bit more detail to help you solve this dilemma.

Where is the third field supposed to go? In the same table? In a report? In a form?

 

[gojets1721]

Lookup Fields in a table? Ouch. That's the hard road for sure. Most experienced Access developers would tell you that lookups, or combo boxes, are interface features, and interface features belong in interface objects, i.e in forms. Microsoft fell for the trap, and put them into Access tables, but that has almost always led to woe and sorrow for those who use them,

So, that's the big problem. However, we also need a bit more detail to help you solve this dilemma.

Where is the third field supposed to go? In the same table? In a report? In a form?

So the report has three fields right next to one another. First two are lookups to tables and the third is meant to show the difference between the two fields

 

[arnelgp]

use same Expression you used on the two textbox for your third textbox:

'ex:
'[dlookup of first textbox] - [dlookup of second textbox]

=Nz(Dlookup("field1","table1"),0) - Nz(DLookup("field2","table2"), 0)

 

[GPGeorge]

So the report has three fields right next to one another. First two are lookups to tables and the third is meant to show the difference between the two fields

You probably need to use the VALUES from the lookups on the report, not the BOUND columns.

 

[June7]

By "two fields using dlookups" do you mean textboxes? What are the textbox names? Shouldn't have to repeat expressions. Works for me.
Provide sample data and the expressions used. Could provide db for analysis.

 

[Galaxiom]

If both DLookups are from the same table you would only need one DLookup. The first parameter of Domain Functions is an expression, not a fieldname.

DLookup("field1 - field2","tablename", {condition})

 

[KitaYama]

If both DLookups are from the same table you would only need one DLookup. The first parameter of Domain Functions is an expression, not a fieldname.

DLookup("field1 - field2","tablename", {condition})

Didn't know I can do math in one lookup.
Thanks...

 

[June7]

Good point, Galaxiom.

Also, can either field be Null? Arithmetic with Null returns Null.

Without any criteria, DLookup will return value from first record it encounters so could get unexpected results.

 

[KitaYama]

Also, can either field be Null?

Just tested it. It returns null if one of the fields (any of them) is null.

 

[June7]

If you want don't want null to return then handle with Nz()
"Nz(field1) - Nz(field2)"

 

[gojets1721]

By "two fields using dlookups" do you mean textboxes? What are the textbox names? Shouldn't have to repeat expressions. Works for me.
Provide sample data and the expressions used. Could provide db for analysis.

@June7 See the (extremely simplified) attached example DB.

 

[June7]

Your data is text (why?), not number, including a % symbol. Hence the #Type error with arithmetic. Without % symbol, arithmetic would work. Otherwise, have to handle it:

=Val([txtEmployeeScore])-Val([txtBenchmark])

These tables do not have a relationship. tblAudit is not normalized. Audit1 should not be a field. Normalized structure would be fields:

EmployeeID, AuditID, Score.

Then you could do a query that joins tables on AuditID and avoid slow performance introduced by domain aggregate functions.