G
Guest
I am teaching myself Access and SQL as I need it on the job, and I want to
thank everyone who has contributed to this discussion group for all of your
very useful guidance. So far I haven't been able to find an answer to a
problem I'm having with an Access 2000 query and report when I have 2 lines
of data in a single field.
For a telephone list, I am running a union query to combine names and phone
extensions from 2 tables, named RTPattorneys and RTPStaff. Most of the names
in the RTPStaff table are associated with one corresponding phone extension
called SRTPExt. However, some of the names in the RTPStaff table have 2
extensions, SRTPExt and SRALExt. When a name has 2 extensions, I need the
query to pick up both of them and print them on separate lines. The SQL
statement below returns the data I need. When there are 2 extensions for one
name, it prints the name twice in a single field containing 2 lines and the
extensions in a single field containing 2 lines.
SELECT [ALastName] & ", " & [AFirstName] AS AlphaName,[AExt] AS AlphaExt
FROM [tblRTPAttorneys]
UNION SELECT IIf([SRALExt]<>"",[SLastName] & ", " & [SFirstName]& " (RTP)" &
Chr(13) & Chr(10) &[SLastName] & ", " & [SFirstName]& " (RAL)",[SLastName] &
(", "+[SFirstName])) AS AlphaName,IIf([SRALExt]<>"",[SRTPExt] & Chr(13) &
Chr(10) & [SRALExt],[SRTPExt]) AS AlphaExt
FROM [tblRTPStaff]
The problem occurs when I try to format the report that uses this query as
its data source. I want to print a simple list of all names, with their
extensions, in ascending order. My report has 3 fields: AlphaName (textbox),
DotLeaders (label), AlphaExt (textbox). My report formatting is inconsistent
because of the 2-line fields. This is how the report prints now:
LastName1, FirstName 1 ......................... RTPExt.1
LastName2, FirstName 2 ......................... RTPExt.2
LastName3, FirstName 3 (RTP) RTPExt.3
LastName3, FirstName 3 (RAL) RALExt.3
LastName4, FirstName 4 ......................... RTPExt.4
As you can see, the row spacing is not consistent, and Stephen Lebans'
fantastic dot leaders (thank you for saving me untold hours of work on that
one, Stephen!) disappear when I have the 2-line fields.
Is there a way for me to re-write the query so that each line of the 2-line
fields will print in its own separate field? I think that would solve my
problem but if that's not the best solution, I'd be grateful for any other
suggestions you may have. My goal is for the report to print like this:
LastName1, FirstName 1 ......................... RTPExt.1
LastName2, FirstName 2 ......................... RTPExt.2
LastName3, FirstName 3 (RTP)..................RTPExt.3
LastName3, FirstName 3 (RAL)..................RALExt.3
LastName4, FirstName 4 ......................... RTPExt.4
Thank you in advance.
thank everyone who has contributed to this discussion group for all of your
very useful guidance. So far I haven't been able to find an answer to a
problem I'm having with an Access 2000 query and report when I have 2 lines
of data in a single field.
For a telephone list, I am running a union query to combine names and phone
extensions from 2 tables, named RTPattorneys and RTPStaff. Most of the names
in the RTPStaff table are associated with one corresponding phone extension
called SRTPExt. However, some of the names in the RTPStaff table have 2
extensions, SRTPExt and SRALExt. When a name has 2 extensions, I need the
query to pick up both of them and print them on separate lines. The SQL
statement below returns the data I need. When there are 2 extensions for one
name, it prints the name twice in a single field containing 2 lines and the
extensions in a single field containing 2 lines.
SELECT [ALastName] & ", " & [AFirstName] AS AlphaName,[AExt] AS AlphaExt
FROM [tblRTPAttorneys]
UNION SELECT IIf([SRALExt]<>"",[SLastName] & ", " & [SFirstName]& " (RTP)" &
Chr(13) & Chr(10) &[SLastName] & ", " & [SFirstName]& " (RAL)",[SLastName] &
(", "+[SFirstName])) AS AlphaName,IIf([SRALExt]<>"",[SRTPExt] & Chr(13) &
Chr(10) & [SRALExt],[SRTPExt]) AS AlphaExt
FROM [tblRTPStaff]
The problem occurs when I try to format the report that uses this query as
its data source. I want to print a simple list of all names, with their
extensions, in ascending order. My report has 3 fields: AlphaName (textbox),
DotLeaders (label), AlphaExt (textbox). My report formatting is inconsistent
because of the 2-line fields. This is how the report prints now:
LastName1, FirstName 1 ......................... RTPExt.1
LastName2, FirstName 2 ......................... RTPExt.2
LastName3, FirstName 3 (RTP) RTPExt.3
LastName3, FirstName 3 (RAL) RALExt.3
LastName4, FirstName 4 ......................... RTPExt.4
As you can see, the row spacing is not consistent, and Stephen Lebans'
fantastic dot leaders (thank you for saving me untold hours of work on that
one, Stephen!) disappear when I have the 2-line fields.
Is there a way for me to re-write the query so that each line of the 2-line
fields will print in its own separate field? I think that would solve my
problem but if that's not the best solution, I'd be grateful for any other
suggestions you may have. My goal is for the report to print like this:
LastName1, FirstName 1 ......................... RTPExt.1
LastName2, FirstName 2 ......................... RTPExt.2
LastName3, FirstName 3 (RTP)..................RTPExt.3
LastName3, FirstName 3 (RAL)..................RALExt.3
LastName4, FirstName 4 ......................... RTPExt.4
Thank you in advance.