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"
Is there a way to do this in Access 97 without using IIF?
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.