DLookup Function in query - pulls wrong values

[Guest]

Hello,

I have one table that houses questions (Question1, Question2, etc) and each
one has a value associated with it (1,3,2...etc). I have another table "Score
Scale" that has those values 1,2,3,4,5 and each of those values has a
corresponding "Score" to it. 1=100, 2=90, etc etc.

In a query, I want the values in table 1 (1,2,3) to be looked up in Table 2
(Score Scale) to find out the score. So far I have this dblookup code in my
query as a calculated field:

Score2: DLookUp("[Score]","Score Scale",[Question 2])

where question 2 is a value of 2 in this example.

I use this dlookup and it returns a 100 in Score2 instead of a 90, what it
should be. I am confused and don't know how to correct this issue.

Please help.

Thanks,
MN

 

[Michel Walsh]

Score2: DLookUp("[Score]","Score Scale","Question=" & [Question 2])




The new criteria, once the concatenation would be done, would be, as
example: " Question = 2 ".



Your first formulation was only mentioning a value, as criteria, and since
FALSE =0, and TRUE is anything else, supplying the value 2 was understood
as 'TRUE' and the first record was returned. In fact, if more than one
record matches the criteria, DLookup behaves like DFirst, returning the
first record 'seen' by the database (and since records may move around, due
to compaction, that can be, 'any' of the record that matches the criteria,
for all practical purposes).




Vanderghast, Access MVP

 

[Guest]

Thank you very much -- that worked!

Score2: DLookUp("[Score]","Score Scale","Question=" & [Question 2])




The new criteria, once the concatenation would be done, would be, as
example: " Question = 2 ".



Your first formulation was only mentioning a value, as criteria, and since
FALSE =0, and TRUE is anything else, supplying the value 2 was understood
as 'TRUE' and the first record was returned. In fact, if more than one
record matches the criteria, DLookup behaves like DFirst, returning the
first record 'seen' by the database (and since records may move around, due
to compaction, that can be, 'any' of the record that matches the criteria,
for all practical purposes).




Vanderghast, Access MVP

Hello,

I have one table that houses questions (Question1, Question2, etc) and
each
one has a value associated with it (1,3,2...etc). I have another table
"Score
Scale" that has those values 1,2,3,4,5 and each of those values has a
corresponding "Score" to it. 1=100, 2=90, etc etc.

In a query, I want the values in table 1 (1,2,3) to be looked up in Table
2
(Score Scale) to find out the score. So far I have this dblookup code in
my
query as a calculated field:

Score2: DLookUp("[Score]","Score Scale",[Question 2])

where question 2 is a value of 2 in this example.

I use this dlookup and it returns a 100 in Score2 instead of a 90, what it
should be. I am confused and don't know how to correct this issue.

Please help.

Thanks,
MN