Solved

DSum Help

Posted on 2006-06-29
9
498 Views
Last Modified: 2008-03-10
I have a control source on an unbound text box in a form as follow:

=Nz(DSum("[Qty]","tblCutSourceInvtySM","[ItemNo]=" & [Me].[ItemNo] & " AND “[DatRevd] >= “ & #" & (Date())-7 & "#"),0)

When I try to run the form, in the text unbound text box it display this message:  #Name?

What did I forget to put in?

perennial
0
Comment
Question by:perennial
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 26

Accepted Solution

by:
dannywareham earned 500 total points
ID: 17012394
The error  #name?  means that it doesn't recognise the function parts.
Either wrong arguements, or wrong names.

Try:

=NZ(DSum("Qty", "tblCutSourceInvtySM", "ItemNo =" & [ItemNo] & " AND DatRevd >=#" & Date()-7 & "#"),0)


0
 
LVL 26

Expert Comment

by:dannywareham
ID: 17012404
Your table names/fields don't need to be in square brackets.
Also, form controls are only refered to as ME in code - not calculated controls.

What you have is half written as code and half written as calculated field (like a query)
0
 
LVL 44

Expert Comment

by:GRayL
ID: 17012411
Try this, assumig ItemNo is a number, not text:

=Nz(DSum("[Qty]","tblCutSourceInvtySM","[ItemNo]=" & [Me].[ItemNo] & " AND [DatRevd] >= #" & (Date())-7 & "#"),0)

0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 1

Author Comment

by:perennial
ID: 17012428
Here is a message:

The object doesn't contain the Automation object 'DatRevd.'

You tried to run a Visual Basic procedure toset a property or method for an object. However, the component does't make the property or method available for automation operations.

Check the conponent's documentation for information on the properities and mothods it maks available for automation operations.

I am not sure what it mean....did I not allow or check something?

perennial
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 17012457
This is ON a form, rather than in code?
0
 
LVL 44

Expert Comment

by:GRayL
ID: 17012526
Are you sure of table and field spelling?
0
 
LVL 1

Author Comment

by:perennial
ID: 17012855
Got it.  Let me test it out and I will let you know.

perennial
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17013108
Hey prennial, is ItemNo definitely a numeric field and not a string
mind u, you probably get a different error message anyway
But if it is a string, u need to use single quotes

After trying the suggestions from the other experts

try this

select qty, itemno, daterevd from tblCutSourceInvtySM

does this query work?

if so, An alternative is to use DateDiff

Nz(DSum("Qty", "tblCutSourceInvtySM", "ItemNo = " & ItemNo.Value & " AND DateDiff('d',DatRevd,Now())>=7)"), 0)

0
 
LVL 1

Author Comment

by:perennial
ID: 17013151
ItemNo is a numeric field...however I believe dannywareham got.

Thanks for the suggestion.

perennial
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Dot leaders between two labels in report 10 33
Setting that's causing Access to crash 19 34
Problem with vba code 4 42
How to properly refer to a form in the code below 2 23
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

837 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