G
Guest
I am trying to create a field that I call "Size" on my Molding Ticket Report.
I am using the table/field [wrktblMoldPlanner].[FinishedItemId] which is in
the select query behind the report. In the string samples below includes the
size of the shoe. The problem is there are two different formats. On some,
the 6 & 7 digits report the size and on others the 7 & 8 digits report the
size. I have made the following expression to ferret out the size. The
expression runs, but the result says "#Error"
Size:
IIf(Mid([wrktblMoldPlanner].[FinishedItemId],6,1='4'),Mid([wrktblMoldPlanner].[FinishedItemId],7,2),IIf(Mid([wrktblMoldPlanner].[FinishedItemId],6,1='0'),Mid([wrktblMoldPlanner].[FinishedItemId],6,2)))
Samples of string.
FinishedItemId
8430-413-3269-7
FinishedItemId
8430-080-3273-0
I also thought of adding a field to a table called tblMolds identifying the
size for this purpose but could not figure out how it is possible to link
this table to the tables in the select query behind the report as none of the
fields link.
tblMolds, field name and data type
IdIdx - Auto number
Descn - Text
Machine - Number
CyclesPerHour - Number
Report Select query:
SELECT tblPickList.*, wrktblMoldPlanner.StartingDate,
wrktblMoldSchedule.MachineType, wrktblMoldPlanner.StationId,
wrktblMoldPlanner.MachineType, wrktblMoldPlanner.MoldDescn,
wrktblMoldPlanner.MoldIdx, wrktblMoldPlanner.FinishedItemId,
Left([DueDate],2) AS Mo,
IIf(Mid([wrktblMoldPlanner].[FinishedItemId],6,1='4'),Mid([wrktblMoldPlanner].[FinishedItemId],7,2),IIf(Mid([wrktblMoldPlanner].[FinishedItemId],6,1='0'),Mid([wrktblMoldPlanner].[FinishedItemId],6,2))) AS [Size]
FROM tblOrderLines INNER JOIN ((tblPickList INNER JOIN wrktblMoldPlanner ON
tblPickList.PickListNum = wrktblMoldPlanner.PickListNum) INNER JOIN
wrktblMoldSchedule ON wrktblMoldPlanner.MoldIdx = wrktblMoldSchedule.MoldIdx)
ON (tblOrderLines.OrderID = tblPickList.OrderNum) AND (tblOrderLines.LineID =
tblPickList.OrderLine)
WHERE
(((wrktblMoldPlanner.StartingDate)=DMin("startingDate","wrktblMoldPlanner"))
AND ((tblPickList.Status)=2));
Are any of this ideas on the right track (although neither seems to work) or
is there a better idea?
I am using the table/field [wrktblMoldPlanner].[FinishedItemId] which is in
the select query behind the report. In the string samples below includes the
size of the shoe. The problem is there are two different formats. On some,
the 6 & 7 digits report the size and on others the 7 & 8 digits report the
size. I have made the following expression to ferret out the size. The
expression runs, but the result says "#Error"
Size:
IIf(Mid([wrktblMoldPlanner].[FinishedItemId],6,1='4'),Mid([wrktblMoldPlanner].[FinishedItemId],7,2),IIf(Mid([wrktblMoldPlanner].[FinishedItemId],6,1='0'),Mid([wrktblMoldPlanner].[FinishedItemId],6,2)))
Samples of string.
FinishedItemId
8430-413-3269-7
FinishedItemId
8430-080-3273-0
I also thought of adding a field to a table called tblMolds identifying the
size for this purpose but could not figure out how it is possible to link
this table to the tables in the select query behind the report as none of the
fields link.
tblMolds, field name and data type
IdIdx - Auto number
Descn - Text
Machine - Number
CyclesPerHour - Number
Report Select query:
SELECT tblPickList.*, wrktblMoldPlanner.StartingDate,
wrktblMoldSchedule.MachineType, wrktblMoldPlanner.StationId,
wrktblMoldPlanner.MachineType, wrktblMoldPlanner.MoldDescn,
wrktblMoldPlanner.MoldIdx, wrktblMoldPlanner.FinishedItemId,
Left([DueDate],2) AS Mo,
IIf(Mid([wrktblMoldPlanner].[FinishedItemId],6,1='4'),Mid([wrktblMoldPlanner].[FinishedItemId],7,2),IIf(Mid([wrktblMoldPlanner].[FinishedItemId],6,1='0'),Mid([wrktblMoldPlanner].[FinishedItemId],6,2))) AS [Size]
FROM tblOrderLines INNER JOIN ((tblPickList INNER JOIN wrktblMoldPlanner ON
tblPickList.PickListNum = wrktblMoldPlanner.PickListNum) INNER JOIN
wrktblMoldSchedule ON wrktblMoldPlanner.MoldIdx = wrktblMoldSchedule.MoldIdx)
ON (tblOrderLines.OrderID = tblPickList.OrderNum) AND (tblOrderLines.LineID =
tblPickList.OrderLine)
WHERE
(((wrktblMoldPlanner.StartingDate)=DMin("startingDate","wrktblMoldPlanner"))
AND ((tblPickList.Status)=2));
Are any of this ideas on the right track (although neither seems to work) or
is there a better idea?