skorpionkz
Well-known Member
- Joined
- Oct 1, 2013
- Messages
- 1,171
- Office Version
- 2016
Hi all,
I have query in Access that prepares report for me. I also have some tool build in excel that running various reports from Access and some calculation on top of it.
Therefore I would like not only extract report but be able to modify it a little. Mainly change value for WHERE.
Bellow is query I have in Access I can manually change Key_table.STATUS and/or REF_UCDB_CAPEX.[UCDB version] and it works fine.
The issue is that it is not me who running those reports and they need to have ability to change status and VBA in excel have variable for UCDB version.
I tough I can just copy this sql to excel and then just use variables like that (Key_table.STATUS) = " & Chr(34) & MYSTATUS & Chr(34), but VBA doesn't like it.
I am then reading result of this report into recordset and loop through it to populate class objects in VBA, which triggers some calculation.
Any advice on how can I provide those variable to sql code?
Thank you for help
I have query in Access that prepares report for me. I also have some tool build in excel that running various reports from Access and some calculation on top of it.
Therefore I would like not only extract report but be able to modify it a little. Mainly change value for WHERE.
Bellow is query I have in Access I can manually change Key_table.STATUS and/or REF_UCDB_CAPEX.[UCDB version] and it works fine.
The issue is that it is not me who running those reports and they need to have ability to change status and VBA in excel have variable for UCDB version.
I tough I can just copy this sql to excel and then just use variables like that (Key_table.STATUS) = " & Chr(34) & MYSTATUS & Chr(34), but VBA doesn't like it.
I am then reading result of this report into recordset and loop through it to populate class objects in VBA, which triggers some calculation.
Any advice on how can I provide those variable to sql code?
Code:
SELECT
fct_CostCurves.[Maximo Code],
fct_CostCurves.[Updated by],
fct_CostCurves.[Updated on],
fct_IPDetails.[Asset Reference],
fct_CostCurves.LookUp_Key,
fct_IPDetails.[IP TITLE],
fct_IPDetails.Domain,
fct_CostCurves.[Cost Curve Reference],
ref_UCDB_CAPEX.Name AS [Cost Curve Name],
fct_CostCurves.[Action Type],
ref_UCDB_CAPEX.[Yardstick x] AS [Primary Yardstick],
ref_UCDB_CAPEX.[Yardstick y] AS [Secondary Yardstick],
fct_CostCurves.[Existing Primary Yard Stick Qty],
fct_CostCurves.[Existing Secondary Yard Stick Qty],
fct_CostCurves.[Existing Unit Quantity],
fct_CostCurves.[Proposed Primary Yard Stick Qty],
fct_CostCurves.[Proposed Secondary Yard Stick Qty],
fct_CostCurves.[Proposed Unit Quantity],
fct_CostCurves.[Total CAPEX],
fct_CostCurves.Carbon,
fct_CostCurves.Power,
fct_CostCurves.Labour,
fct_CostCurves.Chemical,
fct_CostCurves.[Materials and Consumables],
fct_CostCurves.[Incremental OPEX]
FROM
(fct_IPDetails INNER JOIN
(fct_CostCurves INNER JOIN Key_table ON fct_CostCurves.LookUp_Key = Key_table.LookUp_Key)
ON (fct_IPDetails.LookUp_Key = Key_table.[Project Code])
AND (fct_IPDetails.[Intervention] = Key_table.[Intervention]))
INNER JOIN ref_UCDB_CAPEX ON fct_CostCurves.[Cost Curve Reference] = ref_UCDB_CAPEX.[Cost Model ID/Ref]
WHERE (((Key_table.STATUS) = "IS0011") And ((fct_CostCurves.Selected) = True) And ((fct_IPDetails.Selected) = True) And ((REF_UCDB_CAPEX.[UCDB version]) = "5.03"))
ORDER BY fct_CostCurves.LookUp_Key;
Thank you for help