Building SQL String - confusion

[PatriciaShoe]

Good AM:

I have struggling with constructing a string to execute some SQL. Here is my update statement



update teacherresources set [student_enroll] = (([student_enroll] / variable1) * Variable 2 )) where(( trschool_id = 12 and [tryear] = forms!form1!yearcombo) or (where trschool_id = 13 and [tryear = forms!form!yearcombo))

the variables are both numeric. I know the variables have to be converted to strings, i.e Crst().

If anyone knows of a good tutorial on this subject I would live to see it. I am lost in a sea of quotes and data mismatches. Thanks much,

Patti

 

[MikeC14081972]

Just looking quicly as I'm on my way out

(([student_enroll] / variable1) * Variable 2 ))

One to many close brackets

(( trschool_id = 12 and [tryear] = forms!form1!yearcombo) or (where trschool_id = 13 and [tryear = forms!form!yearcombo))

second [tryear] has no closing ]

 

[MikeC14081972]

You also don't need the second where statement and it should end with a ;

 

[MikeC14081972]

Try

UPDATE teacherresources SET teacherresources.student_enroll = ((teacherresources.studentenroll / variable1) * Variable2)
WHERE ((((teacherresources.trschool_id) = 12) AND ((teacherresources.tryear) = '" & forms!form1!yearcombo))
OR (((teacherresources.trschool_id) = 13) AND ((teacherresources.tryear) = '" & forms!form!yearcombo)));

 

[MikeC14081972]

If using the VBA IDE Try

codedb.Execute "UPDATE teacherresources SET " _
             & "teacherresources.student_enroll = ((teacherresources.studentenroll / variable1) * Variable2) " _
             & "WHERE ((((teacherresources.trschool_id) = 12) AND ((teacherresources.tryear) = '" & forms!form1!yearcombo)) " _
             & "OR (((teacherresources.trschool_id) = 13) AND ((teacherresources.tryear) = '" & forms!form!yearcombo)))",dbfailonerror

 

[Remou]

I suspect tryear is numeric, so I would try:

strSQL="UPDATE teacherresources " _
& "SET [student_enroll] = ([student_enroll] / variable1) * Variable 2 " _
& "WHERE (trschool_id = 12 OR trschool_id = 13) " _
& "AND [tryear] = " & forms!form1!yearcombo)

 

[MikeC14081972]

Good spot. I have had a couple this afternoon :lol:

 

[TheAceMan1]

How are ya PatriciaShoe . . .

As you learn to write SQL, learn how to format your SQL for easiest reading as well! Example:

[blue]   Dim SQL As String, valCBx As Long
   
   valCBx = Forms!Form1!YearCombo.Column([purple][b]?[/b][/purple])
   
   SQL = "UPDATE TeacherResources " & _
         "SET [student_enroll] = [student_enroll] * V2/ V1) " & _
         "WHERE (([trSchool_ID] = 12 AND [trYear] = " & valCBx & ") OR " & _
                "([trSchool_ID] = 13 AND [tryear] = " & valCBx & "));"[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[Remou]

It is generally best to build your own SQL once you get the hang of it, rather than relying on the query window. For example the query window frequently includes far too many parentheses or uses multiple AND / OR lines where fewer lines would be clearer.

 

[PatriciaShoe]

Thanks for all the quick responses. One problem, I need to pass than value of the variable into the string. In this case it is numeric. When I use the immediate window I would expect to see the numerics that will be part of the string. Otherwise, I get prompted for the value of the variables when I run the sql. Am I missing something?

Patti

 

[Remou]

Possibly.

What is the result of
?strSQL
In the immediate window?

 

[PatriciaShoe]

Using a simple exmaple, the result in the IW looks like this

UPDATE TeacherResources SET [student_enroll] = v2

The value of v2 = 25 so I would expect that the update statement should look like:

UPDATE TeacherResources SET [student_enroll] = 25

Otherwise I am prompted for the value of the variable. HOw about cstr(v2) ?

Thanks - Patti

 

[Remou]

Try:

strSQL="UPDATE teacherresources " _
& "SET [student_enroll] = ([student_enroll] / " _
& variable1 & ") * " & Variable2 _
& "WHERE (trschool_id = 12 OR trschool_id = 13) " _
& "AND [tryear] = " & forms!form1!yearcombo)

It should not be necessary to convert the variable to anything, because adding it is to be added to a string.

 

[TheAceMan1]

PatriciaShoe . . .

Again attempting a more readable SQL, we have:

[blue]   Dim SQL As String, valCBx As Long, Eq As String
   
   valCBx = Forms!Form1!YearCombo.Column(?)
   [purple][b]Eq[/b][/purple] = "[student_enroll]*" & V2 & "/" & V1 & ") "
   
   SQL = "UPDATE TeacherResources " & _
         "SET [student_enroll] = " & [purple][b]Eq[/b][/purple] & _
         "WHERE (([trSchool_ID] = 12 AND [trYear] = " & valCBx & ") OR " & _
                "([trSchool_ID] = 13 AND [tryear] = " & valCBx & "));"[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[PatriciaShoe]

Thank you all. I finally got it working and learned a great deal as well. Many thanks,Patti