OpenRecordset SQL using wrong SQL statement

[Cyberwolf0000 via AccessMonster.com]

I have an issue where I am using 3 different SQL statements strSQL, strSQL1
and strSQLUp in a Do Loop:
strSQL = "Select OpCode " & _
"FROM R2ROpCodes " & _
"WHERE R2RPerfID=" & Me.R2RPerfID & ";"
Set rec1 = CurrentDb().OpenRecordset(strSQL)
Debug.Print rec1.RecordCount
rec1.MoveFirst
Do Until rec1.EOF
'Gets several fields for later calculations at the Job Number Op
Code level
strSQL1 = "SELECT Sum(NET_GOOD_IMPS) as NGI, Sum(COUNTERDIFF) AS
CDIFF, Sum(TIMEDIFF) AS TDiff, " & _
"Sum(JOB_START) as JobStart , Sum(MR1_TIME) as MR1Time,
Sum(MR2_TIME) AS MR2Time, " & _
"Sum(MR1_IMPS) as MR1Imps, Sum(MR2_IMPS) AS MR2Imps, " &
_
"Sum(NET_GOOD_TIME) AS NGT, Sum(MR3_IMPS) AS MR3Imps,
Sum(MR3_TIME) AS MR3Time " & _
"FROM WEB_DEV_EVENT_SUMMARY_TRIM " & _
"WHERE PRODUCTION_ORDER_NUMBER='" & ZeroPad(strJobNum,
12) & "' AND OPERATION_CODE='" & _
rec1!OpCode & "';"

Set rec = CurrentDb().OpenRecordset(strSQL1)

If Err.Number = 3146 Then
Set rec = ODBCFail(strSQL1, Me.Name, Now())
ElseIf Err.Number = 3075 Then
Resume Next
ElseIf Err.Number > 0 Then
GoTo UpdateActuals_Error
End If
'If there is at least 1 record then update the 7 fields at the
OpCode level
If rec.RecordCount > 0 Then
'Loop through rec1 recordset and upate the 7 fields for each
OpCode
strSQLUp = "UPDATE R2ROpCodes " & _
"SET OpCodeImps=" & rec!NGI & _
" OMR=" & rec!MR1Time + rec!MR2Time
If rec!MR1Imps <> 0 Or rec!MR2Imps <> 0 Then
strSQLUp = strSQLUp & " MRImps=" & rec!MR1Imps + rec!MR2Imps
ElseIf Not IsNull(rec!MR3Imps) Then
strSQLUp = strSQLUp & " MRImps=" & rec!MR3Imps
End If
strSQLUp = strSQLUp & " NIMV=" & rec!NGI / (rec!TDiff - rec!
MR1Time - rec!MR2Time - rec!MR3Time) & _
" RDT=" & rec!NGT + (rec!TDiff - (rec!NGT + rec!
MR1Time + rec!MR2Time + rec!MR3Time)) & _
" VC=" & rec!MR3Time & _
" RWaste=" & (rec!CDIFF - (rec!MR1Imps + rec!
MR2Imps + rec!MR3Imps + rec!NGI)) / rec!CDIFF & ";"
End If
CurrentDb().Execute strSQLUp, dbFailOnError
Set rec = Nothing
strSQL1 = ""
strSQLUp = ""
rec1.MoveNext
Loop
End If
The StrSQL is used to get the record information to updating several other
records. This works fine

Within this I loop through each record updating its recordset using strSQL1
to pull the information from another table. I then use strSQLUp to update
the recordset. The first time it loops though everything works fine. any
subsequent loops when it gets to the OpenRecordset statement that use strSQL1
if fails giving an error 3075. The odd thing is that when I look at the
description of the error it is showing part of the SQL statement from the
first strSQLUp. An example would be like this

1st Loop:
strSQL1 ="SELECT Sum(NET_GOOD_IMPS) as NGI, Sum(COUNTERDIFF) AS CDIFF, Sum
(TIMEDIFF) AS TDiff, Sum(JOB_START) as JobStart , Sum(MR1_TIME) as MR1Time,
Sum(MR2_TIME) AS MR2Time, Sum(MR1_IMPS) as MR1Imps, Sum(MR2_IMPS) AS MR2Imps,
Sum(NET_GOOD_TIME) AS NGT, Sum(MR3_IMPS) AS MR3Imps, Sum(MR3_TIME) AS MR3Time
FROM WEB_DEV_EVENT_SUMMARY_TRIM WHERE PRODUCTION_ORDER_NUMBER='000000043349'
AND OPERATION_CODE='2100';"

strSQLUp = "UPDATE R2ROpCodes SET OpCodeImps=195856 OMR=1.37 MRImps=5465
NIMV=36863.542254846602672689629211 RDT=5.313 VC=1.577 RWaste=3.
09566765856745E-02;"

2nd loop

Debug.Print shows the strSQL1 statement as:

strSQL1 = "SELECT Sum(NET_GOOD_IMPS) as NGI, Sum(COUNTERDIFF) AS CDIFF, Sum
(TIMEDIFF) AS TDiff, Sum(JOB_START) as JobStart , Sum(MR1_TIME) as MR1Time,
Sum(MR2_TIME) AS MR2Time, Sum(MR1_IMPS) as MR1Imps, Sum(MR2_IMPS) AS MR2Imps,
Sum(NET_GOOD_TIME) AS NGT, Sum(MR3_IMPS) AS MR3Imps, Sum(MR3_TIME) AS MR3Time
FROM WEB_DEV_EVENT_SUMMARY_TRIM WHERE PRODUCTION_ORDER_NUMBER='000000043349'
AND OPERATION_CODE='2200';"

When it tries to open the recordset I get the Error 3075 state Missing
operator in SQL statement and shows:
"195856 OMR=1.37 MRImps=5465 NIMV=36863.542254846602672689629211 RDT=5.313
VC=1.577 RWaste=3.09566765856745E-02;" as the SQL code.

I am not sure why this is happening. As you can see just before I loop I am
clearing the 2 SQL statements, and setting rec = Nothing.

I know that you shouldn't store calculated values, but I couldn't figure out
how to show the proper values for each on a continuous subform with more that
1 record. If that can be done maybe I need to go that way.

TIA,

--
James B Gaylord
For the Wolf comes the strength of the Pack,
For the Pack comes the strength of the Wolf,
-R. Kipling
Office 2003 on Win XP SP2

 

[Douglas J. Steele]

I'm surprised that your first update statement works, since its syntax is
incorrect:

 

[Douglas J. Steele]

Oops: that got sent too soon!

Your Update statement

strSQLUp = "UPDATE R2ROpCodes SET OpCodeImps=195856 OMR=1.37 MRImps=5465
NIMV=36863.542254846602672689629211 RDT=5.313 VC=1.577 RWaste=3.
09566765856745E-02;"

is incorrect: you need commas between the various values:

strSQLUp = "UPDATE R2ROpCodes SET OpCodeImps=195856, OMR=1.37, MRImps=5465,
NIMV=36863.542254846602672689629211, RDT=5.313, VC=1.577, RWaste=3.
09566765856745E-02;"

Of course, that will update EVERY row in R2ROpCodes. Is that what you
actually want?

 

[Cyberwolf0000 via AccessMonster.com]

Yes it is. Since each OpCode can have different values I need to see the
values for each of them. Is there a way to show the correct calculated
values for each record on a continuous subform? I haven't been able to
figure that one out yet. I would prefer not to store the calculated values
for each record on the subform.

Thanks for the pointing out the missing commas I will make that correction
and try it out.

Oops: that got sent too soon!

Your Update statement

strSQLUp = "UPDATE R2ROpCodes SET OpCodeImps=195856 OMR=1.37 MRImps=5465
NIMV=36863.542254846602672689629211 RDT=5.313 VC=1.577 RWaste=3.
09566765856745E-02;"

is incorrect: you need commas between the various values:

strSQLUp = "UPDATE R2ROpCodes SET OpCodeImps=195856, OMR=1.37, MRImps=5465,
NIMV=36863.542254846602672689629211, RDT=5.313, VC=1.577, RWaste=3.
09566765856745E-02;"

Of course, that will update EVERY row in R2ROpCodes. Is that what you
actually want?

I have an issue where I am using 3 different SQL statements strSQL, strSQL1
and strSQLUp in a Do Loop:

[quoted text clipped - 123 lines]

TIA,

--
James B Gaylord
For the Wolf comes the strength of the Pack,
For the Pack comes the strength of the Wolf,
-R. Kipling
Office 2003 on Win XP SP2

 

[Cyberwolf0000 via AccessMonster.com]

Thanks Doug,

I added the commas and everything worked fine. Now my problem is getting the
calculated controls to work properly. By this I mean I want the correct
calculations to show next to each record in a continuous form. Is this even
possible? If not how would you handle it?

Oops: that got sent too soon!

Your Update statement

strSQLUp = "UPDATE R2ROpCodes SET OpCodeImps=195856 OMR=1.37 MRImps=5465
NIMV=36863.542254846602672689629211 RDT=5.313 VC=1.577 RWaste=3.
09566765856745E-02;"

is incorrect: you need commas between the various values:

strSQLUp = "UPDATE R2ROpCodes SET OpCodeImps=195856, OMR=1.37, MRImps=5465,
NIMV=36863.542254846602672689629211, RDT=5.313, VC=1.577, RWaste=3.
09566765856745E-02;"

Of course, that will update EVERY row in R2ROpCodes. Is that what you
actually want?

I have an issue where I am using 3 different SQL statements strSQL, strSQL1
and strSQLUp in a Do Loop:

[quoted text clipped - 123 lines]

TIA,

--
James B Gaylord
For the Wolf comes the strength of the Pack,
For the Pack comes the strength of the Wolf,
-R. Kipling
Office 2003 on Win XP SP2

 

[Douglas J. Steele]

If you're doing the calculations on the form, no, it's not possible. You'd
need to do the calculations in the query to which the form is bound, and
bind the text boxes to those calculated fields.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks Doug,

I added the commas and everything worked fine. Now my problem is getting
the
calculated controls to work properly. By this I mean I want the correct
calculations to show next to each record in a continuous form. Is this
even
possible? If not how would you handle it?

Oops: that got sent too soon!

Your Update statement

strSQLUp = "UPDATE R2ROpCodes SET OpCodeImps=195856 OMR=1.37 MRImps=5465
NIMV=36863.542254846602672689629211 RDT=5.313 VC=1.577 RWaste=3.
09566765856745E-02;"

is incorrect: you need commas between the various values:

strSQLUp = "UPDATE R2ROpCodes SET OpCodeImps=195856, OMR=1.37,
MRImps=5465,
NIMV=36863.542254846602672689629211, RDT=5.313, VC=1.577, RWaste=3.
09566765856745E-02;"

Of course, that will update EVERY row in R2ROpCodes. Is that what you
actually want?

I have an issue where I am using 3 different SQL statements strSQL,
strSQL1
and strSQLUp in a Do Loop:

[quoted text clipped - 123 lines]

TIA,

--
James B Gaylord
For the Wolf comes the strength of the Pack,
For the Pack comes the strength of the Wolf,
-R. Kipling
Office 2003 on Win XP SP2

 

[Cyberwolf0000 via AccessMonster.com]

I had bound the form directly to the table. I will bind it to a query. You
know the old saying, "Can't see the forest for the trees"?

If you're doing the calculations on the form, no, it's not possible. You'd
need to do the calculations in the query to which the form is bound, and
bind the text boxes to those calculated fields.

Thanks Doug,

[quoted text clipped - 29 lines]

--
James B Gaylord
For the Wolf comes the strength of the Pack,
For the Pack comes the strength of the Wolf,
-R. Kipling
Office 2003 on Win XP SP2

 

[Tony Toews [MVP]]

strSQLUp = "UPDATE R2ROpCodes SET OpCodeImps=195856 OMR=1.37 MRImps=5465
NIMV=36863.542254846602672689629211 RDT=5.313 VC=1.577 RWaste=3.
09566765856745E-02;"

The NIMV value has too many digits to work in Jet/Access MDB. Now if
you're dealing with SQL Server or other data server product then
possibly.

From A97 help -
Double stores numbers from
–1.79769313486231E308 to–4.94065645841247E–324 for negative values and
from 1.79769313486231E308 to 4.94065645841247E–324 for positive
values.

Also that many digits of precision is likely greater than the number
of atoms in the planet so I don't know how relevant that much
precision would be. Hmm, well, maybe not. One guesstimate states
that there are 1.3E50 or so atoms. +/- a few orders of magnitude.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/