Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Sep 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    80
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Query #Name? (Access 97 SR2 W2K)

    I have a field in a table that contains a 6 to 7 char ID number. The ID number has the first two chars a letters and the last 5-6 chars as numbers. I want to track the last number in the sequence, so I created the following query and saved it as MaxNumber.

    SELECT Max(Int(IIf(Len([Sample Numbers raw]![SampleID])>6,Right([Sample Numbers raw]![SampleID],5),Right([Sample Numbers raw]![SampleID],4)))) AS Expr1
    FROM [Sample Numbers raw];

    This works fine when I run the query. I wanted to display the number on a form, so I used the Expression Builder to refer to the query on a text box's Control Source. I get the #Name? error when I run the form. The form of the reference is:

    =[MaxNumber]!Expr1

    My ultimate goal is to have this update when a new record is posted or deleted on the form. But I first must figure out what simple thing I am missing in implemeting this.

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Query #Name? (Access 97 SR2 W2K)

    Even though the expression builder lets you build expressions that refer to queries by name, they don't work.
    Instead you have to use a dlookup
    = dlookup("[expr1]","MaxNumber")
    Regards
    John



  3. #3
    Star Lounger
    Join Date
    Sep 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    80
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Query #Name? (Access 97 SR2 W2K)

    Thanks. I had looked at this function after seeing it in several similarly related posts, but I did not have a complete understanding of the syntax to implement it in this fashion. Seems the description and examples in help do not point to this implementation.

Posting Permissions

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