|
|
If I have a price calculation based on a complicated query with aggregate and
subqueries which uses the part ID on the input form as the filter for the
subqueries, the part price calculates fine, but if the material costs change
and I wish to do a "batch" update of the part prices I need to loop through
all the records in that form. Since the queries all have
"forms!frmPart!PartID" as their criteria, how do I create this update?
Thanks.
|
|
Hi Mary
Can you not create queries without the criteria referring to the form?
--
Good Luck :-)
Graham Mandeno [Access MVP]
Auckland, New Zealand
"Mary Fran" <MaryFran[ at ]discussions.microsoft.com> wrote in message
news:A1FE222C-488A-430E-A301-159F9271D320[ at ]microsoft.com...
[Quoted Text] > If I have a price calculation based on a complicated query with aggregate
> and
> subqueries which uses the part ID on the input form as the filter for the
> subqueries, the part price calculates fine, but if the material costs
> change
> and I wish to do a "batch" update of the part prices I need to loop
> through
> all the records in that form. Since the queries all have
> "forms!frmPart!PartID" as their criteria, how do I create this update?
> Thanks.
>
|
|
Not really because the queries use fields which are formulas that are then
evaluated in the query - for example, the thickness of a given style's part
is determined by the evaluation of the formula
"IIf(dlookup("[WEIGHT]","qryPartComponents")>=2000,2.5,1.75)" and
qryPartComponents filters for that part. Does that make sense?
"Graham Mandeno" wrote:
[Quoted Text] > Hi Mary
>
> Can you not create queries without the criteria referring to the form?
> --
> Good Luck :-)
>
> Graham Mandeno [Access MVP]
> Auckland, New Zealand
>
> "Mary Fran" <MaryFran[ at ]discussions.microsoft.com> wrote in message
> news:A1FE222C-488A-430E-A301-159F9271D320[ at ]microsoft.com...
> > If I have a price calculation based on a complicated query with aggregate
> > and
> > subqueries which uses the part ID on the input form as the filter for the
> > subqueries, the part price calculates fine, but if the material costs
> > change
> > and I wish to do a "batch" update of the part prices I need to loop
> > through
> > all the records in that form. Since the queries all have
> > "forms!frmPart!PartID" as their criteria, how do I create this update?
> > Thanks.
> >
>
>
|
|
Hi Mary
I'm not sure that I follow you, but if you remove the PartID filter from
qryPartComponents and then add qryPartComponents to your main query, joined
by PartID, will that not work?
--
Good Luck :-)
Graham Mandeno [Access MVP]
Auckland, New Zealand
"Mary Fran" <MaryFran[ at ]discussions.microsoft.com> wrote in message
news:60961EF0-E001-495F-913C-8A5805E904B0[ at ]microsoft.com...
[Quoted Text] > Not really because the queries use fields which are formulas that are then
> evaluated in the query - for example, the thickness of a given style's
> part
> is determined by the evaluation of the formula
> "IIf(dlookup("[WEIGHT]","qryPartComponents")>=2000,2.5,1.75)" and
> qryPartComponents filters for that part. Does that make sense?
>
> "Graham Mandeno" wrote:
>
>> Hi Mary
>>
>> Can you not create queries without the criteria referring to the form?
>> --
>> Good Luck :-)
>>
>> Graham Mandeno [Access MVP]
>> Auckland, New Zealand
>>
>> "Mary Fran" <MaryFran[ at ]discussions.microsoft.com> wrote in message
>> news:A1FE222C-488A-430E-A301-159F9271D320[ at ]microsoft.com...
>> > If I have a price calculation based on a complicated query with
>> > aggregate
>> > and
>> > subqueries which uses the part ID on the input form as the filter for
>> > the
>> > subqueries, the part price calculates fine, but if the material costs
>> > change
>> > and I wish to do a "batch" update of the part prices I need to loop
>> > through
>> > all the records in that form. Since the queries all have
>> > "forms!frmPart!PartID" as their criteria, how do I create this update?
>> > Thanks.
>> >
>>
>>
|
|
If the part price is a calculation then you would have to update the items
that are not calculations.
In this case all you should have to do is update the material cost
Mary Fran wrote:
[Quoted Text] > If I have a price calculation based on a complicated query with
> aggregate and subqueries which uses the part ID on the input form as
> the filter for the subqueries, the part price calculates fine, but if
> the material costs change and I wish to do a "batch" update of the
> part prices I need to loop through all the records in that form.
> Since the queries all have "forms!frmPart!PartID" as their criteria,
> how do I create this update? Thanks.
|
|
Sorry so long in getting back to this issue but if I'm still trying to
resolve it. Before I go any further, if a field in tblStyleComponents named
Length_formula has "[Length]+1.5", meaning for that style the calculated
component length is the part length+1.5", as its contents, I thought I could
use the eval function in a query to determine the new length - no?
"Mike Painter" wrote:
[Quoted Text] > If the part price is a calculation then you would have to update the items
> that are not calculations.
> In this case all you should have to do is update the material cost
>
> Mary Fran wrote:
> > If I have a price calculation based on a complicated query with
> > aggregate and subqueries which uses the part ID on the input form as
> > the filter for the subqueries, the part price calculates fine, but if
> > the material costs change and I wish to do a "batch" update of the
> > part prices I need to loop through all the records in that form.
> > Since the queries all have "forms!frmPart!PartID" as their criteria,
> > how do I create this update? Thanks.
>
>
>
|
|
Hi Mary
No. AFAIK it's not possible to do this in a query.
Let's take a step back...
Where does the 1.5 come from?
Does every formula have the form [Length]+constant?
If so, how about storing the "constant" in another field and adding the two
fields?
If not, what other forms can a formula take?
Is it possible to contrive a generic formula that works for all cases?
--
Good Luck :-)
Graham Mandeno [Access MVP]
Auckland, New Zealand
"Mary Fran" <MaryFran[ at ]discussions.microsoft.com> wrote in message
news:9E718EC9-6061-41BD-BA08-852B12DCA387[ at ]microsoft.com...
[Quoted Text] > Sorry so long in getting back to this issue but if I'm still trying to
> resolve it. Before I go any further, if a field in tblStyleComponents
> named
> Length_formula has "[Length]+1.5", meaning for that style the calculated
> component length is the part length+1.5", as its contents, I thought I
> could
> use the eval function in a query to determine the new length - no?
>
>
> "Mike Painter" wrote:
>
>> If the part price is a calculation then you would have to update the
>> items
>> that are not calculations.
>> In this case all you should have to do is update the material cost
>>
>> Mary Fran wrote:
>> > If I have a price calculation based on a complicated query with
>> > aggregate and subqueries which uses the part ID on the input form as
>> > the filter for the subqueries, the part price calculates fine, but if
>> > the material costs change and I wish to do a "batch" update of the
>> > part prices I need to loop through all the records in that form.
>> > Since the queries all have "forms!frmPart!PartID" as their criteria,
>> > how do I create this update? Thanks.
>>
>>
>>
|
|
In a query you could have a calculated field Length_formula:[Length]+1.5
(Note the ":")
You could also have it in a field on a form but it is better in a query
You could run an update query which updated all Length_formula with
"[Length]+1.5"
But you can't have that in a table.
It would proabably be better to have a field that contained this 1.5 value
unless you add 1.5 to all lengths.
Even then I would not hard code a constant. but would look it up in a
"Company Table" so when they decide it's 1.6 you won't have a lot of work to
do.
Mary Fran wrote:
[Quoted Text] > Sorry so long in getting back to this issue but if I'm still trying to
> resolve it. Before I go any further, if a field in
> tblStyleComponents named Length_formula has "[Length]+1.5", meaning
> for that style the calculated component length is the part
> length+1.5", as its contents, I thought I could use the eval
> function in a query to determine the new length - no?
>
>
> "Mike Painter" wrote:
>
>> If the part price is a calculation then you would have to update the
>> items that are not calculations.
>> In this case all you should have to do is update the material cost
>>
>> Mary Fran wrote:
>>> If I have a price calculation based on a complicated query with
>>> aggregate and subqueries which uses the part ID on the input form as
>>> the filter for the subqueries, the part price calculates fine, but
>>> if the material costs change and I wish to do a "batch" update of
>>> the part prices I need to loop through all the records in that form.
>>> Since the queries all have "forms!frmPart!PartID" as their criteria,
>>> how do I create this update? Thanks.
|
|
Mary Fran wrote:
[Quoted Text] >Sorry so long in getting back to this issue but if I'm still trying to
>resolve it. Before I go any further, if a field in tblStyleComponents named
>Length_formula has "[Length]+1.5", meaning for that style the calculated
>component length is the part length+1.5", as its contents, I thought I could
>use the eval function in a query to determine the new length - no?
You could use the clumsy calculated field expression:
Eval(Replace(Replace(tblStyleComponents.Length_formula,
"[Length]", partlengthfield), """", ""))
But this idea rapidly becomes too cumbersome to be practical
with more than just a few things to replace and/or you can
use other units of length besides inches.
--
Marsh
MVP [MS Access]
|
|
Thank you all for your suggestions - they have prompted me to provide further
clarification. [Length]+1.5 is simply the length formula for one style in a
table of over 100 styles. I have fields for component id, quantity, length,
width and height for each component record in tblStyleComponents. To
calculate the component dimensions I then have a query with an example field
of: EVAL_LENGTH:
IIf([LENGTH_TYPE]="C",[LENGTH_CONSTANT],Eval([LENGTH_FORMULA])). The
scenario that works is when LENGTH_FORMULA =
dlookup("[LENGTH]","qryPartComponents")+1.5. This is when I have the query
filtering for one part's components - it prices the parts correctly. But I
can't do this for the table of parts because I'd have to have a "WHERE"
clause in the dlookup and what would that be? Is the bottom line that a
field value can't be evaluated?
"Mike Painter" wrote:
[Quoted Text] > In a query you could have a calculated field Length_formula:[Length]+1.5
> (Note the ":")
> You could also have it in a field on a form but it is better in a query
> You could run an update query which updated all Length_formula with
> "[Length]+1.5"
> But you can't have that in a table.
>
> It would proabably be better to have a field that contained this 1.5 value
> unless you add 1.5 to all lengths.
> Even then I would not hard code a constant. but would look it up in a
> "Company Table" so when they decide it's 1.6 you won't have a lot of work to
> do.
>
>
> Mary Fran wrote:
> > Sorry so long in getting back to this issue but if I'm still trying to
> > resolve it. Before I go any further, if a field in
> > tblStyleComponents named Length_formula has "[Length]+1.5", meaning
> > for that style the calculated component length is the part
> > length+1.5", as its contents, I thought I could use the eval
> > function in a query to determine the new length - no?
> >
> >
> > "Mike Painter" wrote:
> >
> >> If the part price is a calculation then you would have to update the
> >> items that are not calculations.
> >> In this case all you should have to do is update the material cost
> >>
> >> Mary Fran wrote:
> >>> If I have a price calculation based on a complicated query with
> >>> aggregate and subqueries which uses the part ID on the input form as
> >>> the filter for the subqueries, the part price calculates fine, but
> >>> if the material costs change and I wish to do a "batch" update of
> >>> the part prices I need to loop through all the records in that form.
> >>> Since the queries all have "forms!frmPart!PartID" as their criteria,
> >>> how do I create this update? Thanks.
>
>
>
|
|
Hi Mary
What I was asking is, how complex and variable are these formulas?
Would it not be possible to contrive a generic formula that works for all
cases?
If not, I have some other ideas involving VBA code, so post back...
--
Good Luck :-)
Graham Mandeno [Access MVP]
Auckland, New Zealand
"Mary Fran" <MaryFran[ at ]discussions.microsoft.com> wrote in message
news:6B0D4696-C675-4C5D-A779-5B8FC440BDB8[ at ]microsoft.com...
[Quoted Text] > Thank you all for your suggestions - they have prompted me to provide
> further
> clarification. [Length]+1.5 is simply the length formula for one style
> in a
> table of over 100 styles. I have fields for component id, quantity,
> length,
> width and height for each component record in tblStyleComponents. To
> calculate the component dimensions I then have a query with an example
> field
> of: EVAL_LENGTH:
> IIf([LENGTH_TYPE]="C",[LENGTH_CONSTANT],Eval([LENGTH_FORMULA])). The
> scenario that works is when LENGTH_FORMULA =
> dlookup("[LENGTH]","qryPartComponents")+1.5. This is when I have the
> query
> filtering for one part's components - it prices the parts correctly. But
> I
> can't do this for the table of parts because I'd have to have a "WHERE"
> clause in the dlookup and what would that be? Is the bottom line that a
> field value can't be evaluated?
>
>
>
> "Mike Painter" wrote:
>
>> In a query you could have a calculated field Length_formula:[Length]+1.5
>> (Note the ":")
>> You could also have it in a field on a form but it is better in a query
>> You could run an update query which updated all Length_formula with
>> "[Length]+1.5"
>> But you can't have that in a table.
>>
>> It would proabably be better to have a field that contained this 1.5
>> value
>> unless you add 1.5 to all lengths.
>> Even then I would not hard code a constant. but would look it up in a
>> "Company Table" so when they decide it's 1.6 you won't have a lot of work
>> to
>> do.
>>
>>
>> Mary Fran wrote:
>> > Sorry so long in getting back to this issue but if I'm still trying to
>> > resolve it. Before I go any further, if a field in
>> > tblStyleComponents named Length_formula has "[Length]+1.5", meaning
>> > for that style the calculated component length is the part
>> > length+1.5", as its contents, I thought I could use the eval
>> > function in a query to determine the new length - no?
>> >
>> >
>> > "Mike Painter" wrote:
>> >
>> >> If the part price is a calculation then you would have to update the
>> >> items that are not calculations.
>> >> In this case all you should have to do is update the material cost
>> >>
>> >> Mary Fran wrote:
>> >>> If I have a price calculation based on a complicated query with
>> >>> aggregate and subqueries which uses the part ID on the input form as
>> >>> the filter for the subqueries, the part price calculates fine, but
>> >>> if the material costs change and I wish to do a "batch" update of
>> >>> the part prices I need to loop through all the records in that form.
>> >>> Since the queries all have "forms!frmPart!PartID" as their criteria,
>> >>> how do I create this update? Thanks.
>>
>>
>>
|
|
No, Graham, the formulas vary from one component to the next and some involve
conditional statements. For example, the length of the top board component
is simply the length of part + 1.5". But the length of the sides is the
length of the part + 2*the length of the ends + 2*the length of the end
cleats. And that is for one style. Each style has variations of the
dimension calculation. That is why I had hoped to be able to use a formula
as field data and then evaluate it for each part.
If you have a suggestion using VB, please offer - I was trying to use a
recordset and loop through each part table record, but my query to determine
price filtered by the part record on the form (forms!frmMaster!MMKey). What
were you thinking?
"Graham Mandeno" wrote:
[Quoted Text] > Hi Mary
>
> What I was asking is, how complex and variable are these formulas?
>
> Would it not be possible to contrive a generic formula that works for all
> cases?
>
> If not, I have some other ideas involving VBA code, so post back...
>
> --
> Good Luck :-)
>
> Graham Mandeno [Access MVP]
> Auckland, New Zealand
>
> "Mary Fran" <MaryFran[ at ]discussions.microsoft.com> wrote in message
> news:6B0D4696-C675-4C5D-A779-5B8FC440BDB8[ at ]microsoft.com...
> > Thank you all for your suggestions - they have prompted me to provide
> > further
> > clarification. [Length]+1.5 is simply the length formula for one style
> > in a
> > table of over 100 styles. I have fields for component id, quantity,
> > length,
> > width and height for each component record in tblStyleComponents. To
> > calculate the component dimensions I then have a query with an example
> > field
> > of: EVAL_LENGTH:
> > IIf([LENGTH_TYPE]="C",[LENGTH_CONSTANT],Eval([LENGTH_FORMULA])). The
> > scenario that works is when LENGTH_FORMULA =
> > dlookup("[LENGTH]","qryPartComponents")+1.5. This is when I have the
> > query
> > filtering for one part's components - it prices the parts correctly. But
> > I
> > can't do this for the table of parts because I'd have to have a "WHERE"
> > clause in the dlookup and what would that be? Is the bottom line that a
> > field value can't be evaluated?
> >
> >
> >
> > "Mike Painter" wrote:
> >
> >> In a query you could have a calculated field Length_formula:[Length]+1.5
> >> (Note the ":")
> >> You could also have it in a field on a form but it is better in a query
> >> You could run an update query which updated all Length_formula with
> >> "[Length]+1.5"
> >> But you can't have that in a table.
> >>
> >> It would proabably be better to have a field that contained this 1.5
> >> value
> >> unless you add 1.5 to all lengths.
> >> Even then I would not hard code a constant. but would look it up in a
> >> "Company Table" so when they decide it's 1.6 you won't have a lot of work
> >> to
> >> do.
> >>
> >>
> >> Mary Fran wrote:
> >> > Sorry so long in getting back to this issue but if I'm still trying to
> >> > resolve it. Before I go any further, if a field in
> >> > tblStyleComponents named Length_formula has "[Length]+1.5", meaning
> >> > for that style the calculated component length is the part
> >> > length+1.5", as its contents, I thought I could use the eval
> >> > function in a query to determine the new length - no?
> >> >
> >> >
> >> > "Mike Painter" wrote:
> >> >
> >> >> If the part price is a calculation then you would have to update the
> >> >> items that are not calculations.
> >> >> In this case all you should have to do is update the material cost
> >> >>
> >> >> Mary Fran wrote:
> >> >>> If I have a price calculation based on a complicated query with
> >> >>> aggregate and subqueries which uses the part ID on the input form as
> >> >>> the filter for the subqueries, the part price calculates fine, but
> >> >>> if the material costs change and I wish to do a "batch" update of
> >> >>> the part prices I need to loop through all the records in that form.
> >> >>> Since the queries all have "forms!frmPart!PartID" as their criteria,
> >> >>> how do I create this update? Thanks.
> >>
> >>
> >>
>
>
>
|
|
Mary Fran wrote:
[Quoted Text] > No, Graham, the formulas vary from one component to the next and some
> involve conditional statements. For example, the length of the top
> board component is simply the length of part + 1.5". But the length
> of the sides is the length of the part + 2*the length of the ends +
> 2*the length of the end cleats. And that is for one style. Each
> style has variations of the dimension calculation. That is why I had
> hoped to be able to use a formula as field data and then evaluate it
> for each part.
> If you have a suggestion using VB, please offer - I was trying to use
> a recordset and loop through each part table record, but my query to
> determine price filtered by the part record on the form
> (forms!frmMaster!MMKey). What were you thinking?
>
>
Well I had a good start using calculation based on "the length of part "
but got to "length of the end cleats" and realized there does not seem to
be an easy solution.
" But the length of the sides is the length of the part + 2*the length of
the ends +
2*the length of the end cleats" can be written as
"s = lp + 2*le + 2* lec"
If you can with absolute certainty write an equation like this for every
item then it is possible to get a handle on it.
Right now I'm not sure what a part is but guess it is the finished item.
I also note there are only lengths mentioned.
|
|
Hi Mary
I'm assuming you can write a query that has a unique row for each PartID,
and which contains one column for each "variable" used in the calculation,
as well as a column for the formula.
So, your columns would include:
PartID
Length_Formula
Length
EndLength
EndCleatLength
... and no doubt many more
You could write a function that looks up the record for a PartID passed as
an argument, and then replaces each variable name in Length_Formula with the
value in the corresponding field. Finally, use Eval to evaluate what
*ought* to be a valid arithmetic expression and return the result.
The following is "air code" so please forgive any typos and other logic or
syntax errors:
Public Function CalculatePartMetric( _
PartID as Long, _
FormulaField as String) as Variant
Dim rs as DAO.Recordset
Dim aVariables as variant
Dim sExpression as string
Dim sField as string
Dim dValue as Double
Dim i as Integer
On Error Goto ProcErr
Select Case FormulaField
Case "Length_Formula"
aVariables = Array("Length", "EndLength", "EndCleatLength")
' add more "variable" field names as required
' add other formula cases, e.g. "Width_Formula"
Case Else
CalculatePartMetric="Unknown formula field"
Goto ProcEnd
End Select
Set rs = CurrentDb.OpenRecordset ( _
"Select * from YourQuery where PartID=" & PartID, _
dbOpenForwardOnly)
If rs.EOF then
CalculatePartMetric= "Unknown PartID"
Goto ProcEnd
End If
sExpression = rs(FormulaField)
For i = 0 to UBound(aVariables)
sField = "[" & aVariables(i) & "]"
If Instr( sExpression, sField ) <> 0 then
dValue = rs(aVariables(i))
sExpression = Replace( sExpression, sField, dValue )
End If
Next i
CalculatePartMetric= Eval( sExpression )
ProcEnd:
On Error Resume Next
if not rs is Nothing then
rs.Close
Set rs = Nothing
End If
Exit Function
ProcErr:
CalculatePartMetric= Err.Description
Resume ProcEnd
End Function
You should be able to call this from a query this like so:
CalculatedLength: CalculatePartMetric( [PartID], "Length_Formula" )
--
Good Luck :-)
Graham Mandeno [Access MVP]
Auckland, New Zealand
"Mary Fran" <MaryFran[ at ]discussions.microsoft.com> wrote in message
news:E40275C3-B6CB-444F-987E-C1A0394B5D04[ at ]microsoft.com...
[Quoted Text] > No, Graham, the formulas vary from one component to the next and some
> involve
> conditional statements. For example, the length of the top board
> component
> is simply the length of part + 1.5". But the length of the sides is the
> length of the part + 2*the length of the ends + 2*the length of the end
> cleats. And that is for one style. Each style has variations of the
> dimension calculation. That is why I had hoped to be able to use a
> formula
> as field data and then evaluate it for each part.
> If you have a suggestion using VB, please offer - I was trying to use a
> recordset and loop through each part table record, but my query to
> determine
> price filtered by the part record on the form (forms!frmMaster!MMKey).
> What
> were you thinking?
>
>
> "Graham Mandeno" wrote:
>
>> Hi Mary
>>
>> What I was asking is, how complex and variable are these formulas?
>>
>> Would it not be possible to contrive a generic formula that works for all
>> cases?
>>
>> If not, I have some other ideas involving VBA code, so post back...
>>
>> --
>> Good Luck :-)
>>
>> Graham Mandeno [Access MVP]
>> Auckland, New Zealand
>>
>> "Mary Fran" <MaryFran[ at ]discussions.microsoft.com> wrote in message
>> news:6B0D4696-C675-4C5D-A779-5B8FC440BDB8[ at ]microsoft.com...
>> > Thank you all for your suggestions - they have prompted me to provide
>> > further
>> > clarification. [Length]+1.5 is simply the length formula for one
>> > style
>> > in a
>> > table of over 100 styles. I have fields for component id, quantity,
>> > length,
>> > width and height for each component record in tblStyleComponents. To
>> > calculate the component dimensions I then have a query with an example
>> > field
>> > of: EVAL_LENGTH:
>> > IIf([LENGTH_TYPE]="C",[LENGTH_CONSTANT],Eval([LENGTH_FORMULA])). The
>> > scenario that works is when LENGTH_FORMULA =
>> > dlookup("[LENGTH]","qryPartComponents")+1.5. This is when I have the
>> > query
>> > filtering for one part's components - it prices the parts correctly.
>> > But
>> > I
>> > can't do this for the table of parts because I'd have to have a "WHERE"
>> > clause in the dlookup and what would that be? Is the bottom line that
>> > a
>> > field value can't be evaluated?
>> >
>> >
>> >
>> > "Mike Painter" wrote:
>> >
>> >> In a query you could have a calculated field
>> >> Length_formula:[Length]+1.5
>> >> (Note the ":")
>> >> You could also have it in a field on a form but it is better in a
>> >> query
>> >> You could run an update query which updated all Length_formula with
>> >> "[Length]+1.5"
>> >> But you can't have that in a table.
>> >>
>> >> It would proabably be better to have a field that contained this 1.5
>> >> value
>> >> unless you add 1.5 to all lengths.
>> >> Even then I would not hard code a constant. but would look it up in a
>> >> "Company Table" so when they decide it's 1.6 you won't have a lot of
>> >> work
>> >> to
>> >> do.
>> >>
>> >>
>> >> Mary Fran wrote:
>> >> > Sorry so long in getting back to this issue but if I'm still trying
>> >> > to
>> >> > resolve it. Before I go any further, if a field in
>> >> > tblStyleComponents named Length_formula has "[Length]+1.5", meaning
>> >> > for that style the calculated component length is the part
>> >> > length+1.5", as its contents, I thought I could use the eval
>> >> > function in a query to determine the new length - no?
>> >> >
>> >> >
>> >> > "Mike Painter" wrote:
>> >> >
>> >> >> If the part price is a calculation then you would have to update
>> >> >> the
>> >> >> items that are not calculations.
>> >> >> In this case all you should have to do is update the material cost
>> >> >>
>> >> >> Mary Fran wrote:
>> >> >>> If I have a price calculation based on a complicated query with
>> >> >>> aggregate and subqueries which uses the part ID on the input form
>> >> >>> as
>> >> >>> the filter for the subqueries, the part price calculates fine, but
>> >> >>> if the material costs change and I wish to do a "batch" update of
>> >> >>> the part prices I need to loop through all the records in that
>> >> >>> form.
>> >> >>> Since the queries all have "forms!frmPart!PartID" as their
>> >> >>> criteria,
>> >> >>> how do I create this update? Thanks.
>> >>
>> >>
>> >>
>>
>>
>>
|
|
Okay, give me some time to digest what you've suggested here, Graham, and be
watching for further questions regarding your approach - thanks for your
assistance!
"Graham Mandeno" wrote:
[Quoted Text] > Hi Mary
>
> I'm assuming you can write a query that has a unique row for each PartID,
> and which contains one column for each "variable" used in the calculation,
> as well as a column for the formula.
>
> So, your columns would include:
> PartID
> Length_Formula
> Length
> EndLength
> EndCleatLength
> ... and no doubt many more
>
> You could write a function that looks up the record for a PartID passed as
> an argument, and then replaces each variable name in Length_Formula with the
> value in the corresponding field. Finally, use Eval to evaluate what
> *ought* to be a valid arithmetic expression and return the result.
>
> The following is "air code" so please forgive any typos and other logic or
> syntax errors:
>
> Public Function CalculatePartMetric( _
> PartID as Long, _
> FormulaField as String) as Variant
> Dim rs as DAO.Recordset
> Dim aVariables as variant
> Dim sExpression as string
> Dim sField as string
> Dim dValue as Double
> Dim i as Integer
> On Error Goto ProcErr
> Select Case FormulaField
> Case "Length_Formula"
> aVariables = Array("Length", "EndLength", "EndCleatLength")
> ' add more "variable" field names as required
> ' add other formula cases, e.g. "Width_Formula"
> Case Else
> CalculatePartMetric="Unknown formula field"
> Goto ProcEnd
> End Select
> Set rs = CurrentDb.OpenRecordset ( _
> "Select * from YourQuery where PartID=" & PartID, _
> dbOpenForwardOnly)
> If rs.EOF then
> CalculatePartMetric= "Unknown PartID"
> Goto ProcEnd
> End If
> sExpression = rs(FormulaField)
> For i = 0 to UBound(aVariables)
> sField = "[" & aVariables(i) & "]"
> If Instr( sExpression, sField ) <> 0 then
> dValue = rs(aVariables(i))
> sExpression = Replace( sExpression, sField, dValue )
> End If
> Next i
> CalculatePartMetric= Eval( sExpression )
> ProcEnd:
> On Error Resume Next
> if not rs is Nothing then
> rs.Close
> Set rs = Nothing
> End If
> Exit Function
> ProcErr:
> CalculatePartMetric= Err.Description
> Resume ProcEnd
> End Function
>
> You should be able to call this from a query this like so:
>
> CalculatedLength: CalculatePartMetric( [PartID], "Length_Formula" )
>
> --
> Good Luck :-)
>
> Graham Mandeno [Access MVP]
> Auckland, New Zealand
>
> "Mary Fran" <MaryFran[ at ]discussions.microsoft.com> wrote in message
> news:E40275C3-B6CB-444F-987E-C1A0394B5D04[ at ]microsoft.com...
> > No, Graham, the formulas vary from one component to the next and some
> > involve
> > conditional statements. For example, the length of the top board
> > component
> > is simply the length of part + 1.5". But the length of the sides is the
> > length of the part + 2*the length of the ends + 2*the length of the end
> > cleats. And that is for one style. Each style has variations of the
> > dimension calculation. That is why I had hoped to be able to use a
> > formula
> > as field data and then evaluate it for each part.
> > If you have a suggestion using VB, please offer - I was trying to use a
> > recordset and loop through each part table record, but my query to
> > determine
> > price filtered by the part record on the form (forms!frmMaster!MMKey).
> > What
> > were you thinking?
> >
> >
> > "Graham Mandeno" wrote:
> >
> >> Hi Mary
> >>
> >> What I was asking is, how complex and variable are these formulas?
> >>
> >> Would it not be possible to contrive a generic formula that works for all
> >> cases?
> >>
> >> If not, I have some other ideas involving VBA code, so post back...
> >>
> >> --
> >> Good Luck :-)
> >>
> >> Graham Mandeno [Access MVP]
> >> Auckland, New Zealand
> >>
> >> "Mary Fran" <MaryFran[ at ]discussions.microsoft.com> wrote in message
> >> news:6B0D4696-C675-4C5D-A779-5B8FC440BDB8[ at ]microsoft.com...
> >> > Thank you all for your suggestions - they have prompted me to provide
> >> > further
> >> > clarification. [Length]+1.5 is simply the length formula for one
> >> > style
> >> > in a
> >> > table of over 100 styles. I have fields for component id, quantity,
> >> > length,
> >> > width and height for each component record in tblStyleComponents. To
> >> > calculate the component dimensions I then have a query with an example
> >> > field
> >> > of: EVAL_LENGTH:
> >> > IIf([LENGTH_TYPE]="C",[LENGTH_CONSTANT],Eval([LENGTH_FORMULA])). The
> >> > scenario that works is when LENGTH_FORMULA =
> >> > dlookup("[LENGTH]","qryPartComponents")+1.5. This is when I have the
> >> > query
> >> > filtering for one part's components - it prices the parts correctly.
> >> > But
> >> > I
> >> > can't do this for the table of parts because I'd have to have a "WHERE"
> >> > clause in the dlookup and what would that be? Is the bottom line that
> >> > a
> >> > field value can't be evaluated?
> >> >
> >> >
> >> >
> >> > "Mike Painter" wrote:
> >> >
> >> >> In a query you could have a calculated field
> >> >> Length_formula:[Length]+1.5
> >> >> (Note the ":")
> >> >> You could also have it in a field on a form but it is better in a
> >> >> query
> >> >> You could run an update query which updated all Length_formula with
> >> >> "[Length]+1.5"
> >> >> But you can't have that in a table.
> >> >>
> >> >> It would proabably be better to have a field that contained this 1.5
> >> >> value
> >> >> unless you add 1.5 to all lengths.
> >> >> Even then I would not hard code a constant. but would look it up in a
> >> >> "Company Table" so when they decide it's 1.6 you won't have a lot of
> >> >> work
> >> >> to
> >> >> do.
> >> >>
> >> >>
> >> >> Mary Fran wrote:
> >> >> > Sorry so long in getting back to this issue but if I'm still trying
> >> >> > to
> >> >> > resolve it. Before I go any further, if a field in
> >> >> > tblStyleComponents named Length_formula has "[Length]+1.5", meaning
> >> >> > for that style the calculated component length is the part
> >> >> > length+1.5", as its contents, I thought I could use the eval
> >> >> > function in a query to determine the new length - no?
> >> >> >
> >> >> >
> >> >> > "Mike Painter" wrote:
> >> >> >
> >> >> >> If the part price is a calculation then you would have to update
> >> >> >> the
> >> >> >> items that are not calculations.
> >> >> >> In this case all you should have to do is update the material cost
> >> >> >>
> >> >> >> Mary Fran wrote:
> >> >> >>> If I have a price calculation based on a complicated query with
> >> >> >>> aggregate and subqueries which uses the part ID on the input form
> >> >> >>> as
> >> >> >>> the filter for the subqueries, the part price calculates fine, but
> >> >> >>> if the material costs change and I wish to do a "batch" update of
> >> >> >>> the part prices I need to loop through all the records in that
> >> >> >>> form.
> >> >> >>> Since the queries all have "forms!frmPart!PartID" as their
> >> >> >>> criteria,
> >> >> >>> how do I create this update? Thanks.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
|
|
|