DSum Help

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
LVL 1
perennialAsked:
Who is Participating?
 
dannywarehamConnect With a Mentor Commented:
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
 
dannywarehamCommented:
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
 
GRayLCommented:
Try this, assumig ItemNo is a number, not text:

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

0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

 
perennialAuthor Commented:
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
 
dannywarehamCommented:
This is ON a form, rather than in code?
0
 
GRayLCommented:
Are you sure of table and field spelling?
0
 
perennialAuthor Commented:
Got it.  Let me test it out and I will let you know.

perennial
0
 
rockiroadsCommented:
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
 
perennialAuthor Commented:
ItemNo is a numeric field...however I believe dannywareham got.

Thanks for the suggestion.

perennial
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.