need help on vba to open report

[tomtom1126]

I need help to crate the vba as below:

query name : qrycost
field in query : sccd

report name:
1. rptcosthkg_a / 2. rptcosthkg_b / 3. rptcostszp_a / 4. rptcostszp_b

if sccd like "HKG" then
open report 1. rptcosthkg_a
else
open report 2. rptosthkg_b

if sccd like "SZP" then
open report 3. rptcostszp_a
else
open report 4. rptcostszp_b

end

thanks so much for your great help

Tom

 

[bob fitz]

Can you show us the SQL statement of query "qrycost"

 

[tomtom1126]

Dear Bob,

below is the query "qrypreviewcostrpt


SELECT qryCntrCost.vvd, qryCntrCost.[Container No], qryAll.tsCd, qryCntrCost.bkg, qryAll.[In Terminal Time], qryCntrCost.covdSt, qryCntrCost.rmk, IIf(([demcount]<>"" And [qrycntrcost].[covdmov] Is Not Null),[demcount]+[qrycntrcost].[covdmov],[demcount] & [qrycntrcost].[covdmov]) AS [count], IIf([demcount]<>"" And [qrycntrcost].[covdmov] Is Not Null,"Over Time Storage And Extra Lifting",IIf([demcount]<>"","Over Time Storage",IIf([qrycntrcost].[covdmov] Is Not Null,"Extra Lifting",""))) AS costType, IIf([qryall].[covdac]<>"",[qryall].[covdac],IIf([qryall].[demac]<>"",[qryall].[demac],"")) AS acType, Mid([stcd],3,4) AS ststus, qryCntrCost.covdCostT, qryCntrCost.covdCostA, qryCntrCost.demCostT, qryCntrCost.demCostA, qryVslSkd.lane, qryVslSkd.cct, qryVslSkd.etb, qryVslSkd.etd, qryVslSkd.delay, qryAll.scCd, qryCntrCost.tariff, qryCntrCost.actual
FROM qryVslSkd INNER JOIN (qryCntrCost INNER JOIN qryAll ON qryCntrCost.[Container No] = qryAll.[Container No]) ON qryVslSkd.vvd = qryAll.vvd
WHERE (((IIf(([demcount]<>"" And [qrycntrcost].[covdmov] Is Not Null),[demcount]+[qrycntrcost].[covdmov],[demcount] & [qrycntrcost].[covdmov]))<>""));
"

Regards
Tom

 

[bob fitz]

I'm a bit confused. In your first post you said:

query name : qrycost
field in query : sccd

but you have now posted the SQL of a query called "qrypreviewcostrpt".

 

[tomtom1126]

Sorry Bob,

At the first post, I just want to make it simple so I type qryCost.

Actual query name : qryPreviewcostRpt
Field in the query : scCd (this one I want to lookup to decide which report should be open)

sorry for your inconvenience

Regards
Tom