Data-type conversion with Recordset - HELP!!

[DBQueen]

I have a database where the user will have the opportunity to set up a
number of Tests. I want the user to be able to enter the equation if a
field for a particular test is to be a calculated value. This will be
entered into tblTests. There are 4 data fields in tblSamples (Data1,
Data2, Data3, Data4). When setting up a new test the user will fill in the
equation for any caluclated data fields into fields Calc1, Calc2, Calc3,
Calc4 in tblTests. For example, the user could enter Calc2 as Data1*.001.

The form is based on a query (qryBlue), which includes all fields from
tblTests and tblSamples.

The Calc fields are set up as Text fields; I want to force them to be
numeric when they are used. I am using

Private Sub Data1_AfterUpdate()

Dim rsA As DAO.Recordset
Dim D1Val As Single
Dim D2Val As Single
Dim d3Val As Single
Dim d4Val As Single

Set rsA = CurrentDb.OpenRecordset("SELECT * from qryBlue WHERE BatchID="
& Me.BatchID & " AND Testname='" & TestName & "'", dbOpenDynaset)

If Not IsNull(rsA!Calc2) Then
D2Val = CSng(rsA!Calc2)
End If

End Sub

Everytime, I get an Error 13: Type Mismatch error. The system refuses to
convert the rsa!Calc2 value to a number.

I have also tried the following to no avail:
1) d2Val as Variant; forcing it to a CVar instead of CSng
2) Entering Data2:([Calc2]) in the query
3) Defining the Calc values as Singles in tblTests

I am using the Microsoft 3.6 references.

Is there some rule that a value in a recordset can't be converted to another
type?

I'm about to go jump off a bridge! Any suggestions??

Thanks!

A Plotsky

[Bob Quintal]

"DBQueen" <ir******@bellsouth.net> wrote in
news:7y*******************@bignews3.bellsouth.net:

I have a database where the user will have the opportunity to
set up a number of Tests. I want the user to be able to enter
the equation if a field for a particular test is to be a
calculated value. This will be entered into tblTests. There
are 4 data fields in tblSamples (Data1, Data2, Data3, Data4).
When setting up a new test the user will fill in the equation
for any caluclated data fields into fields Calc1, Calc2,
Calc3, Calc4 in tblTests. For example, the user could enter
Calc2 as Data1*.001.

The form is based on a query (qryBlue), which includes all
fields from tblTests and tblSamples.

The Calc fields are set up as Text fields; I want to force
them to be numeric when they are used. I am using

Private Sub Data1_AfterUpdate()

Dim rsA As DAO.Recordset
Dim D1Val As Single
Dim D2Val As Single
Dim d3Val As Single
Dim d4Val As Single

Set rsA = CurrentDb.OpenRecordset("SELECT * from qryBlue
WHERE BatchID="
& Me.BatchID & " AND Testname='" & TestName & "'",
dbOpenDynaset)

If Not IsNull(rsA!Calc2) Then
D2Val = CSng(rsA!Calc2)
End If

End Sub

Everytime, I get an Error 13: Type Mismatch error. The system
refuses to convert the rsa!Calc2 value to a number.

I have also tried the following to no avail:
1) d2Val as Variant; forcing it to a CVar instead of CSng
2) Entering Data2:([Calc2]) in the query
3) Defining the Calc values as Singles in tblTests

I am using the Microsoft 3.6 references.

Is there some rule that a value in a recordset can't be
converted to another type?

I'm about to go jump off a bridge! Any suggestions??

Thanks!

A Plotsky

Take a parachute! LOL.

Actually, you need to evaluate the equation in calc2, you can't
just convert it to a number.
try D2Val = eval(rsA!Calc2)
--
Bob Quintal

PA is y I've altered my email address.

[Andi Plotsky]

Thank you, Bob. I no longer get a Type mismatch. Now it says it doesn't
recognize the term "Data1" in the Calc2 field.

I tried several things to get around it - loading it as Forms!frmBlue!Data1;
loading it as tblSamples.Data1 - but it still doesn't recognize it. I even
redefined the recordset to explicitly state the fields I'd be using
(BatchID, SampleID, Data1, Calc2, etc ), but it doesn't recognize Data1.

Basically, what I'm looking for is a way the user can enter an expression,
store that string for later use, and then call it up and use it to fill a
field in when the Data1 field has been updated. There must be a reasonably
simple way of doing this.....

Any other ideas?

THanks!

Andi

"Bob Quintal" <rq******@sympatico.ca> wrote in message
news:Xn**********************@207.35.177.135...

"DBQueen" <ir******@bellsouth.net> wrote in
news:7y*******************@bignews3.bellsouth.net:

I have a database where the user will have the opportunity to
set up a number of Tests. I want the user to be able to enter
the equation if a field for a particular test is to be a
calculated value. This will be entered into tblTests. There
are 4 data fields in tblSamples (Data1, Data2, Data3, Data4).
When setting up a new test the user will fill in the equation
for any caluclated data fields into fields Calc1, Calc2,
Calc3, Calc4 in tblTests. For example, the user could enter
Calc2 as Data1*.001.

The form is based on a query (qryBlue), which includes all
fields from tblTests and tblSamples.

The Calc fields are set up as Text fields; I want to force
them to be numeric when they are used. I am using

Private Sub Data1_AfterUpdate()

Dim rsA As DAO.Recordset
Dim D1Val As Single
Dim D2Val As Single
Dim d3Val As Single
Dim d4Val As Single

Set rsA = CurrentDb.OpenRecordset("SELECT * from qryBlue
WHERE BatchID="
& Me.BatchID & " AND Testname='" & TestName & "'",
dbOpenDynaset)

If Not IsNull(rsA!Calc2) Then
D2Val = CSng(rsA!Calc2)
End If

End Sub

Everytime, I get an Error 13: Type Mismatch error. The system
refuses to convert the rsa!Calc2 value to a number.

I have also tried the following to no avail:
1) d2Val as Variant; forcing it to a CVar instead of CSng
2) Entering Data2:([Calc2]) in the query
3) Defining the Calc values as Singles in tblTests

I am using the Microsoft 3.6 references.

Is there some rule that a value in a recordset can't be
converted to another type?

I'm about to go jump off a bridge! Any suggestions??

Thanks!

A Plotsky

Take a parachute! LOL.

Actually, you need to evaluate the equation in calc2, you can't
just convert it to a number.
try D2Val = eval(rsA!Calc2)
--
Bob Quintal

PA is y I've altered my email address.

[DBQueen]

OK. If I leave out the reference to Data1 in the Calc2 field I'm alright
(instead of saying "Data1 * .001" I just entered "* .001" into the Calc2
field. This works:

Set rsA = CurrentDb.OpenRecordset("SELECT BatchID,SampleID, Data1, Testname,
Calc2 from qryBlue WHERE BatchID=" & Me.BatchID & " AND Testname='" &
TestName & "'" & " AND SampleID=" & Me.SampleID, dbOpenDynaset)

If Not IsNull(rsA!Calc2) Then
D2Val = (Forms!frmGreen!frmSub!Data1)
Data2 = Eval(D2Val & rsA!Calc2)
End If
rsA.Close: Set rsA = Nothing

I know with the Calc2 field that it's always going to be an operation using
Data1, so I can put the rest of it in. However, my Calc4 equation could be
using data from any of the fields, such as "Data2 * Data3".

Hmmm, maybe instead of using this approach I should just create a querydef
for each of the tests with calculations that uses the syntax I want for the
calculations as parameter queries. Then I could just call the query for
that test when the Data is entered in the form for the particular
test.........would that work?


"Andi Plotsky" <ir******@bellsouth.net> wrote in message
news:ZN*******************@bignews2.bellsouth.net. ..

Thank you, Bob. I no longer get a Type mismatch. Now it says it doesn't
recognize the term "Data1" in the Calc2 field.

I tried several things to get around it - loading it as Forms!frmBlue!Data1; loading it as tblSamples.Data1 - but it still doesn't recognize it. I even redefined the recordset to explicitly state the fields I'd be using
(BatchID, SampleID, Data1, Calc2, etc ), but it doesn't recognize Data1.

Basically, what I'm looking for is a way the user can enter an expression,
store that string for later use, and then call it up and use it to fill a
field in when the Data1 field has been updated. There must be a reasonably simple way of doing this.....

Any other ideas?

THanks!

Andi

"Bob Quintal" <rq******@sympatico.ca> wrote in message
news:Xn**********************@207.35.177.135...

"DBQueen" <ir******@bellsouth.net> wrote in
news:7y*******************@bignews3.bellsouth.net:

I have a database where the user will have the opportunity to
set up a number of Tests. I want the user to be able to enter
the equation if a field for a particular test is to be a
calculated value. This will be entered into tblTests. There
are 4 data fields in tblSamples (Data1, Data2, Data3, Data4).
When setting up a new test the user will fill in the equation
for any caluclated data fields into fields Calc1, Calc2,
Calc3, Calc4 in tblTests. For example, the user could enter
Calc2 as Data1*.001.

The form is based on a query (qryBlue), which includes all
fields from tblTests and tblSamples.

The Calc fields are set up as Text fields; I want to force
them to be numeric when they are used. I am using

Private Sub Data1_AfterUpdate()

Dim rsA As DAO.Recordset
Dim D1Val As Single
Dim D2Val As Single
Dim d3Val As Single
Dim d4Val As Single

Set rsA = CurrentDb.OpenRecordset("SELECT * from qryBlue
WHERE BatchID="
& Me.BatchID & " AND Testname='" & TestName & "'",
dbOpenDynaset)

If Not IsNull(rsA!Calc2) Then
D2Val = CSng(rsA!Calc2)
End If

End Sub

Everytime, I get an Error 13: Type Mismatch error. The system
refuses to convert the rsa!Calc2 value to a number.

I have also tried the following to no avail:
1) d2Val as Variant; forcing it to a CVar instead of CSng
2) Entering Data2:([Calc2]) in the query
3) Defining the Calc values as Singles in tblTests

I am using the Microsoft 3.6 references.

Is there some rule that a value in a recordset can't be
converted to another type?

I'm about to go jump off a bridge! Any suggestions??

Thanks!

A Plotsky

Take a parachute! LOL.

Actually, you need to evaluate the equation in calc2, you can't
just convert it to a number.
try D2Val = eval(rsA!Calc2)
--
Bob Quintal

PA is y I've altered my email address.

[Andi Plotsky]

I got it to work! I put the Calc2 field back to being a text field and put
the entire path to the subform field into it: Forms!frmGreen!frmSub!Data1 &
..001 (instead of just Data1) .

Then I used :
If Not IsNull(rsA!Calc2) Then
Data2 = Eval(rsA!Calc2)
End If
in my Data1_AfterUpdate procedure and it worked.....

Thanks again, Bob!
Andi

"DBQueen" <ir******@bellsouth.net> wrote in message
news:Zo****************@bignews7.bellsouth.net...

OK. If I leave out the reference to Data1 in the Calc2 field I'm alright
(instead of saying "Data1 * .001" I just entered "* .001" into the Calc2
field. This works:

Set rsA = CurrentDb.OpenRecordset("SELECT BatchID,SampleID, Data1, Testname, Calc2 from qryBlue WHERE BatchID=" & Me.BatchID & " AND Testname='" &
TestName & "'" & " AND SampleID=" & Me.SampleID, dbOpenDynaset)

If Not IsNull(rsA!Calc2) Then
D2Val = (Forms!frmGreen!frmSub!Data1)
Data2 = Eval(D2Val & rsA!Calc2)
End If
rsA.Close: Set rsA = Nothing

I know with the Calc2 field that it's always going to be an operation using Data1, so I can put the rest of it in. However, my Calc4 equation could be using data from any of the fields, such as "Data2 * Data3".

Hmmm, maybe instead of using this approach I should just create a querydef
for each of the tests with calculations that uses the syntax I want for the calculations as parameter queries. Then I could just call the query for
that test when the Data is entered in the form for the particular
test.........would that work?


"Andi Plotsky" <ir******@bellsouth.net> wrote in message
news:ZN*******************@bignews2.bellsouth.net. ..

Thank you, Bob. I no longer get a Type mismatch. Now it says it doesn't recognize the term "Data1" in the Calc2 field.

I tried several things to get around it - loading it as

Forms!frmBlue!Data1;

loading it as tblSamples.Data1 - but it still doesn't recognize it. I

even

redefined the recordset to explicitly state the fields I'd be using
(BatchID, SampleID, Data1, Calc2, etc ), but it doesn't recognize Data1.

Basically, what I'm looking for is a way the user can enter an expression, store that string for later use, and then call it up and use it to fill a field in when the Data1 field has been updated. There must be a

reasonably

simple way of doing this.....

Any other ideas?

THanks!

Andi

"Bob Quintal" <rq******@sympatico.ca> wrote in message
news:Xn**********************@207.35.177.135...

"DBQueen" <ir******@bellsouth.net> wrote in
news:7y*******************@bignews3.bellsouth.net:

> I have a database where the user will have the opportunity to
> set up a number of Tests. I want the user to be able to enter
> the equation if a field for a particular test is to be a
> calculated value. This will be entered into tblTests. There
> are 4 data fields in tblSamples (Data1, Data2, Data3, Data4).
> When setting up a new test the user will fill in the equation
> for any caluclated data fields into fields Calc1, Calc2,
> Calc3, Calc4 in tblTests. For example, the user could enter
> Calc2 as Data1*.001.
>
> The form is based on a query (qryBlue), which includes all
> fields from tblTests and tblSamples.
>
> The Calc fields are set up as Text fields; I want to force
> them to be numeric when they are used. I am using
>
> Private Sub Data1_AfterUpdate()
>
> Dim rsA As DAO.Recordset
> Dim D1Val As Single
> Dim D2Val As Single
> Dim d3Val As Single
> Dim d4Val As Single
>
> Set rsA = CurrentDb.OpenRecordset("SELECT * from qryBlue
> WHERE BatchID="
> & Me.BatchID & " AND Testname='" & TestName & "'",
> dbOpenDynaset)
>
> If Not IsNull(rsA!Calc2) Then
> D2Val = CSng(rsA!Calc2)
> End If
>
> End Sub
>
> Everytime, I get an Error 13: Type Mismatch error. The system
> refuses to convert the rsa!Calc2 value to a number.
>
> I have also tried the following to no avail:
> 1) d2Val as Variant; forcing it to a CVar instead of CSng
> 2) Entering Data2:([Calc2]) in the query
> 3) Defining the Calc values as Singles in tblTests
>
> I am using the Microsoft 3.6 references.
>
> Is there some rule that a value in a recordset can't be
> converted to another type?
>
> I'm about to go jump off a bridge! Any suggestions??
>
> Thanks!
>
> A Plotsky
>
Take a parachute! LOL.

Actually, you need to evaluate the equation in calc2, you can't
just convert it to a number.
try D2Val = eval(rsA!Calc2)
--
Bob Quintal

PA is y I've altered my email address.

[rquintal]

The other way to handle your situation is to put the equation into a
string variable, then use the replace() function to put the actual
values from the table into the string.

'pseudocode only
x= 2; y=3;z=4
stEquation = "(x+y)/z"
stEquation = replace(stequation,"x",x) ' now "(2+y)/z"
stEquation = replace(stequation,"y",y) ' now "(2+3)/z"
stEquation = replace(stequation,"z",z) ' now "(2+3)/4"
result = eval(stEquation)