Thanks, Sam, but I can't get it to work.
My report is built on a query and is essentially a sign-in sheet for a
seminar. Among other information, I want the page header to show the event
name, location, and start date. That information is drawn from an events
table (linked to a State Names table which provides the state abbreviation
for the seminar's address).
I'm also drawing from an Attendees Table and a Registration Table. The
Events Table has a one-to-many relationship with the Registration Table. The
Attendee Table has a one-to-many relationship with the Registration Table.
Each attendee has an address with a state; and each event has an address
with a state. So I included a StateNameID in the Attendees Table and in the
Events Table and linked each to a State Names table. It has 3 columns (which
is redundant, I know, but nonetheless): column 1: autonumber named
StateNameID; column 2: the state name typed out in full; column 3: the
2-letter state name abbreviation called StateNameAbbreviation. In all tables
and forms requiring the state to be filled in, I've used list boxes and the
2-letter abbreviation.
Because multiple tables draw from it, I've included the State Names Table in
the query. But, the relevant information is that I'm trying to get the event
location on one line. To do that I'm drawing from the following fields:
SiteName
SiteAddress (the street address)
SiteCity
StateNameID (from the Events Table)
SiteZipCode
My query holds the StateNameID from the Events table set to show and the
StateNameAbbreviation from the State Names table set to not show. I'm not
sure if this is correct, as I'm very new to Access. The expression I've typed
in one control box is:
= [SiteName] & ", " & [SiteAddress] & ", " & [SiteCity] & ", "
[Events.StateNameID] & " " [SiteZipCode]
It all works except for the state location. I'd appreciate any further
thoughts you have about how to fix it. ~ amjjam
Sam said:
It's not entirely clear how your report is set up. However, it is possible to
display any column in a combobox or drop-down with the following syntax:
[NameofDropDown].column(1)
where "1" refers to the particular column desired. It is base zero which
means that the first column is .column(0), second column is .column(1)... etc.
HTH
Sam
amjjam said:
Yes. I have the appropriate tables in the query. I'm not sure about the
syntax in the expression. I have the StateNameID from the Events table set to
not show and the StateNameAbbreviation from the State Names Table set to
show. They are linked by the StateNameID field.
Wayne-I-M said:
Hi
In the query the report is based on make sure your have the table that the
State is in and then insert the Full State from this table. You will than be
able to insert this data into your report.
HTH
--
Wayne
Trentino, Italia.
:
Hi,
I've created a report in which I would like to have a conference facility's
address run on one line. I've entered the expression:
= [SiteName] & ", " & [SiteAddress] & ", " & [SiteCity] & ", "
[Events.StateNameID] & " " [SiteZipCode]
It all works fine except for the State. The events table has a drop down
list and the events form has a combo box for 2 letter state name
abbreviations. Is there a way to get the report to read the value of the
state given that it's drawn from a field that requires reference to a State
Names table? Thanks.
