K
Ken Snell [MVP]
OK - I take it that this is the complete query that you call "Labels by
Ken":
SELECT Nz(c1.[NickName],c1.[FirstName]) & " " & c1.
[LastName] AS [Member Name],
(SELECT Nz(c2.[NickName],c2.[FirstName]) & " " & c2.
[LastName] AS [SONm] FROM Contacts c2
WHERE c2.ContactID = Nz(c1.SignificantOtherID,-9999)) AS [SO Name],
Nz(c1.[CompanyName]," ") AS
[Company Name], Nz(c1.[ContactNickName],c1.
[ContactFirstName]) & " " & c1.[ContactLastName] AS
ContactName, c1.[MailingAddress] & ", "+c1.[OptAddress] AS
Address, c1.City, UCase(c1.[StateOrProvince]) AS State,
c1.PostalCode, c1.SignificantOtherID, c1.SignificantOtherID
FROM Contacts AS c1
WHERE c1.SignificantOtherID > c1.ContactID Or
c1.SignificantOtherID Is Null
ORDER BY c1.PostalCode;
What we'll do is create a second query that uses this query. Let's call this
second query "Labels_Report":
SELECT (IIf(Len(LK.[SO Name] & "")=0,
Trim(LK.[Member Name]) & Chr(13) & Chr10) &
Trim(LK.[Address]) & Chr(13) & Chr(10) &
Trim(LK.[City] & ", " & LK.[State] & " " & LK.[PostalCode])
& Chr(13) & Chr(10),
Trim(LK.[Member Name]) & Chr(13) & Chr10) &
Trim(LK.[SO Name]) & Chr(13) & Chr10) &
Trim(LK.[Address]) & Chr(13) & Chr(10) &
Trim(LK.[City] & ", " & LK.[State] & " " & LK.[PostalCode]))
AS MyLabelContents
FROM Labels by Ken AS LK;
Then in your report, change the Record Source to the Labels_Report query.
Delete three of the textboxes that you currently use. Set the control source
of the one remaining textbox to MyLabelContents field.
--
Ken Snell
<MS ACCESS MVP>
Ken":
SELECT Nz(c1.[NickName],c1.[FirstName]) & " " & c1.
[LastName] AS [Member Name],
(SELECT Nz(c2.[NickName],c2.[FirstName]) & " " & c2.
[LastName] AS [SONm] FROM Contacts c2
WHERE c2.ContactID = Nz(c1.SignificantOtherID,-9999)) AS [SO Name],
Nz(c1.[CompanyName]," ") AS
[Company Name], Nz(c1.[ContactNickName],c1.
[ContactFirstName]) & " " & c1.[ContactLastName] AS
ContactName, c1.[MailingAddress] & ", "+c1.[OptAddress] AS
Address, c1.City, UCase(c1.[StateOrProvince]) AS State,
c1.PostalCode, c1.SignificantOtherID, c1.SignificantOtherID
FROM Contacts AS c1
WHERE c1.SignificantOtherID > c1.ContactID Or
c1.SignificantOtherID Is Null
ORDER BY c1.PostalCode;
What we'll do is create a second query that uses this query. Let's call this
second query "Labels_Report":
SELECT (IIf(Len(LK.[SO Name] & "")=0,
Trim(LK.[Member Name]) & Chr(13) & Chr10) &
Trim(LK.[Address]) & Chr(13) & Chr(10) &
Trim(LK.[City] & ", " & LK.[State] & " " & LK.[PostalCode])
& Chr(13) & Chr(10),
Trim(LK.[Member Name]) & Chr(13) & Chr10) &
Trim(LK.[SO Name]) & Chr(13) & Chr10) &
Trim(LK.[Address]) & Chr(13) & Chr(10) &
Trim(LK.[City] & ", " & LK.[State] & " " & LK.[PostalCode]))
AS MyLabelContents
FROM Labels by Ken AS LK;
Then in your report, change the Record Source to the Labels_Report query.
Delete three of the textboxes that you currently use. Set the control source
of the one remaining textbox to MyLabelContents field.
--
Ken Snell
<MS ACCESS MVP>
SillySally said:Even after your great explanation, alas, I don't get it.
So here's my step by step. My query is "Labels by Ken":
SELECT Nz(c1.[NickName],c1.[FirstName]) & " " & c1.
[LastName] AS [Member Name], (SELECT Nz(c2.[NickName],c2.
[FirstName]) & " " & c2.[LastName] AS [SONm] FROM Contacts
c2....
I used the report wizard using "Labels by Ken" query as
the record source. I set up the label like this on 4
lines:
=Trim([Member Name])
=Trim([SO Name])
=Trim([Address])
=Trim([City] & ", " & [State] & " " & [PostalCode])
(probably not my best label report since I'm still trying
to figure out SO. It seemed to work (except for Company-
more later), and printed the married couple as I wanted.
You said: You would need to use this "calculated" field in
the query that your label report uses as its
recordsource. So I tried to add a column to my query-
Label: IIf([QueryName.SignificantOtherName] Is Null,
QueryName.ContactName & Chr(13) & Chr10) &
QueryName.StreetAddress & Chr(13) & Chr(10) &
QueryName.CityStateZip & Chr(13) & Chr(10),
QueryName.ContactName & Chr(13) & Chr10) &
QueryName.SignificantOtherName & Chr(13) & Chr(10) &
QueryName.StreetAddress & Chr(13) & Chr(10) &
QueryName.CityStateZip) AS MyLabelContents
But I received an error message: I may have entered an
invalid comman or quotation marks. Am I missing some
syntax or did I put it in the wrong place?
I thought in my label report, I could replace all of the
lines with =Label.
What am I doing wrong? Thanks. Sorry.
-----Original Message-----
You would need to use this "calculated" field in the query that your label
report uses as its recordsource (the one you specified to the wizard when
you created the report). I assume that in your current query you have a
field that provides you with the label info? This calculated field is
designed to produce one entire label; if your current query is using four
fields to make the four lines of the label, then you'd replace all four
fields with this one calculated field.
This field doesn't take into account specifically the company item...I don't
know enough about your setup to be able to state with certainty how to do
that, but this example should give you a start towards how to set that up.
If you can provide details, perhaps I can suggest some ways to do this. (For
example, how do you know if a the label is for a company or for a person?)