Results 1 to 3 of 3
  1. #1
    Join Date
    May 2004

    Unanswered: Access 97 questions...

    I have three questions that are Access 97 specific.

    1. I would like to do a subquery in my FROM clause, but cannot seem to get the code to work. I.,e "SELECT * FROM (SELECT * FROM Customer) as CS". This is very typical SQL but, I keep getting a error about my FROM clause. Any tricks to making this work? I can do it if I save my subquery as a separate query, but that seems like a waste, especially when many times my subqueries are written dynamically in code.

    2. Is there a trick to accessing the AbsoultePosition property of a record in a forms recordset? I.e, if my form is based on a multiple record recordset, I need a field on my form that identifies the AbsolutePosition +1 of each record as a counter.

    3. SQL supports a CASE statement in a SELECT format. I.e. SELECT Customer,CountryName=
    WHEN country="UK" then "United Kingdom"
    WHEN country ="US" then "United States"
    WHEN country ="AU" then "Australia"
    FROM Customer;

    Is there a way to do this in Access 97 without using IIF?

    Thanks in advance for everyone's help with this.


  2. #2
    Join Date
    Apr 2002
    1. Can't help. Sorry.

    2. Have you checked online help? There are some examples there.

    3. Assuming there is a table (ex. tblMaster) with a Country code field that would contain "UK" or "US", create a table tblCountry with Country code and Country Name, create a join on Country code and create a query with both tables and the necessary fields.


  3. #3
    Join Date
    May 2004

    Thanks. I've been working on a join to another table, but because it has a range of values, the join is not as clean as I would like.

    Yes, I have check the help and MS online site.



Posting Permissions

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