Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2009

    Problem with subquery

    I'm trying to do something that seems trivial to me, but I can't see to get it to work correctly. I am building a database for a university academic advisement center. One of the forms needs to be able to display the student's ID number, name, and information concerning them changing majors.

    The data for the ID and name are not entered on this form, they are only there for the user to reference. The only piece of data related to the user that will be stored in my MajorChange table is the student's ID number.

    The student's ID number is passed to this form via a temporary variable (StudentEagleID) created by a macro when the change major button is clicked. This information is passed successfully and loads into the ID field of my MajorChange form.

    I am trying to use a subquery in Expression Builder to call the student's name from the Students table to populate the name fields (Last, First, MI). The syntax of my expression is as follows:

    =(SELECT [Students].[LastName] FROM [Students] WHERE [Students].[EagleID]= [TempVars]![StudentEagleID] )
    However, when the form loads I get #Name? in the last name field.

    I can copy and paste the SQL query into a query and it executes perfectly, returning the desired data. Is there something that I'm missing here?

  2. #2
    thhui is offline Competent Performer
    Windows XP Access 2002 (version 10.0)
    Join Date
    Feb 2009
    In expression,
    use dlookup function instead of
    sql select statement

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Subquery returns Memo
    By Brainmart in forum Queries
    Replies: 0
    Last Post: 03-09-2009, 07:32 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Other Forums: Microsoft Office Forums