Can anyone tell me what's wrong with this syntax?

=DSum("[taxable_premium]", "OtherCountryTax", "[unique_record_number]
= Forms![frmPolicy]![txtURN] AND [year_of_account] = Forms![frmPolicy]!
[Forms![frmPolicy]![txtyear_of_account])

On Nov 11, 3:07*pm, FireyColin <colin.mard... (AT) btopenworld (DOT) com> wrote:
Hi

You are having trouble with your strings. This kind of thing is a bit
of a nightmare. It's very logical but hard to keep track of!

if you put something like Forms![frmPolicy]![txtURN] *inside* your
string it will not be evaluated: it's just a bit of text, so you have
to end the string and concatenate using the & ... like this:

=DSum("[taxable_premium]", "OtherCountryTax", "[unique_record_number]
note that & Forms![frmPolicy]![txtURN] & is NOT inside a pair of
quotes, it's not inside a string and so it will be evaluated. The
final ")" is there to put in the closing bracket in the DSum formula.

This assumes that [txtUrn] is a numeric value. if it is a string
value it gets moore complicated as you have to place speech marks
around it! I like to use chr(34) - which is the " character - because
it's easier to keep track of the dam' things

=DSum("[taxable_premium]", "OtherCountryTax", "[unique_record_number]
Next you need the AND stuff - inside quotes again cos it's a
string ...

=DSum("[taxable_premium]", "OtherCountryTax", "[unique_record_number]

if the form controls (fields) are string (text) it looks like this ...

=DSum("[taxable_premium]", "OtherCountryTax", "[unique_record_number]
[txtyear_of_account] & chr(34) & ")"

Whew!

One way of testing what you've created in all the string stuff is like
this ... place a new command button on the Form and put the code
below in its On Click event

Dim strSQL as string
strSQL = "[unique_record_number] > = " & chr(34) & Forms![frmPolicy]!
[txtURN] & chr(34) & " AND [year_of_account] = " & chr(34) & Forms!
[frmPolicy]![txtyear_of_account] & chr(34)

You should see something like:

[unique_record_number] >= 1234 AND [year_of_account] = 2008

or

[unique_record_number] >= "1234" AND [year_of_account] = "2008"

depending on whether you use the chr(34)'s or not.


WARNING ... untested 'air code'!

HTH

JB







=DSum("[taxable_premium]", "OtherCountryTax", "[unique_record_number]
= " & Forms![frmPolicy]![txtURN] & " AND [year_of_account] = " & Forms!
[frmPolicy]!
[Forms![frmPolicy]![txtyear_of_account] & ")"

On Nov 11, 4:43*pm, jbguernsey <j... (AT) angelsystems (DOT) co.uk> wrote:
Oops!

Just looked at my code ... left out a line (of course!)

in this bit

Dim strSQL as string
strSQL = "[unique_record_number] > = " & chr(34) & Forms![frmPolicy]!
[txtURN] & chr(34) & " AND [year_of_account] = " & chr(34) & Forms!
[frmPolicy]![txtyear_of_account] & chr(34)

add another line:
msgbox "strSQL is " & strSQL

otherwise you won't actually see what strSQL looks like!

JB