Correct syntax error in DSUM using variables

[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.

 

[Stefan Hoffmann]

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?

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 <--

 

[Guest]

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...

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?

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 <--

 

[Stefan Hoffmann]

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).

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...

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 <--

 

[Guest]

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?

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).

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...

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 <--

 

[Guest]

Hi Stefan,

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

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).

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...

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 <--