Using values in a table as criteria in a query

[exRP12Nuke]

Hey guys,

I have a query that I would like to automatically update the criteria in it to equal values from another table. Here is the SQL for my current query.


SELECT LIBE49H_MSPHDRS0.CUNO, LIBE49H_MSPHDRS0.EQMFM1, tblCntFleet.SN, LIBE49H_MSPHDRS0.SVMTHR, LIBE49H_MSPHDRS0.WONO, LIBE49H_MSPSEGS0.WOSGNO, LIBE49H_MSPSEGS0.CSCC, LIBE49H_MSPSEGS0.CPTCD, LIBE49H_MSPSEGS0.JBCD, LIBE49H_MSPSEGS0.CPTMD3, LIBE49H_MSPHDRS0.OPNDT8, LIBE49H_MSPHDRS0.IVDAT8, LIBE49H_MSPSEGS0.TOLBHR, LIBE49H_MSPSEGS0.WARCLI, LIBE49H_MSPSEGS0.PADAMT, LIBE49H_MSPSEGS0.LBDAMT, LIBE49H_MSPSEGS0.MCDAMT, LIBE49H_MSPSEGS0.TLSELL INTO tblHdrSeg
FROM tblCntFleet INNER JOIN (LIBE49H_MSPHDRS0 INNER JOIN LIBE49H_MSPSEGS0 ON LIBE49H_MSPHDRS0.IVNO1 = LIBE49H_MSPSEGS0.IVNO1) ON tblCntFleet.SN = LIBE49H_MSPHDRS0.EQMFSN
[highlight #EF2929]WHERE (((LIBE49H_MSPHDRS0.CUNO)="C331760" Or (LIBE49H_MSPHDRS0.CUNO)="C336970" Or (LIBE49H_MSPHDRS0.CUNO)="C331761" Or (LIBE49H_MSPHDRS0.CUNO)="0004400" Or (LIBE49H_MSPHDRS0.CUNO)="0004500") AND ((tblCntFleet.SN) Like "0ATY*" Or (tblCntFleet.SN) Like "01HL*" Or (tblCntFleet.SN) Like "04AR*" Or (tblCntFleet.SN) Like "0FDB*" Or (tblCntFleet.SN) Like "0SSP*" Or (tblCntFleet.SN) Like "0442*" Or (tblCntFleet.SN) Like "0JSM*" Or (tblCntFleet.SN) Like "0LAJ*" Or (tblCntFleet.SN) Like "0ERM*")[/highlight] AND ((LIBE49H_MSPHDRS0.IVDAT8) Between Val(Format([Forms]![frmMain]![StartDate],"yyyymmdd")) And Val(Format([Forms]![frmMain]![EndDate],"yyyymmdd"))) AND ((LIBE49H_MSPHDRS0.HSTI)<>"H") AND ((LIBE49H_MSPSEGS0.WOOPNO)=""));

The highlighted area is where I am having an issue. Instead of "LIBE49H_MSPSEGS0 = C331760", etc...I would like to be able to essentially say Where LIBE49H_MSPHDRS0 = [tblCntFleet]![CntCUNO] where the query uses criteria of the CntCUNO values in the tblCntFleet.

Is this possible?

Thanks in advance!

 

[dhookom]

You could create records in a table with the hard-coded values and then use the table with a join in your query. Then to change the criteria change the records in the table. I don't understand the structure of tblCntFleet and how it would figure in to your solution.

Duane
Hook'D on Access
MS Access MVP