?
Solved

DSum code not working

Posted on 2011-04-25
8
Medium Priority
?
378 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
0
Comment
Question by:SteveL13
  • 3
  • 3
  • 2
8 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35462091
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'")
0
 

Author Comment

by:SteveL13
ID: 35462224
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.

???

0
 

Author Comment

by:SteveL13
ID: 35462254
 Runtime error 3075... (Missing operator)
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 13

Expert Comment

by:Lucas
ID: 35462422
Try this:

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

Author Comment

by:SteveL13
ID: 35462466
Sorry.  That did not work either.  Here is the exact error I'm getting...

 screen shot
0
 
LVL 13

Expert Comment

by:Lucas
ID: 35462542
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 & "#" & "")
0
 
LVL 13

Accepted Solution

by:
Lucas earned 2000 total points
ID: 35462552
Oops:

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

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35462606
sorry you don't need the "=" sign


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

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question