Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2015

    Unanswered: Dlookup in expression returns a #error

    Hi guys,

    I'm sorry I know this has been posted about a lot, but I'm tearing my hair out and I don't really understand this one.

    Fair to say I'm an Access newbie this year - I'm trying to build a system which can read student results and assign their alphanumeric grade a number in order to help with grade calculation.

    I have 1 table, MFL Data, which contains all of the student data. A second table LevelCalc contains the equivalences needed for the calculations.

    I a separate form, Student_Screen_French, where results are to be displayed I have a textboxes to represent some of the student information, including their Listening grade. I want the textbox below, using an expression, to calculate the numeric equivalence.

    I have entered, as an expression:

    =DLookUp("LevelVal","LevelCalc","[LevelCalc]![Level] = " & [Form]![Term 1 Listening])

    to the best of my understanding, this seeks to display FIELD 'Level Val' (numerical value) FROM table 'LevelCalc' WHERE 'Level' in 'LevelCalc' is equal to 'Term 1 Listening' on the form. The textbox outputs a #Error, and I can't figure out why.

    Additional information: The form recordset is already subject to some criteria in its select statement, so is limited to just one specific student (and thus record) at a time.

    Any thoughts?

    Many thanks,


  2. #2
    Join Date
    Apr 2004
    Derbyshire, UK
    Provided Answers: 2

    I think thing you need this
    =DLookUp("LevelVal","LevelCalc","Level = " & [Form]![Term 1 Listening])
    The criteria argument should have the Field name only. The table name is not required as it is specified in the second argument.

    Also if [Level] is not numeric you need to include text delimiters like this
    =DLookUp("LevelVal","LevelCalc","Level = '" & [Form]![Term 1 Listening] & "'")


  3. #3
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    string values should, as MiketheBike suggests be delimited with a pair of ' or " symbols.
    unless you happen to have a form called form then I'm dubious about ...[Form]![Term 1 Listening]...
    assumign tht you are calling this code from within the form that contains the control Term 1 Listening then

    =DLookUp("LevelVal","LevelCalc","Level = '" & [Term 1 Listening] & "'")
    may be a smarter call.

    moving on from that a couple of observations you are maming life harder for yourself by using reserved symbols, in this case spaces. learn what reserved words and symnboils exist for your version of Access and try no to use 'em

    whenver I see a column / control calledd something X it alwasy makes me suspicious that the design is not normalised
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts