Correct syntax error in DSUM using variables

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

XP - 28 Jan 2007 18:08 GMT

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.

Reply to this Message

Stefan Hoffmann - 28 Jan 2007 18:14 GMT

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

Reply to this Message

XP - 28 Jan 2007 18:38 GMT

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,
>
[quoted text clipped - 15 lines]
> mfG
> --> stefan <--

Reply to this Message

Stefan Hoffmann - 28 Jan 2007 18:44 GMT

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

Reply to this Message

XP - 28 Jan 2007 19:03 GMT

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,
>
[quoted text clipped - 19 lines]
> mfg
> --> stefan <--

Reply to this Message

XP - 28 Jan 2007 19:05 GMT

Hi Stefan,

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

> hi,
>
[quoted text clipped - 19 lines]
> mfg
> --> stefan <--

Reply to this Message