Prev: Ungrouping in DSUM
Next: Using Filter by Form to create a Query and then a Report
From: Elizabeth Reynolds on 29 Jun 2008 17:21
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!




From: Bob Barrows [MVP] on 29 Jun 2008 17:27
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"


From: Elizabeth Reynolds on 29 Jun 2008 17:45
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"
>
>
>
From: Bob Barrows [MVP] on 29 Jun 2008 18:53
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"


From: Elizabeth Reynolds on 29 Jun 2008 21:17
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"
>
>
>
 |  Next  |  Last
Pages: 1 2
Prev: Ungrouping in DSUM
Next: Using Filter by Form to create a Query and then a Report