K
Karen
I'm not sure I want to be using a query to do this but I'll put it here
anyway...
I have two tables that I need to reference to print a Certificate.
The first table is FillRecord (the fields are productno, productlot,
bulkno, bulklot, etc). In this table we record the use of the Bulk so
a Bulk will appear in several Products.
productno productlot bulkno bulklot
2209-L 425425 2209 425
2209-L 425501 2209 425
2786-E 4051212 2505 405
2786-H 106331 2505 106
The second table is the QualityRecord for the Bulk. In this table we
record the production of the Bulk (the fields include bulkno, bulklot,
madedate, etc).
bulkno bulklot madedate
2505 106 04/10/06
2505 405 09/15/06
2209 425 09/28/05
What I want to do is have the user enter the productno and the
productlot into a dialog form and get (print) a certificate for the
Bulk used to fill the Product. The certificate is a report with the
query as its record source.
My current query is as follows:
SELECT tblfillrecord.productno, tblfillrecord.bulklot,
tblfillrecord.bulkno, tblfillrecord.productlot,
tblQualityRecord.madedate
FROM tblfillrecord INNER JOIN tblQualityRecord ON (tblfillrecord.bulkno
= tblQualityRecord.bulkno) AND (tblfillrecord.bulklot =
tblQualityRecord.bulklot)
WHERE (((tblfillrecord.productno) Like [forms]![frm cert dialog
box]![text0]) AND ((tblfillrecord.bulklot)=IIf(Len([forms]![frm cert
dialog box]![text2])=Val(8) Or (Len([forms]![frm cert dialog
box]![text2])=Val(7) And Mid([forms]![frm cert dialog
box]![text2],4,1)<>'1'),Left([forms]![frm cert dialog
box]![text2],4),Left([forms]![frm cert dialog box]![text2],3))));
With this I get 2 records for 2209 when I only want one.
Thanks for any help you can give me.
Karen
anyway...
I have two tables that I need to reference to print a Certificate.
The first table is FillRecord (the fields are productno, productlot,
bulkno, bulklot, etc). In this table we record the use of the Bulk so
a Bulk will appear in several Products.
productno productlot bulkno bulklot
2209-L 425425 2209 425
2209-L 425501 2209 425
2786-E 4051212 2505 405
2786-H 106331 2505 106
The second table is the QualityRecord for the Bulk. In this table we
record the production of the Bulk (the fields include bulkno, bulklot,
madedate, etc).
bulkno bulklot madedate
2505 106 04/10/06
2505 405 09/15/06
2209 425 09/28/05
What I want to do is have the user enter the productno and the
productlot into a dialog form and get (print) a certificate for the
Bulk used to fill the Product. The certificate is a report with the
query as its record source.
My current query is as follows:
SELECT tblfillrecord.productno, tblfillrecord.bulklot,
tblfillrecord.bulkno, tblfillrecord.productlot,
tblQualityRecord.madedate
FROM tblfillrecord INNER JOIN tblQualityRecord ON (tblfillrecord.bulkno
= tblQualityRecord.bulkno) AND (tblfillrecord.bulklot =
tblQualityRecord.bulklot)
WHERE (((tblfillrecord.productno) Like [forms]![frm cert dialog
box]![text0]) AND ((tblfillrecord.bulklot)=IIf(Len([forms]![frm cert
dialog box]![text2])=Val(8) Or (Len([forms]![frm cert dialog
box]![text2])=Val(7) And Mid([forms]![frm cert dialog
box]![text2],4,1)<>'1'),Left([forms]![frm cert dialog
box]![text2],4),Left([forms]![frm cert dialog box]![text2],3))));
With this I get 2 records for 2209 when I only want one.
Thanks for any help you can give me.
Karen