Calculated Field in Dlookup (1 Viewer)

[Access_Help]

I am using a DLOOKUP which is returning no values, wondering if it is to do with the use of a calculated field:

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

I wish to return the A_Score from the table tbl_att_grade where the A_Grade from the table tbl_att_grade matches the calculated field Grade (which also uses a DLOOKUP).

 

[Gasman]

You have a space either end of your string values

 

[bastanu]

Is Grade text or numeric? Also remove the extra spaces between the single and double quotes in three places in your expression.
Cheers,
Vlad

 

[plog]

There's a lot of wrong to unpack here:

1. DLookups have no place in a query. Instead you JOIN (https://www.w3schools.com/sql/sql_join_inner.asp) data sources appropriately using SQL and retrieve the data you want that way.

2. It seems you are storing data in field names. You should not prefix or suffix field names with values (A_Score, B_Score, A_Grade, B_Grade...). In those instances A and B represent data and should be in their own fields. Most likeyly in this case they would be values in the field [GradeType]. I think you should read up on normalization as well (https://en.wikipedia.org/wiki/Database_normalization).

3. You cannot calculate a field and then use it in the same query. This is moot because of #1 above, but a good thing to know. Instead you either save the query that calculates the value and then build a new query using it as a source where you will have access to the calculated value; or you use algebraic replacement instead of the calculated field name:

A = B + C

D = A + E

D = (B + C) + E

I really think the solution to all of this is to fix your tables, you should not be storing values in field names. The issue you posted about is a symptom of this larger issue and that is the one you should concentrate on.