BTW Dale - for any curiosity you might have here's the SQL for the
other query that I needed to join to the query in question. It's bound
to be screwy, too but it works! Its underlying queries do some equally
screwy things, too. I guess this is the result akin to asking a boy to
do a man's job!
As I noted I couldn't join the queries because it resulted in the "too
complex" error. The solution here to "push" the calcuated value to a
table works and I'll look to improve things in the near future.
SELECT tblFGPhysicalAttributes.txtProfileID, tblProfiles.Version,
tblProfiles.Description, tblProfiles.Status,
qryProfilesGroupsFG.GroupDesc,
tblFGPhysicalAttributes.UnitDescription,
tblFGPhysicalAttributes.ShippedIn, tblFGPhysicalAttributes.UnitCount,
tblFGPhysicalAttributes.SubUnitCount,
tblFGPhysicalAttributes.UnitSize, tblFGPhysicalAttributes.UnitUOM,
IIf([UnitUOM]="Ct.",+Nz([UnitCount],1)*Nz([SubUnitCount],
1)*[UnitSize],Null) AS UnitSizeCt, IIf([UnitUOM]="Ct.",Null,
+IIf([SubUnitCount],[SubUnitCount]*[UnitSize],[UnitSize])) AS
UnitSizeCtc, Nz([UnitSizeCt],[UnitSizeCtc]) AS UnitSizec,
IIf([UnitUOM]="Ct.",[UnitSizec],[UnitCount]) AS UnitCountc,
qryFGsINGsDensAllergens.Densitylbgal,
qryPKProfilesAssociationsPKWTsFGs.SumOfSumOfSUBUNPRODWtlb AS
SUBUNPRODWt,
qryPKProfilesAssociationsPKWTsFGs.SumOfSumOfSUBUNPRODWtlbTotal AS
SUBUNPRODWtTotal,
qryPKProfilesAssociationsPKWTsFGs.SumOfSumOfUNPRODWtlb AS PRODWt,
qryPKProfilesAssociationsPKWTsFGs.SumOfSumOfUNPPRODWtlbTotal AS
PRODWtTotal,
qryPKProfilesAssociationsPKWTsFGs.SumOfSumOfSUBUNPKWtlbTotal AS
SUBUNPKWtTotal, qryPKProfilesAssociationsPKWTsFGs.SumOfSumOfUNPKWtlb
AS UNPKWt, qryPKProfilesAssociationsPKWTsFGs.SumOfSumOfUNPKWtlbTotal
AS UNPKWtTotal, Round([UnitSizec]*[lbConvFactor],6) AS UNNtWtConvlb,
Round([UnitSizec]*[galConvFactor],6) AS UNNtWtConvgal,
IIf(IsNull([SubUnitCount]),[UnitCount]*[PRODWt],
[UnitCount]*[SUBUNPRODWtTotal]) AS UNPRODNtWt, Nz([UNNtWtConvlb],
[UNNtWtConvgal]*[Densitylbgal]) AS UNNtWtNz, Nz([UNPRODNtWt],
[UNNtWtNz]) AS UNNtWtNzc, Round([UNNtWtNzc],6) AS UNNtWt,
Round([UNNtWt]*0.1+[UNNtWt],6) AS UNEstNtWt, IIf(Nz([SUBUNPKWtTotal],
0)<>0,Round([SUBUNPKWtTotal]+[UNEstNtWt],6),Round([SumOfSumOfUNPKWtlb]+
[UNEstNtWt],6)) AS UNEstGrWt, [UNEstGrWt]-[UNEstNtWt] AS UNPKNtWt,
[UNPKNtWt]/[UNEstGrWt] AS UNPKpercent,
qryPKProfilesAssociationsPKWTsFGs.SumOfSumOfCSPKWtlb AS CSPKWt,
qryPKProfilesAssociationsPKWTsFGs.SumOfSumOfCSPKWtlbTotal AS
CSPKWtTotal, Nz([PRODWtTotal],([UnitCount]*[UNNtWt])) AS CSNtWtNz,
Round([CSNtWtNz],6) AS CSNtWt, Round([UnitCount]*[UNEstNtWt],6) AS
CSEstNtWt, Round([UNEstGrWt]*[UnitCount]+[CSPKWtTotal],6) AS
CSEstGrWt, [CSEstGrWt]-[CSEstNtWt] AS CSPKNtWt, [CSPKNtWt]/[CSEstGrWt]
AS CSPKpercent, tblFGPhysicalAttributes.UnitLoadStack,
IIf([UnitLoadStack]="Single","1",IIf([UnitLoadStack]="Double","2",IIf([UnitLoadStack]="Triple","3")))
AS ULStackFactor, tblFGPhysicalAttributes.Ti,
tblFGPhysicalAttributes.Hi, tblFGPhysicalAttributes.AdditionalUnits,
[Ti]*[Hi]+[AdditionalUnits] AS ULTotal,
[UnitCountc]*[ULTotal]*[ULStackFactor] AS ULSUV, Round([ULL],4) AS
ULLr, Round([ULW],4) AS ULWr, Round([ULHinConv],4) AS ULHinConvr,
qryPKProfilesAssociationsPKWTsFGsULDims.ULcuin,
qryPKProfilesAssociationsPKWTsFGsULDims.ULcuft,
[SumOfSumOfPTPKWtlb]*[ULStackFactor] AS PTPKWt,
[SumOfSumOfPTPKWtlbTotal]*[ULStackFactor] AS PTPKWtTotal,
IIf([UnitUOM]="Ct.",Round([PRODWt]*[ULSUV],
6),IIf(IsNull([CSNtWt]),Round([UNNtWt]*[ULTotal],
6),Round([UNNtWt]*[ULSUV],6))) AS ULNtWt,
IIf([UnitUOM]="Ct.",Round([ULNtWt]*0.1+[ULNtWt],
6),IIf(IsNull([CSNtWt]),Round([UNEstNtWt]*[ULTotal],
6),Round([UNEstNtWt]*[ULSUV],6))) AS ULEstNtWt,
IIf([CSEstGrWt],Round([CSEstGrWt]*[ULTotal]*[ULStackFactor]+
[PTPKWtTotal],6),Round([UNEstGrWt]*[ULTotal]*[ULStackFactor]+
[PTPKWtTotal],6)) AS ULEstGrWt, [ULEstGrWt]-[ULEstNtWt] AS ULPKNtWt,
[ULPKNtWt]/[ULEstGrWt] AS ULPKpercent,
tblProfilesStorage.StorageCondition, Round([UNL],4) AS UNLr,
Round([UNW],4) AS UNWr, Round([UNH],4) AS UNHr,
qryPKWTCalcsUNCSDims.UNcuin, qryPKWTCalcsUNCSDims.UNcuft, Round([CSL],
4) AS CSLr, Round([CSW],4) AS CSWr, Round([CSHc],4) AS CSHcr,
qryPKWTCalcsUNCSDims.CScuinc, qryPKWTCalcsUNCSDims.CScuftc,
qryFGsINGsDensAllergens.ProfilesAssociations AS FAING,
qryFGsINGsDensAllergens.Allergens, qryFGProcessingDrumNumbers.Drums
FROM ((((((((tblFGPhysicalAttributes LEFT JOIN tblUOMVolumeLiquidMass
ON tblFGPhysicalAttributes.UnitUOM =
tblUOMVolumeLiquidMass.txtUOMVolumeLiquid) LEFT JOIN
tblProfilesStorage ON tblFGPhysicalAttributes.txtProfileID =
tblProfilesStorage.txtProfileID) LEFT JOIN
qryPKProfilesAssociationsPKWTsFGs ON
tblFGPhysicalAttributes.txtProfileID =
qryPKProfilesAssociationsPKWTsFGs.txtProfileID) LEFT JOIN
qryPKProfilesAssociationsPKWTsFGsULDims ON
tblFGPhysicalAttributes.txtProfileID =
qryPKProfilesAssociationsPKWTsFGsULDims.txtProfileID) INNER JOIN
qryPKWTCalcsUNCSDims ON tblFGPhysicalAttributes.txtProfileID =
qryPKWTCalcsUNCSDims.txtProfileID) LEFT JOIN
qryFGProcessingDrumNumbers ON tblFGPhysicalAttributes.txtProfileID =
qryFGProcessingDrumNumbers.txtProfileID) LEFT JOIN qryProfilesGroupsFG
ON tblFGPhysicalAttributes.txtProfileID =
qryProfilesGroupsFG.txtProfileID) LEFT JOIN qryFGsINGsDensAllergens ON
tblFGPhysicalAttributes.txtProfileID =
qryFGsINGsDensAllergens.txtProfileID) INNER JOIN tblProfiles ON
tblFGPhysicalAttributes.txtProfileID = tblProfiles.txtProfileID;
J,
First of all, I'm not sure how well your query will work as is, because in
your computation for CompTotalDiff, you are computing a numeric value against
a string in the expression:
IIF([CompTotal] = "0" or [CompTotal] = "100"
You also don't have the proper number of paramters in the nested IIF
function for the same value, the computation for MoistureC will end up as
NULL if either of the other values is Null. One of the real advantagesof
writing this as a function is that it makes you think through your logic.
Back to topic. In Access you can create user defined functions that perform
calculations for you and return a value back to whatever event called them.
For example, I could create a function to convert degrees Farenheit to
degrees Celsius. To do this, I would select the Modules tab in the Access
database window (or nav pane) and would create a new module. Then I would
add some code to it that looks something like:
Public Function fnFarToCelsius(Degrees as double) as double
fnFarToCelsius = (Degrees - 32) * 5 / 9
End Function
I could then call this function from anywhere in my application, a form, a
query, or in the debug window. All I would have to do is pass it a degree
value and it would return the Celsius equivalent.
Likewise, you could create a function to perform your calculation for you..
It might start out something like:
Public Function fnVDenlbgalcuft(ServingWeight as Variant, SWUOM as Variant,
ProcessTemp as Variant, Moisture as Variant, Protein as Variant, Fat as
Variant, Carbs as Variant, Fiber as Variant, Ash as Variant) as single
Dim CompTotal as single
Dim CompTotalDiff as Variant
Dim MoistureC as Variant
CompTotal = NZ(Moisture, 0) + NZ(Protein, 0) + NZ(Fat, 0) _
+ NZ(Carbs, 0) + NZ(Fiber, 0) + NZ(Ash, 0)
'I'm not exactly sure what you are trying to do here.
'If CompTotal is 50, then CompTotalDiff = -50, is that what youwant?
IF CompTotal = 0 OR CompTotal = 100 Then
CompTotalDiff = NULL
Elseif CompTotal <> 100 then
CompTotalDiff = CompTotal - 100
Else
CompTotalDiff = ????
Endif
'I've added the NZ( ) wrapper around these. Otherwise, if
'either value was NULL, MoistureC would be NULL
MoistureC = NZ(Moisture, 0) - NZ(CompTotalDiff, 0)
'This next part is even screwier
'in your query, you are missing a parameter in the IIF clause
'You are also making a boolean comparison, with numeric values
IF MoistureC Then
MoistureCC = MoistureC
Else
MoistureCC = MoistureC
Endif
'This is all the further I'm going to go
fnVDenlbgalcuft = ????
End Function
You can see that as I went through your query and tried to define the
variables that you created on the fly, I found some problems. I would
recommend you continue to pursue this, as this code will be much easier to
read and to modify.
--
HTH
Dale
Don''t forget to rate the post if it was helpful!
email address is invalid
Please reply to newsgroup only.
Thanks, Dale. What do you mean by "function"?
- Show quoted text -
