We help IT Professionals succeed at work.

DSum code not working

SteveL13
SteveL13 asked
on
Medium Priority
391 Views
Last Modified: 2012-06-21
What is wrong with this DSum code?

Me.txtBooked = DSum("[QuotedSales]", "OpenJob", "[BookedDate] = between " & Chr(34) & Forms!frmSelectDateRange!txtStart & Chr(34)) And " & Chr(34) & Forms!frmSelectDateRange!txtEnd & Chr(34))" And [ProductCode] <> 350

--Steve
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016

Commented:
is ProductCode a Number or Text data type?

if Number use this
Me.txtBooked = DSum("[QuotedSales]", "OpenJob", "[BookedDate] = between  #" & Forms!frmSelectDateRange!txtStart & "# And   #" & Forms!frmSelectDateRange!txtEnd & "# And [ProductCode] <> 350")

if Text

Me.txtBooked = DSum("[QuotedSales]", "OpenJob", "[BookedDate] = between  #" & Forms!frmSelectDateRange!txtStart & "# And   #" & Forms!frmSelectDateRange!txtEnd & "# And [ProductCode] <> '350'")

Author

Commented:
It is a number field.  I am running the code...

Me.txtBooked = DSum("[QuotedSales]", "OpenJob", "[BookedDate] = between  #" & Forms!frmSelectDateRange!txtStart & "# And   #" & Forms!frmSelectDateRange!txtEnd & "# And [ProductCode] <> 350")

On an afterupdate event of the field, "txtEnd" and get a syntax error message.

???

Author

Commented:
 Runtime error 3075... (Missing operator)
LucasMS Dynamics Developer
CERTIFIED EXPERT

Commented:
Try this:

Me.txtBooked = DSum("[QuotedSales]", "OpenJob", "[BookedDate] = between" & "#" & Forms!frmSelectDateRange!txtStart & "#" & " And " & "#" & Forms!frmSelectDateRange!txtEnd & "#" & " And [ProductCode] <> 350")

Author

Commented:
Sorry.  That did not work either.  Here is the exact error I'm getting...

 screen shot
LucasMS Dynamics Developer
CERTIFIED EXPERT

Commented:
Why does it have a ' at the end of 350? you also need a space after the between.  I changed it a bit.  You might have to remove the last ".

Me.txtBooked = DSum("[QuotedSales]", "OpenJob", "[ProductCode] <> 350 and [BookedDate] = between " & "#" & Forms!frmSelectDateRange!txtStart & "#" & " And " & "#" & Forms!frmSelectDateRange!txtEnd & "#" & "")
MS Dynamics Developer
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Top Expert 2016

Commented:
sorry you don't need the "=" sign


Me.txtBooked = DSum("[QuotedSales]", "OpenJob", "[BookedDate]  between  #" & Forms!frmSelectDateRange!txtStart & "# And   #" & Forms!frmSelectDateRange!txtEnd & "# And [ProductCode] <> 350")
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.