Using EVAL() in a Query with a nested Field Reference

[Bri]

Greetings,

I am using Eval() in a query with only limited success. If the text
within the function contains a reference to a Field I get #ERROR#. I'll
try and explain what I'm trying to do and maybe one of you can either
tell me how to get the Eval() to work or suggest and alternate method.

I have a table called Units:
Fields: Unit, Formula
Sample Data:
"ea", "* 1"
"km", "* [Interval] / 1000"

I have a Query that returns (amongst other fields):
Fields: Interval, Unit, Quantity, Rate, Charge
Charge: Eval([Quantity] & " * " & [Rate] & [Formula])

The "ea" unit works like a charm.
The "km" unit returns #ERROR#

Now I can see that since the [Interval] is in the value of the Formula
Field that I need to do something to get it included in the Eval, but I
can't figure out what.

Any ideas?

Thanks.

--
Bri

[Br]

Bri <no*@here.com> wrote:

Greetings,

I am using Eval() in a query with only limited success. If the text
within the function contains a reference to a Field I get #ERROR#.
I'll try and explain what I'm trying to do and maybe one of you can
either tell me how to get the Eval() to work or suggest and alternate
method.
I have a table called Units:
Fields: Unit, Formula
Sample Data:
"ea", "* 1"
"km", "* [Interval] / 1000"

I have a Query that returns (amongst other fields):
Fields: Interval, Unit, Quantity, Rate, Charge
Charge: Eval([Quantity] & " * " & [Rate] & [Formula])

The "ea" unit works like a charm.
The "km" unit returns #ERROR#

Now I can see that since the [Interval] is in the value of the Formula
Field that I need to do something to get it included in the Eval, but
I can't figure out what.

Any ideas?

Thanks.

I think you'd need to replace the string "[Interval]" with the actual
value?

Is there only two formulae and are the ever likely to change? I'd be
tempted to hardcode them otherwise.

eg. Charge : Iif([Unit] = "ea", [Quantity], [Quantity]/[Interval]*1000)

--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response

[Bri]

Br@dley wrote:

Bri <no*@here.com> wrote:

Greetings,

I am using Eval() in a query with only limited success. If the text
within the function contains a reference to a Field I get #ERROR#.
I'll try and explain what I'm trying to do and maybe one of you can
either tell me how to get the Eval() to work or suggest and alternate
method.
I have a table called Units:
Fields: Unit, Formula
Sample Data:
"ea", "* 1"
"km", "* [Interval] / 1000"

I have a Query that returns (amongst other fields):
Fields: Interval, Unit, Quantity, Rate, Charge
Charge: Eval([Quantity] & " * " & [Rate] & [Formula])

The "ea" unit works like a charm.
The "km" unit returns #ERROR#

Now I can see that since the [Interval] is in the value of the Formula
Field that I need to do something to get it included in the Eval, but
I can't figure out what.

Any ideas?

Thanks.

I think you'd need to replace the string "[Interval]" with the actual
value?

Is there only two formulae and are the ever likely to change? I'd be
tempted to hardcode them otherwise.

eg. Charge : Iif([Unit] = "ea", [Quantity], [Quantity]/[Interval]*1000)

The value for Interval is variable and changes from record to record,
otherwise I would have used the actual value. And these two fomulae are
just one example of a woring one and a non-working one. There are
several others too. Also I wanted the User to be able to add more Units
in the future without having to have me modify the app.

It seems like such a simple thing to do, but the solution eludes me.

--
Bri

[Ronald Roberts]

Why don't you create a function and use a select case
in the function to calculate your answer.
In your query:
Charge: CalcAnswer([Unit], [Interval], [Quanty], [Rate])

Function CalcAnswer(UM as string, Intvl as integer, Qty as integer _
, Rate as single) as single
CalcAnswer=0
Select Case UM
Case "ea"
CalcAnswer = Qty * Rate * Intvl * 1
Case "km"
CalcAnswer = Qty * Rate * Intvl /1000
Case whatever...and so on
Case Else
" return an answer if there is no match on unit of measure.
CalcAnswer= -1 'or what ever
End Select
EndFunction

This is air code. You will need to set the datatypes correctly.
Also you need to add logic to test for zero or bad data so you
don't cause an invalid answer or perform an invalid calculation.

Ron
Bri wrote:

Br@dley wrote:

Bri <no*@here.com> wrote:

Greetings,

I am using Eval() in a query with only limited success. If the text
within the function contains a reference to a Field I get #ERROR#.
I'll try and explain what I'm trying to do and maybe one of you can
either tell me how to get the Eval() to work or suggest and alternate
method.
I have a table called Units:
Fields: Unit, Formula
Sample Data:
"ea", "* 1"
"km", "* [Interval] / 1000"

I have a Query that returns (amongst other fields):
Fields: Interval, Unit, Quantity, Rate, Charge
Charge: Eval([Quantity] & " * " & [Rate] & [Formula])

The "ea" unit works like a charm.
The "km" unit returns #ERROR#

Now I can see that since the [Interval] is in the value of the Formula
Field that I need to do something to get it included in the Eval, but
I can't figure out what.

Any ideas?

Thanks.

I think you'd need to replace the string "[Interval]" with the actual
value?

Is there only two formulae and are the ever likely to change? I'd be
tempted to hardcode them otherwise.

eg. Charge : Iif([Unit] = "ea", [Quantity], [Quantity]/[Interval]*1000)

The value for Interval is variable and changes from record to record,
otherwise I would have used the actual value. And these two fomulae are
just one example of a woring one and a non-working one. There are
several others too. Also I wanted the User to be able to add more Units
in the future without having to have me modify the app.

It seems like such a simple thing to do, but the solution eludes me.

--
Bri

[Br]

Ronald Roberts <rw*@robcom.com> wrote:

Why don't you create a function......

Yep, that's probably what I'd have done actually...

<>
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response

[Bri]

Ronald Roberts wrote:

Why don't you create a function and use a select case
in the function to calculate your answer.
In your query:
Charge: CalcAnswer([Unit], [Interval], [Quanty], [Rate])

Function CalcAnswer(UM as string, Intvl as integer, Qty as integer _
, Rate as single) as single
CalcAnswer=0
Select Case UM
Case "ea"
CalcAnswer = Qty * Rate * Intvl * 1
Case "km"
CalcAnswer = Qty * Rate * Intvl /1000
Case whatever...and so on
Case Else
" return an answer if there is no match on unit of measure.
CalcAnswer= -1 'or what ever
End Select
EndFunction

This is air code. You will need to set the datatypes correctly.
Also you need to add logic to test for zero or bad data so you
don't cause an invalid answer or perform an invalid calculation.

Actually, a function is what I started on after not getting any
solutions to the original idea. Its not quite as simple as your example
though because the Formula is in a Field, so I will need to parse it to
do the actual calculation. The idea is that the calculations will be
data driven so the users can add new ones in the future without my
intervention. If it had been a fixed set of known formulae I would have
just done a Switch() in the query. Oh and there are more fields that
could be involved in the formulae than I've shown here. I tried to
reduce the problem down to just a basic example so that the problem
would be easier for me to explain.

The Eval() solution seemed like a nice simple solution, its too bad it
didn't quite do the trick.

--
Bri

[Bri]

Ronald Roberts wrote:

Why don't you create a function and use a select case
in the function to calculate your answer.
In your query:
Charge: CalcAnswer([Unit], [Interval], [Quanty], [Rate])

Function CalcAnswer(UM as string, Intvl as integer, Qty as integer _
, Rate as single) as single
CalcAnswer=0
Select Case UM
Case "ea"
CalcAnswer = Qty * Rate * Intvl * 1
Case "km"
CalcAnswer = Qty * Rate * Intvl /1000
Case whatever...and so on
Case Else
" return an answer if there is no match on unit of measure.
CalcAnswer= -1 'or what ever
End Select
EndFunction

This is air code. You will need to set the datatypes correctly.
Also you need to add logic to test for zero or bad data so you
don't cause an invalid answer or perform an invalid calculation.

Actually, a function is what I started on after not getting any
solutions to the original idea. Its not quite as simple as your example
though because the Formula is in a Field, so I will need to parse it to
do the actual calculation. The idea is that the calculations will be
data driven so the users can add new ones in the future without my
intervention. If it had been a fixed set of known formulae I would have
just done a Switch() in the query. Oh and there are more fields that
could be involved in the formulae than I've shown here. I tried to
reduce the problem down to just a basic example so that the problem
would be easier for me to explain.

The Eval() solution seemed like a nice simple solution, its too bad it
didn't quite do the trick.

--
Bri

[Ronald Roberts]

I understand what you are saying. I have an application were I do
something similar. Over a recordset I needed to calculate answers
based upon selected criteria. Something like, How many males are
within select age groups and what is their race that are classified as
enrolled. I created a table with the field name I wanted to count or
sum, the table name, the Where part of the SQL statement, an operation
field telling me if it were a count or sum operation and an English
description telling the user what this answer represents.

I process this table putting the answer back into the record and also
calculate a percent of the total student count I do this by testing
the operation code and then parsing the Where clause into the Dsum or
Dcount function
Table: Student
Field: StateCode
OP Code: 1=Count, 2 = Sum
English Desc: Native Hawaiian or Other Pacific Islander Male between
16 - 18 years of age Enrolled
SQL Where: (SU_Age Between 16 AND 18) AND UCase(TRIM(SU_gender))= _
'MALE' AND SU_race=6 AND SU_enrolled=True

Then in the logic I do this to get the count.
If IsNull(DCount(cCountField, tFile, Criteria1)) Then
nNum = 0
Else
nNum = DCount(cCountField, tFile, Criteria1)
End If

Then this to get the percent which was calculated before I processed
each record in the table.
If nStudentTotal = 0 Then
nPercent = 0
Else
nPercent = Format(((nNum / nStudentTotal) * 100), "000.0")
End If
I actually have 2 fields of 255 characters for the Where Clause
which I parse together if the second Field is not blank, empty or
null to complete the Where Clause. I currently have some 800
plus records in this table.

The user can then lookup the answer in a subform in datasheet view
or run a report showing all of the records.

I did this in Visual FoxPro 3 which has the ability to do what
you want to do with the Eval command. Later I converted it to Access.

Ron
Bri wrote:

Ronald Roberts wrote:

Why don't you create a function and use a select case
in the function to calculate your answer.
In your query:
Charge: CalcAnswer([Unit], [Interval], [Quanty], [Rate])

Function CalcAnswer(UM as string, Intvl as integer, Qty as integer _
, Rate as single) as single
CalcAnswer=0
Select Case UM
Case "ea"
CalcAnswer = Qty * Rate * Intvl * 1
Case "km"
CalcAnswer = Qty * Rate * Intvl /1000
Case whatever...and so on
Case Else
" return an answer if there is no match on unit of measure.
CalcAnswer= -1 'or what ever
End Select
EndFunction

This is air code. You will need to set the datatypes correctly.
Also you need to add logic to test for zero or bad data so you
don't cause an invalid answer or perform an invalid calculation.

Actually, a function is what I started on after not getting any
solutions to the original idea. Its not quite as simple as your example
though because the Formula is in a Field, so I will need to parse it to
do the actual calculation. The idea is that the calculations will be
data driven so the users can add new ones in the future without my
intervention. If it had been a fixed set of known formulae I would have
just done a Switch() in the query. Oh and there are more fields that
could be involved in the formulae than I've shown here. I tried to
reduce the problem down to just a basic example so that the problem
would be easier for me to explain.

The Eval() solution seemed like a nice simple solution, its too bad it
didn't quite do the trick.

--
Bri

[Ronald Roberts]

I understand what you are saying. I have an application were I do
something similar. Over a recordset I needed to calculate answers
based upon selected criteria. Something like, How many males are
within select age groups and what is their race that are classified as
enrolled. I created a table with the field name I wanted to count or
sum, the table name, the Where part of the SQL statement, an operation
field telling me if it were a count or sum operation and an English
description telling the user what this answer represents.

I process this table putting the answer back into the record and also
calculate a percent of the total student count I do this by testing
the operation code and then parsing the Where clause into the Dsum or
Dcount function
Table: Student
Field: StateCode
OP Code: 1=Count, 2 = Sum
English Desc: Native Hawaiian or Other Pacific Islander Male between
16 - 18 years of age Enrolled
SQL Where: (SU_Age Between 16 AND 18) AND UCase(TRIM(SU_gender))= _
'MALE' AND SU_race=6 AND SU_enrolled=True

Then in the logic I do this to get the count.
If IsNull(DCount(cCountField, tFile, Criteria1)) Then
nNum = 0
Else
nNum = DCount(cCountField, tFile, Criteria1)
End If

Then this to get the percent which was calculated before I processed
each record in the table.
If nStudentTotal = 0 Then
nPercent = 0
Else
nPercent = Format(((nNum / nStudentTotal) * 100), "000.0")
End If
I actually have 2 fields of 255 characters for the Where Clause
which I parse together if the second Field is not blank, empty or
null to complete the Where Clause. I currently have some 800
plus records in this table.

The user can then lookup the answer in a subform in datasheet view
or run a report showing all of the records.

I did this in Visual FoxPro 3 which has the ability to do what
you want to do with the Eval command. Later I converted it to Access.

Ron
Bri wrote:

Ronald Roberts wrote:

Why don't you create a function and use a select case
in the function to calculate your answer.
In your query:
Charge: CalcAnswer([Unit], [Interval], [Quanty], [Rate])

Function CalcAnswer(UM as string, Intvl as integer, Qty as integer _
, Rate as single) as single
CalcAnswer=0
Select Case UM
Case "ea"
CalcAnswer = Qty * Rate * Intvl * 1
Case "km"
CalcAnswer = Qty * Rate * Intvl /1000
Case whatever...and so on
Case Else
" return an answer if there is no match on unit of measure.
CalcAnswer= -1 'or what ever
End Select
EndFunction

This is air code. You will need to set the datatypes correctly.
Also you need to add logic to test for zero or bad data so you
don't cause an invalid answer or perform an invalid calculation.

Actually, a function is what I started on after not getting any
solutions to the original idea. Its not quite as simple as your example
though because the Formula is in a Field, so I will need to parse it to
do the actual calculation. The idea is that the calculations will be
data driven so the users can add new ones in the future without my
intervention. If it had been a fixed set of known formulae I would have
just done a Switch() in the query. Oh and there are more fields that
could be involved in the formulae than I've shown here. I tried to
reduce the problem down to just a basic example so that the problem
would be easier for me to explain.

The Eval() solution seemed like a nice simple solution, its too bad it
didn't quite do the trick.

--
Bri