Building SQL string need guidance

[patriciashoe]

Good AM:

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



update teacherresource s set [student_enroll] = (([student_enroll] / variable1) * Variable 2 )) where(( trschool_id = 12 and [tryear] = forms!form1!yea rcombo) or (where trschool_id = 13 and [tryear = forms!form!year combo))

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

[ADezii]

Good AM:

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



update teacherresource s set [student_enroll] = (([student_enroll] / variable1) * Variable 2 )) where(( trschool_id = 12 and [tryear] = forms!form1!yea rcombo) or (where trschool_id = 13 and [tryear = forms!form!year combo))

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

Hello Patti, try:
[CODE=sql]
Dim strSQL As String

'For Numeric Form values
strSQL = "Update teacherresource s set [student_enroll] = (([student_enroll] / variable1) * Variable 2) " & _
"Where [trschool_id] = 12 And [tryear] = " & Forms!form1!yea rcombo & _
" Or ([trschool_id] = 13 And [tryear] = " & Forms!Form!year combo & ");"[/CODE]

[patriciashoe]

Thanks for the quick reply. The variables are not translating. I believe I read somewhere that you can use the cstr() function to turn them into strings which adds some complexity to the resulting string. I have been using a message box to check the string. Any suggestions on how to tweak your string? thanks again,

Patti

[NeoPa]

Patti, If you can explain exactly what you're trying to do here I'm sure I can help you.

The main trick is to understand the difference between what is the text in your VBA - and what this resolves to before being passed across to the SQL interpreter (or engine).

Some tutorials on (related) matters are :
Basic SQL Syntax for Access Queries
ANSI Standards in String Comparisons
Literal DateTimes and Their Delimiters (#)
Quotes (') and Double-Quotes (") - Where and When to use them

Let me know if you'd like to take this further.