Data Exists but DLOOKUP fails to return (1 Viewer)

Access_Help

Registered User.
Local time
Today, 07:03
Joined
Feb 12, 2005
Messages
136
Not sure why it is returing some values but not others when they clearly exist in the table?:banghead:

Expr1: DLookUp("[Grade]","tbl_grade","[A_Score] = " & [Percentage])

Percentage is a calculated field.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:03
Joined
May 7, 2009
Messages
19,169
you are using the function on same table?


Expr1: DLookUp("[Grade]","tbl_grade","[A_Score] = [Percentage]")

the above will return Null or the First record whose criteria is met.
 

Access_Help

Registered User.
Local time
Today, 07:03
Joined
Feb 12, 2005
Messages
136
you are using the function on same table?


Expr1: DLookUp("[Grade]","tbl_grade","[A_Score] = [Percentage]")

the above will return Null or the First record whose criteria is met.

The function is on a query that has a calculated field, looking up a value from the table tbl_att_grade.

I am getting values but not all..some missing although they exist in the lookup table.
 

Gasman

Enthusiastic Amateur
Local time
Today, 14:03
Joined
Sep 21, 2011
Messages
14,042
The function is on a query that has a calculated field, looking up a value from the table tbl_att_grade.

I am getting values but not all..some missing although they exist in the lookup table.

So what is the name of the table?
"tbl_grade" or " tbl_att_grade"? :confused:
 

plog

Banishment Pending
Local time
Today, 09:03
Joined
May 11, 2011
Messages
11,611
Expr1: DLookUp("[Grade]","tbl_grade","[A_Score] = " & [Percentage])

First, there is no reason to use a Dlookup in a query. Instead you use a JOIN. Just bring in tbl_grade to the query, connect appropriately and bring in [Grade].

Second, is [Percentage] calculated in the same query? You can't reference a field that is calculated in the same query--it hasn't been calculated until the query runs.

Third, a field named [A_Score] makes it seem like your data is not properly structured. When you prefix/suffix field names with identifiers (e.g. A_, B_, C_) you are storing your data incorrectly and most likely need a new table.

How many of these _Score fields do you have in tbl_grade?
 

sonic8

AWF VIP
Local time
Today, 15:03
Joined
Oct 27, 2015
Messages
998
Not sure why it is returing some values but not others when they clearly exist in the table?:banghead:
Is A_Score and/or Percentage a floating point number?
Floating point numbers are imprecise by definition. So, comparing them for equality may not match exactly.

I would recommend to use a fixed point datatype (e.g. Decimal) limited to the required decimal places. The results of calculations may require explicit conversion/rounding to the required type.

PS/Edit:
Tying grades to exact scores seems not to be good idea to me. Maybe you should rather define a range (From/To) to match a percentage to a grade. That would also automatically solve problems with floating point imprecisions.
 
Last edited:

Access_Help

Registered User.
Local time
Today, 07:03
Joined
Feb 12, 2005
Messages
136
Is A_Score and/or Percentage

I would recommend to use a fixed point datatype (e.g. Decimal) limited to the required decimal places. The results of calculations may require explicit conversion/rounding to the required type.

PS/Edit:
Tying grades to exact scores seems not to be good idea to me. Maybe you should rather define a range (From/To) to match a percentage to a grade. That would also automatically solve problems with floating point imprecisions.


Hi,
Thank you!
I set both fields to fixed to 0 decimal places also rounded the percentage field to 0 decimal places and it seems to have fixed the problem.

Thank you!
 

Access_Help

Registered User.
Local time
Today, 07:03
Joined
Feb 12, 2005
Messages
136
Just one more Q:
How do I concat a second condition which is a text field [GradingSystem_] in the query to lookup the field [GradingSystem] in the lookup table. I currently have:

Grade: DLookUp("[A_Grade]","tbl_att_grade","[A_Score] = " & [Percentage] And "[GradingSystem] = ' " & [GradingSystem_] & " ' ")

however, it is only returning one result for all lookup values.
 

Gasman

Enthusiastic Amateur
Local time
Today, 14:03
Joined
Sep 21, 2011
Messages
14,042
Try
Code:
Grade: DLookUp("[A_Grade]","tbl_att_grade","[A_Score] = " & [Percentage] & " And [GradingSystem] = ' " & [GradingSystem_] & " ' ")
 

vba_php

Forum Troll
Local time
Today, 09:03
Joined
Oct 6, 2019
Messages
2,884
Thanks Gasman but it returns null values.
AH,

I just ran a test on Gasman's solution, and the values that are supposed to returned by the domain function did not appear for me because there was too much whitespace between the single and double quotes in the WHERE argument section. It might be a parsing issue. He posted:
Code:
Grade: DLookUp("[A_Grade]","tbl_att_grade","[A_Score] = " & [Percentage] & " And [GradingSystem] = ' " & [GradingSystem_] & " ' ")
maybe try:
Code:
Grade: DLookUp("[A_Grade]","tbl_att_grade","[A_Score] = " & [Percentage] & " And [GradingSystem] = '" & [GradingSystem_] & "'")
Furthermore, are these 2 fields of yours correct? as in, do they have the right names? (one has a trailing underscore, the other does not):
Code:
[GradingSystem] = ' " & [GradingSystem_]
 

Gasman

Enthusiastic Amateur
Local time
Today, 14:03
Joined
Sep 21, 2011
Messages
14,042
Apologies Access_Help,

I was concentrating on the misplaced AND.:eek:
 

Users who are viewing this thread

Top Bottom