concatenate problem

  • Thread starter Thread starter Mary
  • Start date Start date
M

Mary

Is it possible to base a concatenation for address labels off a query? A
couple years ago you really helped me fix our address labels with
concatenation, and I have used it for many things but always based off the
same model. Now I need something a little different but I am running into a
roadblock.

Basing the concatenation off the query, I can get the address to show up
properly (one address per concatenation, instead of four times if four people
live at an address), but the names don't show up. There is no error message
or anything, the concatenation field is just blank.
I'd be grateful any help you can offer.

SELECT qryLPNonContactPersonsHomes.MailAddress1,
qryLPNonContactPersonsHomes.MailAddress2,
qryLPNonContactPersonsHomes.MailAddress3,
qryLPNonContactPersonsHomes.MailCity, qryLPNonContactPersonsHomes.MailState,
qryLPNonContactPersonsHomes.MailZip, qryLPNonContactPersonsHomes.Country,
Concatenate("SELECT [IndexName] FROM qryLPNonContactPersonsHomes WHERE
MailAddress1 & MailAddress2 & MailAddress3 & MailCity & MailState & MailZip
=""" & [MailAddress1] & [MailAddress2] & [MailAddress3] & [MailCity] &
[MailState] & [MailZip] & """",", ") AS Concat
FROM qryLPNonContactPersonsHomes
GROUP BY qryLPNonContactPersonsHomes.MailAddress1,
qryLPNonContactPersonsHomes.MailAddress2,
qryLPNonContactPersonsHomes.MailAddress3,
qryLPNonContactPersonsHomes.MailCity, qryLPNonContactPersonsHomes.MailState,
qryLPNonContactPersonsHomes.MailZip, qryLPNonContactPersonsHomes.Country,
Concatenate("SELECT [IndexName] FROM qryLPNonContactPersonsHomes WHERE
MailAddress1 & MailAddress2 & MailAddress3 & MailCity & MailState & MailZip
=""" & [MailAddress1] & [MailAddress2] & [MailAddress3] & [MailCity] &
[MailState] & [MailZip] & """",", ");
 
Your concatenate expression and main query are based on the same query. I
would create a new query like:
qryContactHome
SELECT IndexName, [MailAddress1] & [MailAddress2] & [MailAddress3] &
[MailCity] &
[MailState] & [MailZip] As AddCSZ
FROM qryLPNonContactPersonsHomes;
Then try change the Concatenate to:
Concatenate("SELECT [IndexName] FROM qryContactHome WHERE
AddCSZ = """ & [MailAddress1] & [MailAddress2] & [MailAddress3] & [MailCity]
&
[MailState] & [MailZip] & """ ",", ") AS Concat

If this doesn't work, I would change the query to return a single record and
then set a breakpoint in the code.
 
Hi Duane, I appreciate your response.

I have created the new query as directed, but I am fuzzy on where the new
concatenation expression should go. Do I place it in
qryLPNonContactPersonsHomes? Can I just paste it into the design grid or do I
have to put it in SQL view?

The instruction about inserting a break is beyond my database understanding
so if you have additional time to help me then let's try tackling the first
part.


Duane Hookom said:
Your concatenate expression and main query are based on the same query. I
would create a new query like:
qryContactHome
SELECT IndexName, [MailAddress1] & [MailAddress2] & [MailAddress3] &
[MailCity] &
[MailState] & [MailZip] As AddCSZ
FROM qryLPNonContactPersonsHomes;
Then try change the Concatenate to:
Concatenate("SELECT [IndexName] FROM qryContactHome WHERE
AddCSZ = """ & [MailAddress1] & [MailAddress2] & [MailAddress3] & [MailCity]
&
[MailState] & [MailZip] & """ ",", ") AS Concat

If this doesn't work, I would change the query to return a single record and
then set a breakpoint in the code.

--
Duane Hookom
Microsoft Access MVP


Mary said:
Is it possible to base a concatenation for address labels off a query? A
couple years ago you really helped me fix our address labels with
concatenation, and I have used it for many things but always based off the
same model. Now I need something a little different but I am running into a
roadblock.

Basing the concatenation off the query, I can get the address to show up
properly (one address per concatenation, instead of four times if four people
live at an address), but the names don't show up. There is no error message
or anything, the concatenation field is just blank.
I'd be grateful any help you can offer.

SELECT qryLPNonContactPersonsHomes.MailAddress1,
qryLPNonContactPersonsHomes.MailAddress2,
qryLPNonContactPersonsHomes.MailAddress3,
qryLPNonContactPersonsHomes.MailCity, qryLPNonContactPersonsHomes.MailState,
qryLPNonContactPersonsHomes.MailZip, qryLPNonContactPersonsHomes.Country,
Concatenate("SELECT [IndexName] FROM qryLPNonContactPersonsHomes WHERE
MailAddress1 & MailAddress2 & MailAddress3 & MailCity & MailState & MailZip
=""" & [MailAddress1] & [MailAddress2] & [MailAddress3] & [MailCity] &
[MailState] & [MailZip] & """",", ") AS Concat
FROM qryLPNonContactPersonsHomes
GROUP BY qryLPNonContactPersonsHomes.MailAddress1,
qryLPNonContactPersonsHomes.MailAddress2,
qryLPNonContactPersonsHomes.MailAddress3,
qryLPNonContactPersonsHomes.MailCity, qryLPNonContactPersonsHomes.MailState,
qryLPNonContactPersonsHomes.MailZip, qryLPNonContactPersonsHomes.Country,
Concatenate("SELECT [IndexName] FROM qryLPNonContactPersonsHomes WHERE
MailAddress1 & MailAddress2 & MailAddress3 & MailCity & MailState & MailZip
=""" & [MailAddress1] & [MailAddress2] & [MailAddress3] & [MailCity] &
[MailState] & [MailZip] & """",", ");
Click to expand...
Click to expand...
 
Your query would then look like:
SELECT qryLPNonContactPersonsHomes.MailAddress1,
qryLPNonContactPersonsHomes.MailAddress2,
qryLPNonContactPersonsHomes.MailAddress3,
qryLPNonContactPersonsHomes.MailCity,
qryLPNonContactPersonsHomes.MailState,
qryLPNonContactPersonsHomes.MailZip,
qryLPNonContactPersonsHomes.Country,
Concatenate("SELECT [IndexName] FROM qryContactHome
WHERE AddCSZ = """ & [MailAddress1] & [MailAddress2] &
[MailAddress3] & [MailCity] & [MailState] & [MailZip] &
""" ",", ") AS Concat
FROM qryLPNonContactPersonsHomes
GROUP BY qryLPNonContactPersonsHomes.MailAddress1,
qryLPNonContactPersonsHomes.MailAddress2,
qryLPNonContactPersonsHomes.MailAddress3,
qryLPNonContactPersonsHomes.MailCity, qryLPNonContactPersonsHomes.MailState,
qryLPNonContactPersonsHomes.MailZip, qryLPNonContactPersonsHomes.Country,
Concatenate("SELECT [IndexName] FROM qryContactHome
WHERE AddCSZ = """ & [MailAddress1] & [MailAddress2] &
[MailAddress3] & [MailCity] & [MailState] & [MailZip] & """ ",", ");

--
Duane Hookom
Microsoft Access MVP


Mary said:
Hi Duane, I appreciate your response.

I have created the new query as directed, but I am fuzzy on where the new
concatenation expression should go. Do I place it in
qryLPNonContactPersonsHomes? Can I just paste it into the design grid or do I
have to put it in SQL view?

The instruction about inserting a break is beyond my database understanding
so if you have additional time to help me then let's try tackling the first
part.


Duane Hookom said:
Your concatenate expression and main query are based on the same query. I
would create a new query like:
qryContactHome
SELECT IndexName, [MailAddress1] & [MailAddress2] & [MailAddress3] &
[MailCity] &
[MailState] & [MailZip] As AddCSZ
FROM qryLPNonContactPersonsHomes;
Then try change the Concatenate to:
Concatenate("SELECT [IndexName] FROM qryContactHome WHERE
AddCSZ = """ & [MailAddress1] & [MailAddress2] & [MailAddress3] & [MailCity]
&
[MailState] & [MailZip] & """ ",", ") AS Concat

If this doesn't work, I would change the query to return a single record and
then set a breakpoint in the code.

--
Duane Hookom
Microsoft Access MVP


Mary said:
Is it possible to base a concatenation for address labels off a query? A
couple years ago you really helped me fix our address labels with
concatenation, and I have used it for many things but always based off the
same model. Now I need something a little different but I am running into a
roadblock.

Basing the concatenation off the query, I can get the address to show up
properly (one address per concatenation, instead of four times if four people
live at an address), but the names don't show up. There is no error message
or anything, the concatenation field is just blank.
I'd be grateful any help you can offer.

SELECT qryLPNonContactPersonsHomes.MailAddress1,
qryLPNonContactPersonsHomes.MailAddress2,
qryLPNonContactPersonsHomes.MailAddress3,
qryLPNonContactPersonsHomes.MailCity, qryLPNonContactPersonsHomes.MailState,
qryLPNonContactPersonsHomes.MailZip, qryLPNonContactPersonsHomes.Country,
Concatenate("SELECT [IndexName] FROM qryLPNonContactPersonsHomes WHERE
MailAddress1 & MailAddress2 & MailAddress3 & MailCity & MailState & MailZip
=""" & [MailAddress1] & [MailAddress2] & [MailAddress3] & [MailCity] &
[MailState] & [MailZip] & """",", ") AS Concat
FROM qryLPNonContactPersonsHomes
GROUP BY qryLPNonContactPersonsHomes.MailAddress1,
qryLPNonContactPersonsHomes.MailAddress2,
qryLPNonContactPersonsHomes.MailAddress3,
qryLPNonContactPersonsHomes.MailCity, qryLPNonContactPersonsHomes.MailState,
qryLPNonContactPersonsHomes.MailZip, qryLPNonContactPersonsHomes.Country,
Concatenate("SELECT [IndexName] FROM qryLPNonContactPersonsHomes WHERE
MailAddress1 & MailAddress2 & MailAddress3 & MailCity & MailState & MailZip
=""" & [MailAddress1] & [MailAddress2] & [MailAddress3] & [MailCity] &
[MailState] & [MailZip] & """",", ");
Click to expand...
Click to expand...
Click to expand...
 
Back
Top