Correct syntax error in DSUM using variables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In Office 2003 and Windows XP,

I am trying to set a text box on a main form to the total of column
HEAD_COUNT in table MyGlobalTableVariable WHERE DEPT1 = MyLocalDeptVariable.

What I have so far is shown below:

Me.tbxHeadCountUSER.Value = DSum("[HEAD_COUNT]", MyGlobalTableVariable,
"[DEPT1] = '" & MyLocalDeptVariable & "'" & """)

The error seems to reside in the Criteria portion and I know it's syntax,
but I can't seem to get it to work. Could someone please help me out with
this?

Thanks.
 
hi,
Me.tbxHeadCountUSER.Value = DSum("[HEAD_COUNT]", MyGlobalTableVariable,
"[DEPT1] = '" & MyLocalDeptVariable & "'" & """)
The error seems to reside in the Criteria portion and I know it's syntax,
but I can't seem to get it to work. Could someone please help me out with
this?
Click to expand...
There are too many " and ' in your condition. It must be

"[DEPT1] = '" & MyLocalDeptVariable & "'"


Be aware of ' in MyLocalDeptVariable. Better use

MyLocalDeptVariable = Replace(MyLocalDeptVariable, "'", "''")

to escape them before using it in your condition.


mfG
--> stefan <--
 
Hi Stefan,

I'm not sure I understand, the double and single quotes are not intrinsic in
the variable. The department variable might equal: Parks and Recreation (no
quotes at all).

I tried removing some of the quotes to:

DSum("[HEAD_COUNT]", MyGlobalTableVariable, "[DEPT1] = '" &
MyLocalDeptVariable & "'")

But, this still fails...


Stefan Hoffmann said:
hi,
Me.tbxHeadCountUSER.Value = DSum("[HEAD_COUNT]", MyGlobalTableVariable,
"[DEPT1] = '" & MyLocalDeptVariable & "'" & """)
The error seems to reside in the Criteria portion and I know it's syntax,
but I can't seem to get it to work. Could someone please help me out with
this?
Click to expand...
There are too many " and ' in your condition. It must be

"[DEPT1] = '" & MyLocalDeptVariable & "'"


Be aware of ' in MyLocalDeptVariable. Better use

MyLocalDeptVariable = Replace(MyLocalDeptVariable, "'", "''")

to escape them before using it in your condition.


mfG
--> stefan <--
Click to expand...
 
hi,
I'm not sure I understand, the double and single quotes are not intrinsic in
the variable. The department variable might equal: Parks and Recreation (no
quotes at all).
Click to expand...
You should use it to avoid SQL injection, when creating SQL strings and
conditions. You never know when there is an single quote in your variable.
I tried removing some of the quotes to:

DSum("[HEAD_COUNT]", MyGlobalTableVariable, "[DEPT1] = '" &
MyLocalDeptVariable & "'")

But, this still fails...
Click to expand...
What error message do you get?

Are you sure that MyGlobalTableVariable and MyLocalDeptVariable contain
the right values?

How do you invoke your function?


mfg
--> stefan <--
 
I get "You cancelled the previous operation." error; yet when I remove my
DSUM it runs fine.

The variables are good, I checked them using DEBUG and rested my cursor on
them and they show the correct values; also I checked using MsgBox.

It is invoked using an After_Update event on a combo box.

Any ideas?

Stefan Hoffmann said:
hi,
I'm not sure I understand, the double and single quotes are not intrinsic in
the variable. The department variable might equal: Parks and Recreation (no
quotes at all).
Click to expand...
You should use it to avoid SQL injection, when creating SQL strings and
conditions. You never know when there is an single quote in your variable.
I tried removing some of the quotes to:

DSum("[HEAD_COUNT]", MyGlobalTableVariable, "[DEPT1] = '" &
MyLocalDeptVariable & "'")

But, this still fails...
Click to expand...
What error message do you get?

Are you sure that MyGlobalTableVariable and MyLocalDeptVariable contain
the right values?

How do you invoke your function?


mfg
--> stefan <--
Click to expand...
 
Hi Stefan,

SOOORRRY; the column name in the table was changed and that did it!

Stefan Hoffmann said:
hi,
I'm not sure I understand, the double and single quotes are not intrinsic in
the variable. The department variable might equal: Parks and Recreation (no
quotes at all).
Click to expand...
You should use it to avoid SQL injection, when creating SQL strings and
conditions. You never know when there is an single quote in your variable.
I tried removing some of the quotes to:

DSum("[HEAD_COUNT]", MyGlobalTableVariable, "[DEPT1] = '" &
MyLocalDeptVariable & "'")

But, this still fails...
Click to expand...
What error message do you get?

Are you sure that MyGlobalTableVariable and MyLocalDeptVariable contain
the right values?

How do you invoke your function?


mfg
--> stefan <--
Click to expand...
 
Back
Top