Thanks Bob,
I just got the chance to sit back down and work on this again. I have done
what you said but now I am getting the following message: "Type mismatch in
expression". Below is what the expression has in it now.
Address: [Street Address] & " " & [City] & " " & States.[Abb] & " " & [Zip]
And here is the sql statement:
SELECT [BP Contacts].[Male First Name], [BP Contacts].[Male Last Name], [BP
Contacts].[Male Bereavement Type], [BP Contacts].[Male Other], [BP
Contacts].[Female First Name], [BP Contacts].[Female Last Name], [BP
Contacts].[Female Bereavement Type], [BP Contacts].[Female Other], [Street
Address] & " " & [City] & " " & [States].[Abb] & " " & [Zip] AS Address, [BP
Contacts].Zip, [BP Contacts].[Home Phone], [BP Contacts].[Cell Phone], [BP
Contacts].[Email Address], [BP Contacts].[Child 1], [BP Contacts].[Child 2],
[BP Contacts].[Child 3], [BP Contacts].Siblings, [BP Contacts].[Member Info]
FROM States INNER JOIN [BP Contacts] ON States.Abb = [BP Contacts].State;
What am I missing now?
Bob Barrows said:
OK, thanks for confirming my initial conclusions about the table.
Here is the thing: as far as the database engine (Jet) knows, the State
field in BP Contacts contains the ID of the state. In order to get the
appropriate Abb value, you need to link to the States table in the query.
Open the query in Design View. Right-click the query window and select "Show
Table". Select the States table and click OK. Click-and-drag the State field
in the BP Contacts graphic to the ID field in the States graphic to
establish the link. Now, change the expression to:
Address: [Street Address] & " " & [City] & " " & States.[Abb] & " " & [Zip]
Elizabeth said:
Here is what the fields contain:
ID State Abb
1 ALABAMA AL
2 ALASKA AK
This is what the SQL statement shows:
SELECT [BP Contacts].[Male First Name], [BP Contacts].[Male Last
Name], [BP Contacts].[Male Bereavement Type], [BP Contacts].[Male
Other], [BP Contacts].[Female First Name], [BP Contacts].[Female Last
Name], [BP Contacts].[Female Bereavement Type], [BP Contacts].[Female
Other], [BP Contacts].[Street Address], [Street Address] & " " &
[City] & " " & [State] & " " & [Zip] AS Address, [BP
Contacts].[State], [BP Contacts].[Zip], [BP Contacts].[Home Phone],
[BP Contacts].[Cell Phone], [BP Contacts].[Email Address], [BP
Contacts].[Child 1], [BP Contacts].[Child 2], [BP Contacts].[Child
3], [BP Contacts].[Siblings], [BP Contacts].[Member Info] FROM [BP
Contacts];
:
What is the State field supposed to contain? And what is the Abb
field supposed to contain? Could you show some examples (just a
couple rows)? I assume it's the ID field whose contents are being
displayed as the result of your formula?
Could you show us the sql statement for your query? Open your query
in Design View. Then do one of the following:
View | SQL View
Click the appropriate toolbar button
Right-click in the query window and select SQL View
This will display the sql statement being generated by your actions
in the Design View. Just copy/paste it into your reply.
Elizabeth Reynolds wrote:
The table name is:
States
and it contains the following fields:
ID
State
Abb
:
Elizabeth Reynolds wrote:
I am working in a query that I want to put the address, city,
state
and zip into one field using the expression builder. When I do
this the state which is coming from a lookup value from a table of
states, it shows the ID # of the state rather than the two digit
code it should show. How do I correct this in the expression
builder?
This is what I am using in the expression buildier:
Address: [Street Address] & " " & [City] & " " & [State] & " " &
[Zip]
Thank you for any help with this. I have been going in circles all
day!
What are the names of the fields in your states table and what do
they contain?
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap
so
I don't check it very often. If you must reply off-line, then
remove
the "NO SPAM"
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so
I don't check it very often. If you must reply off-line, then remove
the "NO SPAM"
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
