Format of available fields

[Laurel]

I have a form based on a SQL statement. The SQL statement retrieves two
tables. In the "Existing Fields" list, the names of fields from the first
table have no prefix, and the names of the fields from the second table have
this form.... tablename_field_name. I set it up some time ago and just
recently added fields from the second table. The new fields have to form
tablename.field_name. A dot between table and field instead of an
underscore. Why is this? Does it have ramifications?

(Note - I had a very difficult time adding the new fields. It would look
like they were added OK in the SQL statement, but when I returned to the
form they were not in the "Existing Fields" list, and when I returned to the
SQL statement they were not there. This was true even if I explicitly saved
the SQL statement before closing it. I think I solved the problem by
picking the table and then the field instead of other methods, like clicking
the field list and choosing from all the fields in both tables, or double
clicking on the field in the graphic at the top.)

 

[Arvin Meyer MVP]

I have a form based on a SQL statement. The SQL statement retrieves two
tables. In the "Existing Fields" list, the names of fields from the first
table have no prefix, and the names of the fields from the second table
have this form.... tablename_field_name. I set it up some time ago and
just recently added fields from the second table. The new fields have to
form tablename.field_name. A dot between table and field instead of an
underscore. Why is this? Does it have ramifications?

Yes, without the additional identifiers, how would Access (or anyone for
that matter) know which table the data came from.

(Note - I had a very difficult time adding the new fields. It would look
like they were added OK in the SQL statement, but when I returned to the
form they were not in the "Existing Fields" list, and when I returned to
the SQL statement they were not there. This was true even if I explicitly
saved the SQL statement before closing it. I think I solved the problem
by picking the table and then the field instead of other methods, like
clicking the field list and choosing from all the fields in both tables,
or double clicking on the field in the graphic at the top.)

Try using an alias for the field name like this in a query column (or SQL
statement):

Note: Description

The SQL might look something like:

Select Description As Note From MyTable;

 

[John W. Vinson]

I have a form based on a SQL statement. The SQL statement retrieves two
tables. In the "Existing Fields" list, the names of fields from the first
table have no prefix, and the names of the fields from the second table have
this form.... tablename_field_name. I set it up some time ago and just
recently added fields from the second table. The new fields have to form
tablename.field_name. A dot between table and field instead of an
underscore. Why is this? Does it have ramifications?

Absolutely. The . delimiter *is a delimiter* separating the name of a table
from the name of a field in that table. The underscore is just a character in
a name; Access doesn't treat it any differently than any alphanumeric
character. If there is a field named Lastname in the table MyTable, then the
syntax for referencing it would be

MyTable.LastName

If you reference MyTable_LastName Access will look for a field in *any one* of
the tables included in the query of that name - literally MyTable_LastName. It
probably won't find one and will cause an error.

(Note - I had a very difficult time adding the new fields. It would look
like they were added OK in the SQL statement, but when I returned to the
form they were not in the "Existing Fields" list, and when I returned to the
SQL statement they were not there. This was true even if I explicitly saved
the SQL statement before closing it. I think I solved the problem by
picking the table and then the field instead of other methods, like clicking
the field list and choosing from all the fields in both tables, or double
clicking on the field in the graphic at the top.)

A field will disappear if it's not used in the query: that is, if it's not in
the SELECT clause (which it won't be if the Show checkbox is unchecked in the
query grid), it has no criteria (nothing on the WHERE line in the grid), and
it's not sorted (nothing on the Order By line in the grid).

Your post might have been more helpful if you had... erm... actually posted
the SQL that's causing you trouble. It's a bit hard to see from here.