Expression Builder

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!

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"

The table name is:

States

and it contains the following fields:

ID
State
Abb

"Bob Barrows [MVP]" wrote:

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"

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

"Bob Barrows [MVP]" wrote:

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"

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];



"Bob Barrows [MVP]" wrote:

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

"Bob Barrows [MVP]" wrote:

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"

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 Reynolds wrote:
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];



"Bob Barrows [MVP]" wrote:

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

"Bob Barrows [MVP]" wrote:

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"

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 [MVP]" wrote:

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 Reynolds wrote:
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];



"Bob Barrows [MVP]" wrote:

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

"Bob Barrows [MVP]" wrote:

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"

Well, I fixed it after making a lot of changes that did not work. I finally
went in and just changed the "State" field in the "States" table to be "State
Name".

Thanks so much for getting me started in the right direction!

"Elizabeth Reynolds" wrote:

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 [MVP]" wrote:

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 Reynolds wrote:
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];



"Bob Barrows [MVP]" wrote:

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

"Bob Barrows [MVP]" wrote:

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"