Can I run UPDATE query with a calculated variable?

  • Thread starter Thread starter Richard Hollenbeck
  • Start date Start date
R

Richard Hollenbeck

My debugging window shows that the correct values for CourseAverage is being
collected at run time, but for some reason I get endless syntax errors. I
tried a number of combinations single and double quotations but I just can't
seem to figure it out.
Here's my stuff (abbreviated):

'. . .etc., etc., etc. . .
CourseAverage = CourseTotals / NumOfStudents
'. . .etc., etc., etc. . .

StrSQL = "UPDATE tblTempCourseGradeDistribution "
StrSQL = StrSQL & "SET [tblTempCourseGradeDistribution].[averageGrade] =
" & CourseAverage & ";"

'. . .etc., etc., etc. . .

I'm trying to update [tblTempCourseGradeDistribution].[averageGrade]
with the value contained in the variable CourseAverage. The temporary table
only has one record.

Could it be that UPDATE queries just don't like variables? Does the data
have to come from a table? Or is there a fundamental flaw in my query? The
variables all properly demensioned and properly collect the correct data.

Thanks. Richard Hollenbeck
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Queries do not read VBA variables. You can set up a VBA function that
reads the variable. The query can read VBA functions. Your query would
look like this:

UPDATE tblTempCourseGradeDistribution "
SET averageGrade = fnCourseAverage

The function could look like this:

Public Function fnCourseAverage() Double

fnCourseAverage = CourseTotals / NumOfStudents

End Function

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQRfmBIechKqOuFEgEQLJ7QCgk1riMkcFB9j6iUapQpnAE2BGjqQAn2pi
1A4Vn4Gl5uvDNhBTllJuoctq
=LbIE
-----END PGP SIGNATURE-----
 
Thanks. That was a huge peice of new information for me. I was wondering
because I couldn't get my query to run and I'd never used a variable before.
Here's what I did:

' This works great!
txtAvg.Value = fnCourseAverage(courseChoice)

'And this works too!
StrSQL = "UPDATE tblTempCourseGradeDistribution Set averageGrade = '" &
fnCourseAverage(courseChoice) & "';"
CurrentDb.Execute StrSQL, dbFailOnError

This code breezes right through with no problems. Thanks a million!

Here's my function

Public Function fnCourseAverage(Course As String) As Double
fnCourseAverage = (Nz(DLookup("sum(ActivityPoints)", _
"qryCourseGradeDistribution", "courseCode = '" & _
Course & "'")) / Nz(DLookup("count(studentID)", _
"studentsInCourses", "courseCode = '" & Course & _
"'"))) / 100
End Function


MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Queries do not read VBA variables. You can set up a VBA function that
reads the variable. The query can read VBA functions. Your query would
look like this:

UPDATE tblTempCourseGradeDistribution "
SET averageGrade = fnCourseAverage

The function could look like this:

Public Function fnCourseAverage() Double

fnCourseAverage = CourseTotals / NumOfStudents

End Function

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQRfmBIechKqOuFEgEQLJ7QCgk1riMkcFB9j6iUapQpnAE2BGjqQAn2pi
1A4Vn4Gl5uvDNhBTllJuoctq
=LbIE
-----END PGP SIGNATURE-----


Richard said:
My debugging window shows that the correct values for CourseAverage is being
collected at run time, but for some reason I get endless syntax errors. I
tried a number of combinations single and double quotations but I just can't
seem to figure it out.
Here's my stuff (abbreviated):

'. . .etc., etc., etc. . .
CourseAverage = CourseTotals / NumOfStudents
'. . .etc., etc., etc. . .

StrSQL = "UPDATE tblTempCourseGradeDistribution "
StrSQL = StrSQL & "SET [tblTempCourseGradeDistribution].[averageGrade] =
" & CourseAverage & ";"

'. . .etc., etc., etc. . .

I'm trying to update [tblTempCourseGradeDistribution].[averageGrade]
with the value contained in the variable CourseAverage. The temporary table
only has one record.

Could it be that UPDATE queries just don't like variables? Does the data
have to come from a table? Or is there a fundamental flaw in my query? The
variables all properly demensioned and properly collect the correct
Click to expand...
data.
Click to expand...
 
Back
Top