Truncated Text Box on Report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a field from a query where I have concatenated multiple fields to show
(Part Numbers, Operations and Inventory amounts on hand) for all components
of a finished part number where I'm using ** as a spacer between each set.

([P1]&"-"&[O1]&"/"&[Inv1]&"**"&[P2]&"-"&[O2]&"/"&[Inv2]&"**"&[P3]....ect. up
to 25 sets). This concatenated field is attached in a query by a job and
finished part number.

I've done this to be able to include this info as a single line in a very
complicated report. The report has no groupings, but is sorted by Job Number
and Part Number.

All the info I'm seeking is showing up in the query, but is being truncated
on the report.

Any idea what I can do to show all the data from the query field on the
report?
 
I've read some other threads and have seen that I may need to format the text
box as memo. However, in it's properties, there is no drop down for the
format field. How can I format it?
 
neenmarie said:
I've read some other threads and have seen that I may need to format
the text box as memo. However, in it's properties, there is no drop
down for the format field. How can I format it?
Click to expand...

Memo is not a format, it is a data type. You need to change the design of
the table and make the field a memo data type instead of Text.
 
This is a field in a query to combines both text and number fields. How can
I change the combined field to a memo type?
 
Have you set any value in the Format property of the text box? Is the text
box Can Grow property set to Yes?
 
neenmarie said:
This is a field in a query to combines both text and number fields.
How can I change the combined field to a memo type?
Click to expand...

Truncation should not be a problem in the scenario you are describing. What
is the query expression?
 
I have a field from a query where I have concatenated multiple fields to show
(Part Numbers, Operations and Inventory amounts on hand) for all components
of a finished part number where I'm using ** as a spacer between each set.

([P1]&"-"&[O1]&"/"&[Inv1]&"**"&[P2]&"-"&[O2]&"/"&[Inv2]&"**"&[P3]....ect. up
to 25 sets). This concatenated field is attached in a query by a job and
finished part number.

I've done this to be able to include this info as a single line in a very
complicated report. The report has no groupings, but is sorted by Job Number
and Part Number.

All the info I'm seeking is showing up in the query, but is being truncated
on the report.

Any idea what I can do to show all the data from the query field on the
report?
 
I've put nothing in the Format property. And, there is nothing in the drop
down there.
The Property is set to Yes.


Duane Hookom said:
Have you set any value in the Format property of the text box? Is the text
box Can Grow property set to Yes?

--
Duane Hookom
MS Access MVP


neenmarie said:
I have a field from a query where I have concatenated multiple fields to
show
(Part Numbers, Operations and Inventory amounts on hand) for all
components
of a finished part number where I'm using ** as a spacer between each set.

([P1]&"-"&[O1]&"/"&[Inv1]&"**"&[P2]&"-"&[O2]&"/"&[Inv2]&"**"&[P3]....ect.
up
to 25 sets). This concatenated field is attached in a query by a job and
finished part number.

I've done this to be able to include this info as a single line in a very
complicated report. The report has no groupings, but is sorted by Job
Number
and Part Number.

All the info I'm seeking is showing up in the query, but is being
truncated
on the report.

Any idea what I can do to show all the data from the query field on the
report?
Click to expand...
Click to expand...
 
Is your text always truncating at about 255 characters? Is the text box in
the detail section or a header/footer?

--
Duane Hookom
MS Access MVP
--

neenmarie said:
I've put nothing in the Format property. And, there is nothing in the
drop
down there.
The Property is set to Yes.


Duane Hookom said:
Have you set any value in the Format property of the text box? Is the
text
box Can Grow property set to Yes?

--
Duane Hookom
MS Access MVP


neenmarie said:
I have a field from a query where I have concatenated multiple fields to
show
(Part Numbers, Operations and Inventory amounts on hand) for all
components
of a finished part number where I'm using ** as a spacer between each
set.

([P1]&"-"&[O1]&"/"&[Inv1]&"**"&[P2]&"-"&[O2]&"/"&[Inv2]&"**"&[P3]....ect.
up
to 25 sets). This concatenated field is attached in a query by a job
and
finished part number.

I've done this to be able to include this info as a single line in a
very
complicated report. The report has no groupings, but is sorted by Job
Number
and Part Number.

All the info I'm seeking is showing up in the query, but is being
truncated
on the report.

Any idea what I can do to show all the data from the query field on the
report?
Click to expand...
Click to expand...
Click to expand...
 
Yes, is truncating at 255 and is in the detail section.

Duane Hookom said:
Is your text always truncating at about 255 characters? Is the text box in
the detail section or a header/footer?

--
Duane Hookom
MS Access MVP
--

neenmarie said:
I've put nothing in the Format property. And, there is nothing in the
drop
down there.
The Property is set to Yes.


Duane Hookom said:
Have you set any value in the Format property of the text box? Is the
text
box Can Grow property set to Yes?

--
Duane Hookom
MS Access MVP


I have a field from a query where I have concatenated multiple fields to
show
(Part Numbers, Operations and Inventory amounts on hand) for all
components
of a finished part number where I'm using ** as a spacer between each
set.

([P1]&"-"&[O1]&"/"&[Inv1]&"**"&[P2]&"-"&[O2]&"/"&[Inv2]&"**"&[P3]....ect.
up
to 25 sets). This concatenated field is attached in a query by a job
and
finished part number.

I've done this to be able to include this info as a single line in a
very
complicated report. The report has no groupings, but is sorted by Job
Number
and Part Number.

All the info I'm seeking is showing up in the query, but is being
truncated
on the report.

Any idea what I can do to show all the data from the query field on the
report?
Click to expand...
Click to expand...
Click to expand...
 
How many characters do you have in your expression in your query column?
Your structure looks to be a bit un-normalized.

Can you provide the complete SQL view of your query?

--
Duane Hookom
MS Access MVP


neenmarie said:
I have a field from a query where I have concatenated multiple fields to
show
(Part Numbers, Operations and Inventory amounts on hand) for all
components
of a finished part number where I'm using ** as a spacer between each set.

([P1]&"-"&[O1]&"/"&[Inv1]&"**"&[P2]&"-"&[O2]&"/"&[Inv2]&"**"&[P3]....ect.
up
to 25 sets). This concatenated field is attached in a query by a job and
finished part number.

I've done this to be able to include this info as a single line in a very
complicated report. The report has no groupings, but is sorted by Job
Number
and Part Number.

All the info I'm seeking is showing up in the query, but is being
truncated
on the report.

Any idea what I can do to show all the data from the query field on the
report?


Rick Brandt said:
Truncation should not be a problem in the scenario you are describing.
What
is the query expression?
Click to expand...
Click to expand...
 
The expressions are huge.
The report was working perfectly until the boss decided he wanted to see all
the current inventory in house for the build of each job on the same report
with the customer requirements. The original report takes a lot of info and
makes a table. Perhaps I'll need to somehow include the build's inventory at
each operation into the make table query instead of trying to include it
later. But, I'm afraid I'll still have the same problem of too many
characters to show on the report.

Here are the current expression.
I have divided them into two sub queries. The first:qryBOMA contains the
following:

SELECT qry1BOM.JobNumb, qry1BOM.FPN, ([1p] & " ** " & [2p] & " ** " & [3p] &
" ** " & [4p] & " ** " & [5p] & " ** " & [6p] & " ** " & [7p] & " ** " &
[8p] & " ** " & [9p] & " ** " & [10p] & " ** " & [11p] & " ** " & [12p] & "
** " & [13p] & " ** " & [14p] & " ** " & [15p]) AS InvA, qry1BOM.[1p],
qry1BOM.[1q], qry2BOM.[2p], qry2BOM.[2q], qry3BOM.[3p], qry3BOM.[3q],
qry4BOM.[4p], qry4BOM.[4q], qry5BOM.[5p], qry5BOM.[5q], qry6BOM.[6p],
qry6BOM.[6q], qry7BOM.[7p], qry7BOM.[7q], qry8BOM.[8p], qry8BOM.[8q],
qry9BOM.[9p], qry9BOM.[9q], qry10BOM.[10p], qry10BOM.[10q], qry11BOM.[11p],
qry11BOM.[11q], qry12BOM.[12p], qry12BOM.[12q], qry13BOM.[13p],
qry13BOM.[13q], qry14BOM.[14p], qry14BOM.[14q], qry15BOM.[15p], qry15BOM.[15q]
FROM (((((((((((((qry1BOM LEFT JOIN qry2BOM ON (qry1BOM.JobNumb =
qry2BOM.JobNumb) AND (qry1BOM.FPN = qry2BOM.FPN)) LEFT JOIN qry3BOM ON
(qry1BOM.JobNumb = qry3BOM.JobNumb) AND (qry1BOM.FPN = qry3BOM.FPN)) LEFT
JOIN qry4BOM ON (qry1BOM.JobNumb = qry4BOM.JobNumb) AND (qry1BOM.FPN =
qry4BOM.FPN)) LEFT JOIN qry5BOM ON (qry1BOM.JobNumb = qry5BOM.JobNumb) AND
(qry1BOM.FPN = qry5BOM.FPN)) LEFT JOIN qry6BOM ON (qry1BOM.JobNumb =
qry6BOM.JobNumb) AND (qry1BOM.FPN = qry6BOM.FPN)) LEFT JOIN qry7BOM ON
(qry1BOM.JobNumb = qry7BOM.JobNumb) AND (qry1BOM.FPN = qry7BOM.FPN)) LEFT
JOIN qry8BOM ON (qry1BOM.JobNumb = qry8BOM.JobNumb) AND (qry1BOM.FPN =
qry8BOM.FPN)) LEFT JOIN qry9BOM ON (qry1BOM.JobNumb = qry9BOM.JobNumb) AND
(qry1BOM.FPN = qry9BOM.FPN)) LEFT JOIN qry10BOM ON (qry1BOM.JobNumb =
qry10BOM.JobNumb) AND (qry1BOM.FPN = qry10BOM.FPN)) LEFT JOIN qry11BOM ON
(qry1BOM.JobNumb = qry11BOM.JobNumb) AND (qry1BOM.FPN = qry11BOM.FPN)) LEFT
JOIN qry12BOM ON (qry1BOM.JobNumb = qry12BOM.JobNumb) AND (qry1BOM.FPN =
qry12BOM.FPN)) LEFT JOIN qry13BOM ON (qry1BOM.JobNumb = qry13BOM.JobNumb) AND
(qry1BOM.FPN = qry13BOM.FPN)) LEFT JOIN qry14BOM ON (qry1BOM.JobNumb =
qry14BOM.JobNumb) AND (qry1BOM.FPN = qry14BOM.FPN)) LEFT JOIN qry15BOM ON
(qry1BOM.JobNumb = qry15BOM.JobNumb) AND (qry1BOM.FPN = qry15BOM.FPN);

The second is qryBOMB and contains:

SELECT qry1BOM.JobNumb, qry1BOM.FPN, ([16p] & " ** " & [17p] & " ** " &
[18p] & " ** " & [19p] & " ** " & [20p] & " ** " & [21p] & " ** " & [22p]
& " ** " & [23p] & " ** " & [24p] & " ** " & [25p]) AS Invb
FROM (((((((((qry1BOM LEFT JOIN qry16BOM ON (qry1BOM.JobNumb =
qry16BOM.JobNumb) AND (qry1BOM.FPN = qry16BOM.FPN)) LEFT JOIN qry17BOM ON
(qry1BOM.JobNumb = qry17BOM.JobNumb) AND (qry1BOM.FPN = qry17BOM.FPN)) LEFT
JOIN qry18BOM ON (qry1BOM.JobNumb = qry18BOM.JobNumb) AND (qry1BOM.FPN =
qry18BOM.FPN)) LEFT JOIN qry19BOM ON (qry1BOM.JobNumb = qry19BOM.JobNumb) AND
(qry1BOM.FPN = qry19BOM.FPN)) LEFT JOIN qry20BOM ON (qry1BOM.JobNumb =
qry20BOM.JobNumb) AND (qry1BOM.FPN = qry20BOM.FPN)) LEFT JOIN qry21BOM ON
(qry1BOM.JobNumb = qry21BOM.JobNumb) AND (qry1BOM.FPN = qry21BOM.FPN)) LEFT
JOIN qry22BOM ON (qry1BOM.JobNumb = qry22BOM.JobNumb) AND (qry1BOM.FPN =
qry22BOM.FPN)) LEFT JOIN qry23BOM ON (qry1BOM.JobNumb = qry23BOM.JobNumb) AND
(qry1BOM.FPN = qry23BOM.FPN)) LEFT JOIN qry24BOM ON (qry1BOM.JobNumb =
qry24BOM.JobNumb) AND (qry1BOM.FPN = qry24BOM.FPN)) LEFT JOIN qry25BOM ON
(qry1BOM.JobNumb = qry25BOM.JobNumb) AND (qry1BOM.FPN = qry25BOM.FPN);

The third query combines the first two into qryBOM as follows:

SELECT qryBOMA.JobNumb, qryBOMA.FPN, ([Inva] & "**" & [InvB]) AS Inv
FROM qryBOMA LEFT JOIN qryBOMB ON (qryBOMA.JobNumb = qryBOMB.JobNumb) AND
(qryBOMA.FPN = qryBOMB.FPN);

This is then joined to the query that the report pulls from as follows:

SELECT DISTINCT RelReportPastDue.Company, RelReportPastDue.CustPO,
RelReportPastDue.ShortCode, RelReportPastDue.PN, RelReportPastDue.JobNumb,
RelReportPastDue.LastShipNumber, RelReportPastDue.LastShipDate,
RelReportPastDue.LastQty, RelReportPastDue.CCum, RelReportPastDue.KKCum,
RelReportPastDue.PastDue, RelReportPastDue.Week1QtyDue, IIf([Wk1Bal] Is
Null,0,[Wk1Bal]) AS BalWk1, RelReportPastDue.BegWk2, RelReportPastDue.EndWk2,
RelReportPastDue.Wk2QtyDue, IIf([Wk2Bal] Is Null,0,[Wk2Bal]) AS BalWk2,
RelReportPastDue.BegWk3, RelReportPastDue.EndWk3, RelReportPastDue.Wk3QtyDue,
IIf([Wk3Bal] Is Null,0,[Wk3Bal]) AS BalWk3, RelReportPastDue.BegWk4,
RelReportPastDue.EndWk4, RelReportPastDue.Wk4QtyDue, IIf([Wk4Bal] Is
Null,0,[Wk4Bal]) AS BalWk4, RelReportPastDue.BegWk3, RelReportPastDue.BegWk4,
RelReportPastDue.BegWk2, CurrentInventoryFromTUFPNs.[Sum Of InventoryQty],
RelReportPastDue.StandardPack, qryBOM.Inv
FROM (RelReportPastDue LEFT JOIN CurrentInventoryFromTUFPNs ON
(RelReportPastDue.PN = CurrentInventoryFromTUFPNs.InventoryPN) AND
(RelReportPastDue.JobNumb = CurrentInventoryFromTUFPNs.JobNumber)) LEFT JOIN
qryBOM ON (RelReportPastDue.JobNumb = qryBOM.JobNumb) AND
(RelReportPastDue.PN = qryBOM.FPN);





Duane Hookom said:
How many characters do you have in your expression in your query column?
Your structure looks to be a bit un-normalized.

Can you provide the complete SQL view of your query?

--
Duane Hookom
MS Access MVP


neenmarie said:
I have a field from a query where I have concatenated multiple fields to
show
(Part Numbers, Operations and Inventory amounts on hand) for all
components
of a finished part number where I'm using ** as a spacer between each set.

([P1]&"-"&[O1]&"/"&[Inv1]&"**"&[P2]&"-"&[O2]&"/"&[Inv2]&"**"&[P3]....ect.
up
to 25 sets). This concatenated field is attached in a query by a job and
finished part number.

I've done this to be able to include this info as a single line in a very
complicated report. The report has no groupings, but is sorted by Job
Number
and Part Number.

All the info I'm seeking is showing up in the query, but is being
truncated
on the report.

Any idea what I can do to show all the data from the query field on the
report?


Rick Brandt said:
neenmarie wrote:
This is a field in a query to combines both text and number fields.
How can I change the combined field to a memo type?

Truncation should not be a problem in the scenario you are describing.
What
is the query expression?
Click to expand...
Click to expand...
Click to expand...
 
I thought you stated all the characters were displaying in the query. Try
remove the "DISTINCT" from the final query.

Your table seems highly un-normalized. I can't help but think there has to
be a better way but I hate to even ask deeper questions.

--
Duane Hookom
MS Access MVP


neenmarie said:
The expressions are huge.
The report was working perfectly until the boss decided he wanted to see
all
the current inventory in house for the build of each job on the same
report
with the customer requirements. The original report takes a lot of info
and
makes a table. Perhaps I'll need to somehow include the build's inventory
at
each operation into the make table query instead of trying to include it
later. But, I'm afraid I'll still have the same problem of too many
characters to show on the report.

Here are the current expression.
I have divided them into two sub queries. The first:qryBOMA contains the
following:

SELECT qry1BOM.JobNumb, qry1BOM.FPN, ([1p] & " ** " & [2p] & " ** " & [3p]
&
" ** " & [4p] & " ** " & [5p] & " ** " & [6p] & " ** " & [7p] & " ** "
&
[8p] & " ** " & [9p] & " ** " & [10p] & " ** " & [11p] & " ** " & [12p] &
"
** " & [13p] & " ** " & [14p] & " ** " & [15p]) AS InvA, qry1BOM.[1p],
qry1BOM.[1q], qry2BOM.[2p], qry2BOM.[2q], qry3BOM.[3p], qry3BOM.[3q],
qry4BOM.[4p], qry4BOM.[4q], qry5BOM.[5p], qry5BOM.[5q], qry6BOM.[6p],
qry6BOM.[6q], qry7BOM.[7p], qry7BOM.[7q], qry8BOM.[8p], qry8BOM.[8q],
qry9BOM.[9p], qry9BOM.[9q], qry10BOM.[10p], qry10BOM.[10q],
qry11BOM.[11p],
qry11BOM.[11q], qry12BOM.[12p], qry12BOM.[12q], qry13BOM.[13p],
qry13BOM.[13q], qry14BOM.[14p], qry14BOM.[14q], qry15BOM.[15p],
qry15BOM.[15q]
FROM (((((((((((((qry1BOM LEFT JOIN qry2BOM ON (qry1BOM.JobNumb =
qry2BOM.JobNumb) AND (qry1BOM.FPN = qry2BOM.FPN)) LEFT JOIN qry3BOM ON
(qry1BOM.JobNumb = qry3BOM.JobNumb) AND (qry1BOM.FPN = qry3BOM.FPN)) LEFT
JOIN qry4BOM ON (qry1BOM.JobNumb = qry4BOM.JobNumb) AND (qry1BOM.FPN =
qry4BOM.FPN)) LEFT JOIN qry5BOM ON (qry1BOM.JobNumb = qry5BOM.JobNumb) AND
(qry1BOM.FPN = qry5BOM.FPN)) LEFT JOIN qry6BOM ON (qry1BOM.JobNumb =
qry6BOM.JobNumb) AND (qry1BOM.FPN = qry6BOM.FPN)) LEFT JOIN qry7BOM ON
(qry1BOM.JobNumb = qry7BOM.JobNumb) AND (qry1BOM.FPN = qry7BOM.FPN)) LEFT
JOIN qry8BOM ON (qry1BOM.JobNumb = qry8BOM.JobNumb) AND (qry1BOM.FPN =
qry8BOM.FPN)) LEFT JOIN qry9BOM ON (qry1BOM.JobNumb = qry9BOM.JobNumb) AND
(qry1BOM.FPN = qry9BOM.FPN)) LEFT JOIN qry10BOM ON (qry1BOM.JobNumb =
qry10BOM.JobNumb) AND (qry1BOM.FPN = qry10BOM.FPN)) LEFT JOIN qry11BOM ON
(qry1BOM.JobNumb = qry11BOM.JobNumb) AND (qry1BOM.FPN = qry11BOM.FPN))
LEFT
JOIN qry12BOM ON (qry1BOM.JobNumb = qry12BOM.JobNumb) AND (qry1BOM.FPN =
qry12BOM.FPN)) LEFT JOIN qry13BOM ON (qry1BOM.JobNumb = qry13BOM.JobNumb)
AND
(qry1BOM.FPN = qry13BOM.FPN)) LEFT JOIN qry14BOM ON (qry1BOM.JobNumb =
qry14BOM.JobNumb) AND (qry1BOM.FPN = qry14BOM.FPN)) LEFT JOIN qry15BOM ON
(qry1BOM.JobNumb = qry15BOM.JobNumb) AND (qry1BOM.FPN = qry15BOM.FPN);

The second is qryBOMB and contains:

SELECT qry1BOM.JobNumb, qry1BOM.FPN, ([16p] & " ** " & [17p] & " ** " &
[18p] & " ** " & [19p] & " ** " & [20p] & " ** " & [21p] & " ** " &
[22p]
& " ** " & [23p] & " ** " & [24p] & " ** " & [25p]) AS Invb
FROM (((((((((qry1BOM LEFT JOIN qry16BOM ON (qry1BOM.JobNumb =
qry16BOM.JobNumb) AND (qry1BOM.FPN = qry16BOM.FPN)) LEFT JOIN qry17BOM ON
(qry1BOM.JobNumb = qry17BOM.JobNumb) AND (qry1BOM.FPN = qry17BOM.FPN))
LEFT
JOIN qry18BOM ON (qry1BOM.JobNumb = qry18BOM.JobNumb) AND (qry1BOM.FPN =
qry18BOM.FPN)) LEFT JOIN qry19BOM ON (qry1BOM.JobNumb = qry19BOM.JobNumb)
AND
(qry1BOM.FPN = qry19BOM.FPN)) LEFT JOIN qry20BOM ON (qry1BOM.JobNumb =
qry20BOM.JobNumb) AND (qry1BOM.FPN = qry20BOM.FPN)) LEFT JOIN qry21BOM ON
(qry1BOM.JobNumb = qry21BOM.JobNumb) AND (qry1BOM.FPN = qry21BOM.FPN))
LEFT
JOIN qry22BOM ON (qry1BOM.JobNumb = qry22BOM.JobNumb) AND (qry1BOM.FPN =
qry22BOM.FPN)) LEFT JOIN qry23BOM ON (qry1BOM.JobNumb = qry23BOM.JobNumb)
AND
(qry1BOM.FPN = qry23BOM.FPN)) LEFT JOIN qry24BOM ON (qry1BOM.JobNumb =
qry24BOM.JobNumb) AND (qry1BOM.FPN = qry24BOM.FPN)) LEFT JOIN qry25BOM ON
(qry1BOM.JobNumb = qry25BOM.JobNumb) AND (qry1BOM.FPN = qry25BOM.FPN);

The third query combines the first two into qryBOM as follows:

SELECT qryBOMA.JobNumb, qryBOMA.FPN, ([Inva] & "**" & [InvB]) AS Inv
FROM qryBOMA LEFT JOIN qryBOMB ON (qryBOMA.JobNumb = qryBOMB.JobNumb) AND
(qryBOMA.FPN = qryBOMB.FPN);

This is then joined to the query that the report pulls from as follows:

SELECT DISTINCT RelReportPastDue.Company, RelReportPastDue.CustPO,
RelReportPastDue.ShortCode, RelReportPastDue.PN, RelReportPastDue.JobNumb,
RelReportPastDue.LastShipNumber, RelReportPastDue.LastShipDate,
RelReportPastDue.LastQty, RelReportPastDue.CCum, RelReportPastDue.KKCum,
RelReportPastDue.PastDue, RelReportPastDue.Week1QtyDue, IIf([Wk1Bal] Is
Null,0,[Wk1Bal]) AS BalWk1, RelReportPastDue.BegWk2,
RelReportPastDue.EndWk2,
RelReportPastDue.Wk2QtyDue, IIf([Wk2Bal] Is Null,0,[Wk2Bal]) AS BalWk2,
RelReportPastDue.BegWk3, RelReportPastDue.EndWk3,
RelReportPastDue.Wk3QtyDue,
IIf([Wk3Bal] Is Null,0,[Wk3Bal]) AS BalWk3, RelReportPastDue.BegWk4,
RelReportPastDue.EndWk4, RelReportPastDue.Wk4QtyDue, IIf([Wk4Bal] Is
Null,0,[Wk4Bal]) AS BalWk4, RelReportPastDue.BegWk3,
RelReportPastDue.BegWk4,
RelReportPastDue.BegWk2, CurrentInventoryFromTUFPNs.[Sum Of InventoryQty],
RelReportPastDue.StandardPack, qryBOM.Inv
FROM (RelReportPastDue LEFT JOIN CurrentInventoryFromTUFPNs ON
(RelReportPastDue.PN = CurrentInventoryFromTUFPNs.InventoryPN) AND
(RelReportPastDue.JobNumb = CurrentInventoryFromTUFPNs.JobNumber)) LEFT
JOIN
qryBOM ON (RelReportPastDue.JobNumb = qryBOM.JobNumb) AND
(RelReportPastDue.PN = qryBOM.FPN);





Duane Hookom said:
How many characters do you have in your expression in your query column?
Your structure looks to be a bit un-normalized.

Can you provide the complete SQL view of your query?

--
Duane Hookom
MS Access MVP


neenmarie said:
I have a field from a query where I have concatenated multiple fields to
show
(Part Numbers, Operations and Inventory amounts on hand) for all
components
of a finished part number where I'm using ** as a spacer between each
set.

([P1]&"-"&[O1]&"/"&[Inv1]&"**"&[P2]&"-"&[O2]&"/"&[Inv2]&"**"&[P3]....ect.
up
to 25 sets). This concatenated field is attached in a query by a job
and
finished part number.

I've done this to be able to include this info as a single line in a
very
complicated report. The report has no groupings, but is sorted by Job
Number
and Part Number.

All the info I'm seeking is showing up in the query, but is being
truncated
on the report.

Any idea what I can do to show all the data from the query field on the
report?


:

neenmarie wrote:
This is a field in a query to combines both text and number fields.
How can I change the combined field to a memo type?

Truncation should not be a problem in the scenario you are describing.
What
is the query expression?
Click to expand...
Click to expand...
Click to expand...
 
Thank you for trying. Removing 'Distinct' does allow all the characters to
appear, but adds extra records to the query and report.

What do you mean by 'un-normalized'.

Duane Hookom said:
I thought you stated all the characters were displaying in the query. Try
remove the "DISTINCT" from the final query.

Your table seems highly un-normalized. I can't help but think there has to
be a better way but I hate to even ask deeper questions.

--
Duane Hookom
MS Access MVP


neenmarie said:
The expressions are huge.
The report was working perfectly until the boss decided he wanted to see
all
the current inventory in house for the build of each job on the same
report
with the customer requirements. The original report takes a lot of info
and
makes a table. Perhaps I'll need to somehow include the build's inventory
at
each operation into the make table query instead of trying to include it
later. But, I'm afraid I'll still have the same problem of too many
characters to show on the report.

Here are the current expression.
I have divided them into two sub queries. The first:qryBOMA contains the
following:

SELECT qry1BOM.JobNumb, qry1BOM.FPN, ([1p] & " ** " & [2p] & " ** " & [3p]
&
" ** " & [4p] & " ** " & [5p] & " ** " & [6p] & " ** " & [7p] & " ** "
&
[8p] & " ** " & [9p] & " ** " & [10p] & " ** " & [11p] & " ** " & [12p] &
"
** " & [13p] & " ** " & [14p] & " ** " & [15p]) AS InvA, qry1BOM.[1p],
qry1BOM.[1q], qry2BOM.[2p], qry2BOM.[2q], qry3BOM.[3p], qry3BOM.[3q],
qry4BOM.[4p], qry4BOM.[4q], qry5BOM.[5p], qry5BOM.[5q], qry6BOM.[6p],
qry6BOM.[6q], qry7BOM.[7p], qry7BOM.[7q], qry8BOM.[8p], qry8BOM.[8q],
qry9BOM.[9p], qry9BOM.[9q], qry10BOM.[10p], qry10BOM.[10q],
qry11BOM.[11p],
qry11BOM.[11q], qry12BOM.[12p], qry12BOM.[12q], qry13BOM.[13p],
qry13BOM.[13q], qry14BOM.[14p], qry14BOM.[14q], qry15BOM.[15p],
qry15BOM.[15q]
FROM (((((((((((((qry1BOM LEFT JOIN qry2BOM ON (qry1BOM.JobNumb =
qry2BOM.JobNumb) AND (qry1BOM.FPN = qry2BOM.FPN)) LEFT JOIN qry3BOM ON
(qry1BOM.JobNumb = qry3BOM.JobNumb) AND (qry1BOM.FPN = qry3BOM.FPN)) LEFT
JOIN qry4BOM ON (qry1BOM.JobNumb = qry4BOM.JobNumb) AND (qry1BOM.FPN =
qry4BOM.FPN)) LEFT JOIN qry5BOM ON (qry1BOM.JobNumb = qry5BOM.JobNumb) AND
(qry1BOM.FPN = qry5BOM.FPN)) LEFT JOIN qry6BOM ON (qry1BOM.JobNumb =
qry6BOM.JobNumb) AND (qry1BOM.FPN = qry6BOM.FPN)) LEFT JOIN qry7BOM ON
(qry1BOM.JobNumb = qry7BOM.JobNumb) AND (qry1BOM.FPN = qry7BOM.FPN)) LEFT
JOIN qry8BOM ON (qry1BOM.JobNumb = qry8BOM.JobNumb) AND (qry1BOM.FPN =
qry8BOM.FPN)) LEFT JOIN qry9BOM ON (qry1BOM.JobNumb = qry9BOM.JobNumb) AND
(qry1BOM.FPN = qry9BOM.FPN)) LEFT JOIN qry10BOM ON (qry1BOM.JobNumb =
qry10BOM.JobNumb) AND (qry1BOM.FPN = qry10BOM.FPN)) LEFT JOIN qry11BOM ON
(qry1BOM.JobNumb = qry11BOM.JobNumb) AND (qry1BOM.FPN = qry11BOM.FPN))
LEFT
JOIN qry12BOM ON (qry1BOM.JobNumb = qry12BOM.JobNumb) AND (qry1BOM.FPN =
qry12BOM.FPN)) LEFT JOIN qry13BOM ON (qry1BOM.JobNumb = qry13BOM.JobNumb)
AND
(qry1BOM.FPN = qry13BOM.FPN)) LEFT JOIN qry14BOM ON (qry1BOM.JobNumb =
qry14BOM.JobNumb) AND (qry1BOM.FPN = qry14BOM.FPN)) LEFT JOIN qry15BOM ON
(qry1BOM.JobNumb = qry15BOM.JobNumb) AND (qry1BOM.FPN = qry15BOM.FPN);

The second is qryBOMB and contains:

SELECT qry1BOM.JobNumb, qry1BOM.FPN, ([16p] & " ** " & [17p] & " ** " &
[18p] & " ** " & [19p] & " ** " & [20p] & " ** " & [21p] & " ** " &
[22p]
& " ** " & [23p] & " ** " & [24p] & " ** " & [25p]) AS Invb
FROM (((((((((qry1BOM LEFT JOIN qry16BOM ON (qry1BOM.JobNumb =
qry16BOM.JobNumb) AND (qry1BOM.FPN = qry16BOM.FPN)) LEFT JOIN qry17BOM ON
(qry1BOM.JobNumb = qry17BOM.JobNumb) AND (qry1BOM.FPN = qry17BOM.FPN))
LEFT
JOIN qry18BOM ON (qry1BOM.JobNumb = qry18BOM.JobNumb) AND (qry1BOM.FPN =
qry18BOM.FPN)) LEFT JOIN qry19BOM ON (qry1BOM.JobNumb = qry19BOM.JobNumb)
AND
(qry1BOM.FPN = qry19BOM.FPN)) LEFT JOIN qry20BOM ON (qry1BOM.JobNumb =
qry20BOM.JobNumb) AND (qry1BOM.FPN = qry20BOM.FPN)) LEFT JOIN qry21BOM ON
(qry1BOM.JobNumb = qry21BOM.JobNumb) AND (qry1BOM.FPN = qry21BOM.FPN))
LEFT
JOIN qry22BOM ON (qry1BOM.JobNumb = qry22BOM.JobNumb) AND (qry1BOM.FPN =
qry22BOM.FPN)) LEFT JOIN qry23BOM ON (qry1BOM.JobNumb = qry23BOM.JobNumb)
AND
(qry1BOM.FPN = qry23BOM.FPN)) LEFT JOIN qry24BOM ON (qry1BOM.JobNumb =
qry24BOM.JobNumb) AND (qry1BOM.FPN = qry24BOM.FPN)) LEFT JOIN qry25BOM ON
(qry1BOM.JobNumb = qry25BOM.JobNumb) AND (qry1BOM.FPN = qry25BOM.FPN);

The third query combines the first two into qryBOM as follows:

SELECT qryBOMA.JobNumb, qryBOMA.FPN, ([Inva] & "**" & [InvB]) AS Inv
FROM qryBOMA LEFT JOIN qryBOMB ON (qryBOMA.JobNumb = qryBOMB.JobNumb) AND
(qryBOMA.FPN = qryBOMB.FPN);

This is then joined to the query that the report pulls from as follows:

SELECT DISTINCT RelReportPastDue.Company, RelReportPastDue.CustPO,
RelReportPastDue.ShortCode, RelReportPastDue.PN, RelReportPastDue.JobNumb,
RelReportPastDue.LastShipNumber, RelReportPastDue.LastShipDate,
RelReportPastDue.LastQty, RelReportPastDue.CCum, RelReportPastDue.KKCum,
RelReportPastDue.PastDue, RelReportPastDue.Week1QtyDue, IIf([Wk1Bal] Is
Null,0,[Wk1Bal]) AS BalWk1, RelReportPastDue.BegWk2,
RelReportPastDue.EndWk2,
RelReportPastDue.Wk2QtyDue, IIf([Wk2Bal] Is Null,0,[Wk2Bal]) AS BalWk2,
RelReportPastDue.BegWk3, RelReportPastDue.EndWk3,
RelReportPastDue.Wk3QtyDue,
IIf([Wk3Bal] Is Null,0,[Wk3Bal]) AS BalWk3, RelReportPastDue.BegWk4,
RelReportPastDue.EndWk4, RelReportPastDue.Wk4QtyDue, IIf([Wk4Bal] Is
Null,0,[Wk4Bal]) AS BalWk4, RelReportPastDue.BegWk3,
RelReportPastDue.BegWk4,
RelReportPastDue.BegWk2, CurrentInventoryFromTUFPNs.[Sum Of InventoryQty],
RelReportPastDue.StandardPack, qryBOM.Inv
FROM (RelReportPastDue LEFT JOIN CurrentInventoryFromTUFPNs ON
(RelReportPastDue.PN = CurrentInventoryFromTUFPNs.InventoryPN) AND
(RelReportPastDue.JobNumb = CurrentInventoryFromTUFPNs.JobNumber)) LEFT
JOIN
qryBOM ON (RelReportPastDue.JobNumb = qryBOM.JobNumb) AND
(RelReportPastDue.PN = qryBOM.FPN);





Duane Hookom said:
How many characters do you have in your expression in your query column?
Your structure looks to be a bit un-normalized.

Can you provide the complete SQL view of your query?

--
Duane Hookom
MS Access MVP


I have a field from a query where I have concatenated multiple fields to
show
(Part Numbers, Operations and Inventory amounts on hand) for all
components
of a finished part number where I'm using ** as a spacer between each
set.

([P1]&"-"&[O1]&"/"&[Inv1]&"**"&[P2]&"-"&[O2]&"/"&[Inv2]&"**"&[P3]....ect.
up
to 25 sets). This concatenated field is attached in a query by a job
and
finished part number.

I've done this to be able to include this info as a single line in a
very
complicated report. The report has no groupings, but is sorted by Job
Number
and Part Number.

All the info I'm seeking is showing up in the query, but is being
truncated
on the report.

Any idea what I can do to show all the data from the query field on the
report?


:

neenmarie wrote:
This is a field in a query to combines both text and number fields.
How can I change the combined field to a memo type?

Truncation should not be a problem in the scenario you are describing.
What
is the query expression?
Click to expand...
Click to expand...
Click to expand...
 
I couldn't begin to figure out how to remove duplicates from your queries.
If you think you need all of the text, then you will have change your
solution.

To find out about normalization, check these links
http://www.ltcomputerdesigns.com/JCReferences.html#DatabaseDesign101.

I don't know the origin of your data but seeing repeating objects suggests
there has to be a better solution.

--
Duane Hookom
MS Access MVP


neenmarie said:
Thank you for trying. Removing 'Distinct' does allow all the characters
to
appear, but adds extra records to the query and report.

What do you mean by 'un-normalized'.

Duane Hookom said:
I thought you stated all the characters were displaying in the query. Try
remove the "DISTINCT" from the final query.

Your table seems highly un-normalized. I can't help but think there has
to
be a better way but I hate to even ask deeper questions.

--
Duane Hookom
MS Access MVP


neenmarie said:
The expressions are huge.
The report was working perfectly until the boss decided he wanted to
see
all
the current inventory in house for the build of each job on the same
report
with the customer requirements. The original report takes a lot of
info
and
makes a table. Perhaps I'll need to somehow include the build's
inventory
at
each operation into the make table query instead of trying to include
it
later. But, I'm afraid I'll still have the same problem of too many
characters to show on the report.

Here are the current expression.
I have divided them into two sub queries. The first:qryBOMA contains
the
following:

SELECT qry1BOM.JobNumb, qry1BOM.FPN, ([1p] & " ** " & [2p] & " ** " &
[3p]
&
" ** " & [4p] & " ** " & [5p] & " ** " & [6p] & " ** " & [7p] & " **
"
&
[8p] & " ** " & [9p] & " ** " & [10p] & " ** " & [11p] & " ** " & [12p]
&
"
** " & [13p] & " ** " & [14p] & " ** " & [15p]) AS InvA, qry1BOM.[1p],
qry1BOM.[1q], qry2BOM.[2p], qry2BOM.[2q], qry3BOM.[3p], qry3BOM.[3q],
qry4BOM.[4p], qry4BOM.[4q], qry5BOM.[5p], qry5BOM.[5q], qry6BOM.[6p],
qry6BOM.[6q], qry7BOM.[7p], qry7BOM.[7q], qry8BOM.[8p], qry8BOM.[8q],
qry9BOM.[9p], qry9BOM.[9q], qry10BOM.[10p], qry10BOM.[10q],
qry11BOM.[11p],
qry11BOM.[11q], qry12BOM.[12p], qry12BOM.[12q], qry13BOM.[13p],
qry13BOM.[13q], qry14BOM.[14p], qry14BOM.[14q], qry15BOM.[15p],
qry15BOM.[15q]
FROM (((((((((((((qry1BOM LEFT JOIN qry2BOM ON (qry1BOM.JobNumb =
qry2BOM.JobNumb) AND (qry1BOM.FPN = qry2BOM.FPN)) LEFT JOIN qry3BOM ON
(qry1BOM.JobNumb = qry3BOM.JobNumb) AND (qry1BOM.FPN = qry3BOM.FPN))
LEFT
JOIN qry4BOM ON (qry1BOM.JobNumb = qry4BOM.JobNumb) AND (qry1BOM.FPN =
qry4BOM.FPN)) LEFT JOIN qry5BOM ON (qry1BOM.JobNumb = qry5BOM.JobNumb)
AND
(qry1BOM.FPN = qry5BOM.FPN)) LEFT JOIN qry6BOM ON (qry1BOM.JobNumb =
qry6BOM.JobNumb) AND (qry1BOM.FPN = qry6BOM.FPN)) LEFT JOIN qry7BOM ON
(qry1BOM.JobNumb = qry7BOM.JobNumb) AND (qry1BOM.FPN = qry7BOM.FPN))
LEFT
JOIN qry8BOM ON (qry1BOM.JobNumb = qry8BOM.JobNumb) AND (qry1BOM.FPN =
qry8BOM.FPN)) LEFT JOIN qry9BOM ON (qry1BOM.JobNumb = qry9BOM.JobNumb)
AND
(qry1BOM.FPN = qry9BOM.FPN)) LEFT JOIN qry10BOM ON (qry1BOM.JobNumb =
qry10BOM.JobNumb) AND (qry1BOM.FPN = qry10BOM.FPN)) LEFT JOIN qry11BOM
ON
(qry1BOM.JobNumb = qry11BOM.JobNumb) AND (qry1BOM.FPN = qry11BOM.FPN))
LEFT
JOIN qry12BOM ON (qry1BOM.JobNumb = qry12BOM.JobNumb) AND (qry1BOM.FPN
=
qry12BOM.FPN)) LEFT JOIN qry13BOM ON (qry1BOM.JobNumb =
qry13BOM.JobNumb)
AND
(qry1BOM.FPN = qry13BOM.FPN)) LEFT JOIN qry14BOM ON (qry1BOM.JobNumb =
qry14BOM.JobNumb) AND (qry1BOM.FPN = qry14BOM.FPN)) LEFT JOIN qry15BOM
ON
(qry1BOM.JobNumb = qry15BOM.JobNumb) AND (qry1BOM.FPN = qry15BOM.FPN);

The second is qryBOMB and contains:

SELECT qry1BOM.JobNumb, qry1BOM.FPN, ([16p] & " ** " & [17p] & " ** " &
[18p] & " ** " & [19p] & " ** " & [20p] & " ** " & [21p] & " ** " &
[22p]
& " ** " & [23p] & " ** " & [24p] & " ** " & [25p]) AS Invb
FROM (((((((((qry1BOM LEFT JOIN qry16BOM ON (qry1BOM.JobNumb =
qry16BOM.JobNumb) AND (qry1BOM.FPN = qry16BOM.FPN)) LEFT JOIN qry17BOM
ON
(qry1BOM.JobNumb = qry17BOM.JobNumb) AND (qry1BOM.FPN = qry17BOM.FPN))
LEFT
JOIN qry18BOM ON (qry1BOM.JobNumb = qry18BOM.JobNumb) AND (qry1BOM.FPN
=
qry18BOM.FPN)) LEFT JOIN qry19BOM ON (qry1BOM.JobNumb =
qry19BOM.JobNumb)
AND
(qry1BOM.FPN = qry19BOM.FPN)) LEFT JOIN qry20BOM ON (qry1BOM.JobNumb =
qry20BOM.JobNumb) AND (qry1BOM.FPN = qry20BOM.FPN)) LEFT JOIN qry21BOM
ON
(qry1BOM.JobNumb = qry21BOM.JobNumb) AND (qry1BOM.FPN = qry21BOM.FPN))
LEFT
JOIN qry22BOM ON (qry1BOM.JobNumb = qry22BOM.JobNumb) AND (qry1BOM.FPN
=
qry22BOM.FPN)) LEFT JOIN qry23BOM ON (qry1BOM.JobNumb =
qry23BOM.JobNumb)
AND
(qry1BOM.FPN = qry23BOM.FPN)) LEFT JOIN qry24BOM ON (qry1BOM.JobNumb =
qry24BOM.JobNumb) AND (qry1BOM.FPN = qry24BOM.FPN)) LEFT JOIN qry25BOM
ON
(qry1BOM.JobNumb = qry25BOM.JobNumb) AND (qry1BOM.FPN = qry25BOM.FPN);

The third query combines the first two into qryBOM as follows:

SELECT qryBOMA.JobNumb, qryBOMA.FPN, ([Inva] & "**" & [InvB]) AS Inv
FROM qryBOMA LEFT JOIN qryBOMB ON (qryBOMA.JobNumb = qryBOMB.JobNumb)
AND
(qryBOMA.FPN = qryBOMB.FPN);

This is then joined to the query that the report pulls from as follows:

SELECT DISTINCT RelReportPastDue.Company, RelReportPastDue.CustPO,
RelReportPastDue.ShortCode, RelReportPastDue.PN,
RelReportPastDue.JobNumb,
RelReportPastDue.LastShipNumber, RelReportPastDue.LastShipDate,
RelReportPastDue.LastQty, RelReportPastDue.CCum,
RelReportPastDue.KKCum,
RelReportPastDue.PastDue, RelReportPastDue.Week1QtyDue, IIf([Wk1Bal] Is
Null,0,[Wk1Bal]) AS BalWk1, RelReportPastDue.BegWk2,
RelReportPastDue.EndWk2,
RelReportPastDue.Wk2QtyDue, IIf([Wk2Bal] Is Null,0,[Wk2Bal]) AS BalWk2,
RelReportPastDue.BegWk3, RelReportPastDue.EndWk3,
RelReportPastDue.Wk3QtyDue,
IIf([Wk3Bal] Is Null,0,[Wk3Bal]) AS BalWk3, RelReportPastDue.BegWk4,
RelReportPastDue.EndWk4, RelReportPastDue.Wk4QtyDue, IIf([Wk4Bal] Is
Null,0,[Wk4Bal]) AS BalWk4, RelReportPastDue.BegWk3,
RelReportPastDue.BegWk4,
RelReportPastDue.BegWk2, CurrentInventoryFromTUFPNs.[Sum Of
InventoryQty],
RelReportPastDue.StandardPack, qryBOM.Inv
FROM (RelReportPastDue LEFT JOIN CurrentInventoryFromTUFPNs ON
(RelReportPastDue.PN = CurrentInventoryFromTUFPNs.InventoryPN) AND
(RelReportPastDue.JobNumb = CurrentInventoryFromTUFPNs.JobNumber)) LEFT
JOIN
qryBOM ON (RelReportPastDue.JobNumb = qryBOM.JobNumb) AND
(RelReportPastDue.PN = qryBOM.FPN);





:

How many characters do you have in your expression in your query
column?
Your structure looks to be a bit un-normalized.

Can you provide the complete SQL view of your query?

--
Duane Hookom
MS Access MVP


I have a field from a query where I have concatenated multiple fields
to
show
(Part Numbers, Operations and Inventory amounts on hand) for all
components
of a finished part number where I'm using ** as a spacer between
each
set.

([P1]&"-"&[O1]&"/"&[Inv1]&"**"&[P2]&"-"&[O2]&"/"&[Inv2]&"**"&[P3]....ect.
up
to 25 sets). This concatenated field is attached in a query by a
job
and
finished part number.

I've done this to be able to include this info as a single line in a
very
complicated report. The report has no groupings, but is sorted by
Job
Number
and Part Number.

All the info I'm seeking is showing up in the query, but is being
truncated
on the report.

Any idea what I can do to show all the data from the query field on
the
report?


:

neenmarie wrote:
This is a field in a query to combines both text and number
fields.
How can I change the combined field to a memo type?

Truncation should not be a problem in the scenario you are
describing.
What
is the query expression?
Click to expand...
Click to expand...
Click to expand...
 
Thank your for all the time you spent trying to help me. I guess I'll
attempt to find another approach.

Duane Hookom said:
I couldn't begin to figure out how to remove duplicates from your queries.
If you think you need all of the text, then you will have change your
solution.

To find out about normalization, check these links
http://www.ltcomputerdesigns.com/JCReferences.html#DatabaseDesign101.

I don't know the origin of your data but seeing repeating objects suggests
there has to be a better solution.

--
Duane Hookom
MS Access MVP


neenmarie said:
Thank you for trying. Removing 'Distinct' does allow all the characters
to
appear, but adds extra records to the query and report.

What do you mean by 'un-normalized'.

Duane Hookom said:
I thought you stated all the characters were displaying in the query. Try
remove the "DISTINCT" from the final query.

Your table seems highly un-normalized. I can't help but think there has
to
be a better way but I hate to even ask deeper questions.

--
Duane Hookom
MS Access MVP


The expressions are huge.
The report was working perfectly until the boss decided he wanted to
see
all
the current inventory in house for the build of each job on the same
report
with the customer requirements. The original report takes a lot of
info
and
makes a table. Perhaps I'll need to somehow include the build's
inventory
at
each operation into the make table query instead of trying to include
it
later. But, I'm afraid I'll still have the same problem of too many
characters to show on the report.

Here are the current expression.
I have divided them into two sub queries. The first:qryBOMA contains
the
following:

SELECT qry1BOM.JobNumb, qry1BOM.FPN, ([1p] & " ** " & [2p] & " ** " &
[3p]
&
" ** " & [4p] & " ** " & [5p] & " ** " & [6p] & " ** " & [7p] & " **
"
&
[8p] & " ** " & [9p] & " ** " & [10p] & " ** " & [11p] & " ** " & [12p]
&
"
** " & [13p] & " ** " & [14p] & " ** " & [15p]) AS InvA, qry1BOM.[1p],
qry1BOM.[1q], qry2BOM.[2p], qry2BOM.[2q], qry3BOM.[3p], qry3BOM.[3q],
qry4BOM.[4p], qry4BOM.[4q], qry5BOM.[5p], qry5BOM.[5q], qry6BOM.[6p],
qry6BOM.[6q], qry7BOM.[7p], qry7BOM.[7q], qry8BOM.[8p], qry8BOM.[8q],
qry9BOM.[9p], qry9BOM.[9q], qry10BOM.[10p], qry10BOM.[10q],
qry11BOM.[11p],
qry11BOM.[11q], qry12BOM.[12p], qry12BOM.[12q], qry13BOM.[13p],
qry13BOM.[13q], qry14BOM.[14p], qry14BOM.[14q], qry15BOM.[15p],
qry15BOM.[15q]
FROM (((((((((((((qry1BOM LEFT JOIN qry2BOM ON (qry1BOM.JobNumb =
qry2BOM.JobNumb) AND (qry1BOM.FPN = qry2BOM.FPN)) LEFT JOIN qry3BOM ON
(qry1BOM.JobNumb = qry3BOM.JobNumb) AND (qry1BOM.FPN = qry3BOM.FPN))
LEFT
JOIN qry4BOM ON (qry1BOM.JobNumb = qry4BOM.JobNumb) AND (qry1BOM.FPN =
qry4BOM.FPN)) LEFT JOIN qry5BOM ON (qry1BOM.JobNumb = qry5BOM.JobNumb)
AND
(qry1BOM.FPN = qry5BOM.FPN)) LEFT JOIN qry6BOM ON (qry1BOM.JobNumb =
qry6BOM.JobNumb) AND (qry1BOM.FPN = qry6BOM.FPN)) LEFT JOIN qry7BOM ON
(qry1BOM.JobNumb = qry7BOM.JobNumb) AND (qry1BOM.FPN = qry7BOM.FPN))
LEFT
JOIN qry8BOM ON (qry1BOM.JobNumb = qry8BOM.JobNumb) AND (qry1BOM.FPN =
qry8BOM.FPN)) LEFT JOIN qry9BOM ON (qry1BOM.JobNumb = qry9BOM.JobNumb)
AND
(qry1BOM.FPN = qry9BOM.FPN)) LEFT JOIN qry10BOM ON (qry1BOM.JobNumb =
qry10BOM.JobNumb) AND (qry1BOM.FPN = qry10BOM.FPN)) LEFT JOIN qry11BOM
ON
(qry1BOM.JobNumb = qry11BOM.JobNumb) AND (qry1BOM.FPN = qry11BOM.FPN))
LEFT
JOIN qry12BOM ON (qry1BOM.JobNumb = qry12BOM.JobNumb) AND (qry1BOM.FPN
=
qry12BOM.FPN)) LEFT JOIN qry13BOM ON (qry1BOM.JobNumb =
qry13BOM.JobNumb)
AND
(qry1BOM.FPN = qry13BOM.FPN)) LEFT JOIN qry14BOM ON (qry1BOM.JobNumb =
qry14BOM.JobNumb) AND (qry1BOM.FPN = qry14BOM.FPN)) LEFT JOIN qry15BOM
ON
(qry1BOM.JobNumb = qry15BOM.JobNumb) AND (qry1BOM.FPN = qry15BOM.FPN);

The second is qryBOMB and contains:

SELECT qry1BOM.JobNumb, qry1BOM.FPN, ([16p] & " ** " & [17p] & " ** " &
[18p] & " ** " & [19p] & " ** " & [20p] & " ** " & [21p] & " ** " &
[22p]
& " ** " & [23p] & " ** " & [24p] & " ** " & [25p]) AS Invb
FROM (((((((((qry1BOM LEFT JOIN qry16BOM ON (qry1BOM.JobNumb =
qry16BOM.JobNumb) AND (qry1BOM.FPN = qry16BOM.FPN)) LEFT JOIN qry17BOM
ON
(qry1BOM.JobNumb = qry17BOM.JobNumb) AND (qry1BOM.FPN = qry17BOM.FPN))
LEFT
JOIN qry18BOM ON (qry1BOM.JobNumb = qry18BOM.JobNumb) AND (qry1BOM.FPN
=
qry18BOM.FPN)) LEFT JOIN qry19BOM ON (qry1BOM.JobNumb =
qry19BOM.JobNumb)
AND
(qry1BOM.FPN = qry19BOM.FPN)) LEFT JOIN qry20BOM ON (qry1BOM.JobNumb =
qry20BOM.JobNumb) AND (qry1BOM.FPN = qry20BOM.FPN)) LEFT JOIN qry21BOM
ON
(qry1BOM.JobNumb = qry21BOM.JobNumb) AND (qry1BOM.FPN = qry21BOM.FPN))
LEFT
JOIN qry22BOM ON (qry1BOM.JobNumb = qry22BOM.JobNumb) AND (qry1BOM.FPN
=
qry22BOM.FPN)) LEFT JOIN qry23BOM ON (qry1BOM.JobNumb =
qry23BOM.JobNumb)
AND
(qry1BOM.FPN = qry23BOM.FPN)) LEFT JOIN qry24BOM ON (qry1BOM.JobNumb =
qry24BOM.JobNumb) AND (qry1BOM.FPN = qry24BOM.FPN)) LEFT JOIN qry25BOM
ON
(qry1BOM.JobNumb = qry25BOM.JobNumb) AND (qry1BOM.FPN = qry25BOM.FPN);

The third query combines the first two into qryBOM as follows:

SELECT qryBOMA.JobNumb, qryBOMA.FPN, ([Inva] & "**" & [InvB]) AS Inv
FROM qryBOMA LEFT JOIN qryBOMB ON (qryBOMA.JobNumb = qryBOMB.JobNumb)
AND
(qryBOMA.FPN = qryBOMB.FPN);

This is then joined to the query that the report pulls from as follows:

SELECT DISTINCT RelReportPastDue.Company, RelReportPastDue.CustPO,
RelReportPastDue.ShortCode, RelReportPastDue.PN,
RelReportPastDue.JobNumb,
RelReportPastDue.LastShipNumber, RelReportPastDue.LastShipDate,
RelReportPastDue.LastQty, RelReportPastDue.CCum,
RelReportPastDue.KKCum,
RelReportPastDue.PastDue, RelReportPastDue.Week1QtyDue, IIf([Wk1Bal] Is
Null,0,[Wk1Bal]) AS BalWk1, RelReportPastDue.BegWk2,
RelReportPastDue.EndWk2,
RelReportPastDue.Wk2QtyDue, IIf([Wk2Bal] Is Null,0,[Wk2Bal]) AS BalWk2,
RelReportPastDue.BegWk3, RelReportPastDue.EndWk3,
RelReportPastDue.Wk3QtyDue,
IIf([Wk3Bal] Is Null,0,[Wk3Bal]) AS BalWk3, RelReportPastDue.BegWk4,
RelReportPastDue.EndWk4, RelReportPastDue.Wk4QtyDue, IIf([Wk4Bal] Is
Null,0,[Wk4Bal]) AS BalWk4, RelReportPastDue.BegWk3,
RelReportPastDue.BegWk4,
RelReportPastDue.BegWk2, CurrentInventoryFromTUFPNs.[Sum Of
InventoryQty],
RelReportPastDue.StandardPack, qryBOM.Inv
FROM (RelReportPastDue LEFT JOIN CurrentInventoryFromTUFPNs ON
(RelReportPastDue.PN = CurrentInventoryFromTUFPNs.InventoryPN) AND
(RelReportPastDue.JobNumb = CurrentInventoryFromTUFPNs.JobNumber)) LEFT
JOIN
qryBOM ON (RelReportPastDue.JobNumb = qryBOM.JobNumb) AND
(RelReportPastDue.PN = qryBOM.FPN);





:

How many characters do you have in your expression in your query
column?
Your structure looks to be a bit un-normalized.

Can you provide the complete SQL view of your query?

--
Duane Hookom
MS Access MVP


I have a field from a query where I have concatenated multiple fields
to
show
(Part Numbers, Operations and Inventory amounts on hand) for all
components
of a finished part number where I'm using ** as a spacer between
each
set.

([P1]&"-"&[O1]&"/"&[Inv1]&"**"&[P2]&"-"&[O2]&"/"&[Inv2]&"**"&[P3]....ect.
up
to 25 sets). This concatenated field is attached in a query by a
job
and
finished part number.

I've done this to be able to include this info as a single line in a
very
complicated report. The report has no groupings, but is sorted by
Job
Number
and Part Number.

All the info I'm seeking is showing up in the query, but is being
truncated
on the report.

Any idea what I can do to show all the data from the query field on
the
report?


:

neenmarie wrote:
This is a field in a query to combines both text and number
fields.
How can I change the combined field to a memo type?

Truncation should not be a problem in the scenario you are
describing.
What
is the query expression?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top