Align text in concatenated string using tab (or ?)

[Guest]

Pardon the lengthy description!!!
I have a report derived from a query containing a Location, a Date and 16
Fields of data. The 16 fields are review categories and can be "X" (no
problem) or one of 4 other characters. I display the 'other' categories
(only) by using Invisible Text boxes for [Field1], [Field2] etc., then, in
another invisible text box, I do an Iif to test for "X" and display the field
name and value.
Example: =Iif([Field1]="X","","Field 1 Name "&[Field1]&Chr(13)&Chr(10))
Finally, I concatenate all 16 fields together to display ONLY those
'problem' fields for the record one under the other. The report also groups
by location, date etc.
PROBLEM:
In the Iif statement all the Field names are of different lengths and I want
the values to line up vertically on the report. Is there a way to put a 'Tab'
character or ? at the end of the Field name and before the value. I can play
with the number of spaces I put between the quote marks and get close but
that is clumsy and doesn't really line them up exactly anyway.

 

[Duane Hookom]

How set are you with your current table structure? It sounds a bit
un-normalized but I could be wrong. I expect a normalized structure would
remove all issues with the use of a subreport.

I could be wrong but it would help to get a better understanding of your
table structure.

 

[Marshall Barton]

Pardon the lengthy description!!!
I have a report derived from a query containing a Location, a Date and 16
Fields of data. The 16 fields are review categories and can be "X" (no
problem) or one of 4 other characters. I display the 'other' categories
(only) by using Invisible Text boxes for [Field1], [Field2] etc., then, in
another invisible text box, I do an Iif to test for "X" and display the field
name and value.
Example: =Iif([Field1]="X","","Field 1 Name "&[Field1]&Chr(13)&Chr(10))
Finally, I concatenate all 16 fields together to display ONLY those
'problem' fields for the record one under the other. The report also groups
by location, date etc.
PROBLEM:
In the Iif statement all the Field names are of different lengths and I want
the values to line up vertically on the report. Is there a way to put a 'Tab'
character or ? at the end of the Field name and before the value. I can play
with the number of spaces I put between the quote marks and get close but
that is clumsy and doesn't really line them up exactly anyway.

You can not align things in a single text box, the only
special characters that are recognized in a text string is
the new line sequence.

Given your unnormailzed data structure, I think the best you
can do is reverse the values so that variable length stuff
is at the end of the line:

Iif([Field1]="X", "", [Field1] & " " & [Field1].Name &
Chr(13)&Chr(10))

 

[Guest]

I inherited the table but it serves as a 'checklist' of incoming financial
reports from facilities in the field (about 200 facilities). The table allows
the audit folks to evaluate each of 16 criteria of each report - an "X"
indicates the report is compliant and any one of 4 other characters (F,R,P,O)
indicate some problem (the letters are just codes) or an N/A. The record also
has a transaction date, facilitiy number, etc. The input form for the data
puts a date and record number with the record and then a report is generated
daily which is then emailed to the affected facilities which lists all of the
discrepancies for the transaction date range selected for the report. The
"X"s are important to the auditors because it tells them the item was
included and correct. The original report was a typical report with the field
names across the top and detail record values underneath. Problem was it was
way too cluttered and I am tasked with a redesign. It sounds like what I
would like to do is impossible (align in the middle of a text box).

Thanks for the interest anyway.
--
R&D Consulting

How set are you with your current table structure? It sounds a bit
un-normalized but I could be wrong. I expect a normalized structure would
remove all issues with the use of a subreport.

I could be wrong but it would help to get a better understanding of your
table structure.

--
Duane Hookom
MS Access MVP

Pardon the lengthy description!!!
I have a report derived from a query containing a Location, a Date and 16
Fields of data. The 16 fields are review categories and can be "X" (no
problem) or one of 4 other characters. I display the 'other' categories
(only) by using Invisible Text boxes for [Field1], [Field2] etc., then, in
another invisible text box, I do an Iif to test for "X" and display the
field
name and value.
Example: =Iif([Field1]="X","","Field 1 Name "&[Field1]&Chr(13)&Chr(10))
Finally, I concatenate all 16 fields together to display ONLY those
'problem' fields for the record one under the other. The report also
groups
by location, date etc.
PROBLEM:
In the Iif statement all the Field names are of different lengths and I
want
the values to line up vertically on the report. Is there a way to put a
'Tab'
character or ? at the end of the Field name and before the value. I can
play
with the number of spaces I put between the quote marks and get close but
that is clumsy and doesn't really line them up exactly anyway.

 

[Duane Hookom]

If you provided the table structure, a few sample records, and how you want
them displayed in a report, we might be able to help.

I think a union query would work but you didn't provide the structure
information.

--
Duane Hookom
MS Access MVP
--

I inherited the table but it serves as a 'checklist' of incoming financial
reports from facilities in the field (about 200 facilities). The table
allows
the audit folks to evaluate each of 16 criteria of each report - an "X"
indicates the report is compliant and any one of 4 other characters
(F,R,P,O)
indicate some problem (the letters are just codes) or an N/A. The record
also
has a transaction date, facilitiy number, etc. The input form for the data
puts a date and record number with the record and then a report is
generated
daily which is then emailed to the affected facilities which lists all of
the
discrepancies for the transaction date range selected for the report. The
"X"s are important to the auditors because it tells them the item was
included and correct. The original report was a typical report with the
field
names across the top and detail record values underneath. Problem was it
was
way too cluttered and I am tasked with a redesign. It sounds like what I
would like to do is impossible (align in the middle of a text box).

Thanks for the interest anyway.
--
R&D Consulting

How set are you with your current table structure? It sounds a bit
un-normalized but I could be wrong. I expect a normalized structure would
remove all issues with the use of a subreport.

I could be wrong but it would help to get a better understanding of your
table structure.

--
Duane Hookom
MS Access MVP

Pardon the lengthy description!!!
I have a report derived from a query containing a Location, a Date and
16
Fields of data. The 16 fields are review categories and can be "X" (no
problem) or one of 4 other characters. I display the 'other' categories
(only) by using Invisible Text boxes for [Field1], [Field2] etc., then,
in
another invisible text box, I do an Iif to test for "X" and display the
field
name and value.
Example: =Iif([Field1]="X","","Field 1 Name
"&[Field1]&Chr(13)&Chr(10))
Finally, I concatenate all 16 fields together to display ONLY those
'problem' fields for the record one under the other. The report also
groups
by location, date etc.
PROBLEM:
In the Iif statement all the Field names are of different lengths and I
want
the values to line up vertically on the report. Is there a way to put a
'Tab'
character or ? at the end of the Field name and before the value. I can
play
with the number of spaces I put between the quote marks and get close
but
that is clumsy and doesn't really line them up exactly anyway.

 

[Guest]

Duane,
The table looks like this (I'll not include all of the fields for brevity)

Record Number - AutoNumber (Primary)
EntryDate - Date/Time (date of transaction)
CSC - Number (Facility)
DSJ - Text (1st criteria)
CRE - Text (2nd criteria)
ACH - Text (3rd criteria)
CC_CRE - Text (4th criteria)
ACCT_Zero - Text (5th criteria)

and so forth up to 16 different criteria. The list is to validate financial
transactions posted to the main office contain all of the required
documentation and are correct. They are graded as Compliant ("X"), Not
Applicable ("N/A"), or Failed (4 different codes).
The table is kept for a permanent record for auditing and site evaluation
and a Report sent to each facility for their info. This report is what I am
working on. I select a date range (entered by the operator) and test for any
records with any non-compliant field (a record may fail in one or, I guess,
all categories). I then generate the report but only print (actually email vs
print) those records with non-compliant field(s) AND only the fields which
are non-compliant.
I have all of that working ---- the last problem is only a formatting one,
I would like for it to look more professional by all of the data 'left
aligned'.
See earlier of mine for how I print the final result.
Sample printout (2 records)
CSC 1904
11/11/2005
DSJ F
CC-CRE R

11/12/2005
CRE R

CSC 2301
11/10/2005
ACCT_ZERO F

Hope that helps. The R, F, O etc don't line up properly. And no, they won't
let me change the 'labels'. I have also abbreviated the labels as some of
them have 10 to 15 characters while others have only 4 or 5.

Thanks for any thoughts.



--
R&D Consulting

If you provided the table structure, a few sample records, and how you want
them displayed in a report, we might be able to help.

I think a union query would work but you didn't provide the structure
information.

--
Duane Hookom
MS Access MVP
--

I inherited the table but it serves as a 'checklist' of incoming financial
reports from facilities in the field (about 200 facilities). The table
allows
the audit folks to evaluate each of 16 criteria of each report - an "X"
indicates the report is compliant and any one of 4 other characters
(F,R,P,O)
indicate some problem (the letters are just codes) or an N/A. The record
also
has a transaction date, facilitiy number, etc. The input form for the data
puts a date and record number with the record and then a report is
generated
daily which is then emailed to the affected facilities which lists all of
the
discrepancies for the transaction date range selected for the report. The
"X"s are important to the auditors because it tells them the item was
included and correct. The original report was a typical report with the
field
names across the top and detail record values underneath. Problem was it
was
way too cluttered and I am tasked with a redesign. It sounds like what I
would like to do is impossible (align in the middle of a text box).

Thanks for the interest anyway.
--
R&D Consulting

How set are you with your current table structure? It sounds a bit
un-normalized but I could be wrong. I expect a normalized structure would
remove all issues with the use of a subreport.

I could be wrong but it would help to get a better understanding of your
table structure.

--
Duane Hookom
MS Access MVP


Pardon the lengthy description!!!
I have a report derived from a query containing a Location, a Date and
16
Fields of data. The 16 fields are review categories and can be "X" (no
problem) or one of 4 other characters. I display the 'other' categories
(only) by using Invisible Text boxes for [Field1], [Field2] etc., then,
in
another invisible text box, I do an Iif to test for "X" and display the
field
name and value.
Example: =Iif([Field1]="X","","Field 1 Name
"&[Field1]&Chr(13)&Chr(10))
Finally, I concatenate all 16 fields together to display ONLY those
'problem' fields for the record one under the other. The report also
groups
by location, date etc.
PROBLEM:
In the Iif statement all the Field names are of different lengths and I
want
the values to line up vertically on the report. Is there a way to put a
'Tab'
character or ? at the end of the Field name and before the value. I can
play
with the number of spaces I put between the quote marks and get close
but
that is clumsy and doesn't really line them up exactly anyway.

 

[Duane Hookom]

I would normalize your table with a union query:
SELECT [Record Number], CSC As Grade, "CSC" as Crit
FROM [table looks like this]
UNION ALL
SELECT [Record Number], DSJ, "DSJ"
FROM [table looks like this]
UNION ALL
SELECT [Record Number], CRE, "CRE"
FROM [table looks like this]
UNION ALL
-- etc --;

You can the query your union query for [Record Number] WHERE Grade is
non-compliant.

Use this union query as the linked subreport in your main report and filter
the subreport to only records that are non-compliant. You can use Grade and
Crit as separate text boxes.

--
Duane Hookom
MS Access MVP

Duane,
The table looks like this (I'll not include all of the fields for brevity)

Record Number - AutoNumber (Primary)
EntryDate - Date/Time (date of transaction)
CSC - Number (Facility)
DSJ - Text (1st criteria)
CRE - Text (2nd criteria)
ACH - Text (3rd criteria)
CC_CRE - Text (4th criteria)
ACCT_Zero - Text (5th criteria)

and so forth up to 16 different criteria. The list is to validate
financial
transactions posted to the main office contain all of the required
documentation and are correct. They are graded as Compliant ("X"), Not
Applicable ("N/A"), or Failed (4 different codes).
The table is kept for a permanent record for auditing and site evaluation
and a Report sent to each facility for their info. This report is what I
am
working on. I select a date range (entered by the operator) and test for
any
records with any non-compliant field (a record may fail in one or, I
guess,
all categories). I then generate the report but only print (actually email
vs
print) those records with non-compliant field(s) AND only the fields which
are non-compliant.
I have all of that working ---- the last problem is only a formatting one,
I would like for it to look more professional by all of the data 'left
aligned'.
See earlier of mine for how I print the final result.
Sample printout (2 records)
CSC 1904
11/11/2005
DSJ F
CC-CRE R

11/12/2005
CRE R

CSC 2301
11/10/2005
ACCT_ZERO F

Hope that helps. The R, F, O etc don't line up properly. And no, they
won't
let me change the 'labels'. I have also abbreviated the labels as some of
them have 10 to 15 characters while others have only 4 or 5.

Thanks for any thoughts.



--
R&D Consulting

If you provided the table structure, a few sample records, and how you
want
them displayed in a report, we might be able to help.

I think a union query would work but you didn't provide the structure
information.

--
Duane Hookom
MS Access MVP
--

I inherited the table but it serves as a 'checklist' of incoming
financial
reports from facilities in the field (about 200 facilities). The table
allows
the audit folks to evaluate each of 16 criteria of each report - an "X"
indicates the report is compliant and any one of 4 other characters
(F,R,P,O)
indicate some problem (the letters are just codes) or an N/A. The
record
also
has a transaction date, facilitiy number, etc. The input form for the
data
puts a date and record number with the record and then a report is
generated
daily which is then emailed to the affected facilities which lists all
of
the
discrepancies for the transaction date range selected for the report.
The
"X"s are important to the auditors because it tells them the item was
included and correct. The original report was a typical report with the
field
names across the top and detail record values underneath. Problem was
it
was
way too cluttered and I am tasked with a redesign. It sounds like what
I
would like to do is impossible (align in the middle of a text box).

Thanks for the interest anyway.
--
R&D Consulting


:

How set are you with your current table structure? It sounds a bit
un-normalized but I could be wrong. I expect a normalized structure
would
remove all issues with the use of a subreport.

I could be wrong but it would help to get a better understanding of
your
table structure.

--
Duane Hookom
MS Access MVP


Pardon the lengthy description!!!
I have a report derived from a query containing a Location, a Date
and
16
Fields of data. The 16 fields are review categories and can be "X"
(no
problem) or one of 4 other characters. I display the 'other'
categories
(only) by using Invisible Text boxes for [Field1], [Field2] etc.,
then,
in
another invisible text box, I do an Iif to test for "X" and display
the
field
name and value.
Example: =Iif([Field1]="X","","Field 1 Name
"&[Field1]&Chr(13)&Chr(10))
Finally, I concatenate all 16 fields together to display ONLY those
'problem' fields for the record one under the other. The report also
groups
by location, date etc.
PROBLEM:
In the Iif statement all the Field names are of different lengths
and I
want
the values to line up vertically on the report. Is there a way to
put a
'Tab'
character or ? at the end of the Field name and before the value. I
can
play
with the number of spaces I put between the quote marks and get
close
but
that is clumsy and doesn't really line them up exactly anyway.

 

[Guest]

Duane,
Thanks for the input. Actually I am normalizing the table and after work
last night had the same idea you give about using Criteria and Grade as
separate text boxes. Don't know why I had become hung up on putting them both
together but your input confirmed the thought and I have implemented it and
it looks great.
Thanks for your help!!!


--
R&D Consulting

I would normalize your table with a union query:
SELECT [Record Number], CSC As Grade, "CSC" as Crit
FROM [table looks like this]
UNION ALL
SELECT [Record Number], DSJ, "DSJ"
FROM [table looks like this]
UNION ALL
SELECT [Record Number], CRE, "CRE"
FROM [table looks like this]
UNION ALL
-- etc --;

You can the query your union query for [Record Number] WHERE Grade is
non-compliant.

Use this union query as the linked subreport in your main report and filter
the subreport to only records that are non-compliant. You can use Grade and
Crit as separate text boxes.

--
Duane Hookom
MS Access MVP

Duane,
The table looks like this (I'll not include all of the fields for brevity)

Record Number - AutoNumber (Primary)
EntryDate - Date/Time (date of transaction)
CSC - Number (Facility)
DSJ - Text (1st criteria)
CRE - Text (2nd criteria)
ACH - Text (3rd criteria)
CC_CRE - Text (4th criteria)
ACCT_Zero - Text (5th criteria)

and so forth up to 16 different criteria. The list is to validate
financial
transactions posted to the main office contain all of the required
documentation and are correct. They are graded as Compliant ("X"), Not
Applicable ("N/A"), or Failed (4 different codes).
The table is kept for a permanent record for auditing and site evaluation
and a Report sent to each facility for their info. This report is what I
am
working on. I select a date range (entered by the operator) and test for
any
records with any non-compliant field (a record may fail in one or, I
guess,
all categories). I then generate the report but only print (actually email
vs
print) those records with non-compliant field(s) AND only the fields which
are non-compliant.
I have all of that working ---- the last problem is only a formatting one,
I would like for it to look more professional by all of the data 'left
aligned'.
See earlier of mine for how I print the final result.
Sample printout (2 records)
CSC 1904
11/11/2005
DSJ F
CC-CRE R

11/12/2005
CRE R

CSC 2301
11/10/2005
ACCT_ZERO F

Hope that helps. The R, F, O etc don't line up properly. And no, they
won't
let me change the 'labels'. I have also abbreviated the labels as some of
them have 10 to 15 characters while others have only 4 or 5.

Thanks for any thoughts.



--
R&D Consulting

If you provided the table structure, a few sample records, and how you
want
them displayed in a report, we might be able to help.

I think a union query would work but you didn't provide the structure
information.

--
Duane Hookom
MS Access MVP
--

I inherited the table but it serves as a 'checklist' of incoming
financial
reports from facilities in the field (about 200 facilities). The table
allows
the audit folks to evaluate each of 16 criteria of each report - an "X"
indicates the report is compliant and any one of 4 other characters
(F,R,P,O)
indicate some problem (the letters are just codes) or an N/A. The
record
also
has a transaction date, facilitiy number, etc. The input form for the
data
puts a date and record number with the record and then a report is
generated
daily which is then emailed to the affected facilities which lists all
of
the
discrepancies for the transaction date range selected for the report.
The
"X"s are important to the auditors because it tells them the item was
included and correct. The original report was a typical report with the
field
names across the top and detail record values underneath. Problem was
it
was
way too cluttered and I am tasked with a redesign. It sounds like what
I
would like to do is impossible (align in the middle of a text box).

Thanks for the interest anyway.
--
R&D Consulting


:

How set are you with your current table structure? It sounds a bit
un-normalized but I could be wrong. I expect a normalized structure
would
remove all issues with the use of a subreport.

I could be wrong but it would help to get a better understanding of
your
table structure.

--
Duane Hookom
MS Access MVP


Pardon the lengthy description!!!
I have a report derived from a query containing a Location, a Date
and
16
Fields of data. The 16 fields are review categories and can be "X"
(no
problem) or one of 4 other characters. I display the 'other'
categories
(only) by using Invisible Text boxes for [Field1], [Field2] etc.,
then,
in
another invisible text box, I do an Iif to test for "X" and display
the
field
name and value.
Example: =Iif([Field1]="X","","Field 1 Name
"&[Field1]&Chr(13)&Chr(10))
Finally, I concatenate all 16 fields together to display ONLY those
'problem' fields for the record one under the other. The report also
groups
by location, date etc.
PROBLEM:
In the Iif statement all the Field names are of different lengths
and I
want
the values to line up vertically on the report. Is there a way to
put a
'Tab'
character or ? at the end of the Field name and before the value. I
can
play
with the number of spaces I put between the quote marks and get
close
but
that is clumsy and doesn't really line them up exactly anyway.