Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

DCount Syntax

Posted on 2014-02-26
4
Medium Priority
?
334 Views
Last Modified: 2014-02-26
Hi,

Table is tblOrders
Field Name is CustomerNumber

I want to display the number of times a CUSTOMER has placed an order.

So, if I am looking at a form that displays a single order for CUSTOMER 888 - I want the form to display that this CUSTOMER has 999 Orders already.

Each time an order is displayed on the screen , then there should also be a display of the total number of orders placed by that customer.

What's the DCOUNT format??

Thanks!
0
Comment
Question by:Patrick O'Dea
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 19

Assisted Solution

by:MINDSUPERB
MINDSUPERB earned 600 total points
ID: 39888381
Try this:

=DCount("[CustomerNumber]", "tblOrders", "[CustomerNumber] = 888")

Sincerely,

Ed
0
 
LVL 85
ID: 39888384
Without knowing your table structure it's hard to give exact syntax, but assuming you have an Orders table, with a field named Customer, then something like:

Nz(DCount("IDField", "Orders", "Customer=" & Me.Customer),0)

If "Customer" is a Text field:

Nz(DCount("IDField", "Orders", "Customer='" & Me.Customer & "'"),0)

This would tell you the number of orders EVER placed by the customer.

"IDFIeld" is any field in the table, although it's generally better to use an indexed field. If you have an Autonumber field in the table  use that.
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1400 total points
ID: 39888386
Note that you'd want to do this on the Form's Current event, so something like this:

Sub Form_Current()
  Me.YourControl = Nz(DCount("IDField", "Orders", "Customer='" & Me.Customer & "'"),0)
End Sub
0
 

Author Closing Comment

by:Patrick O'Dea
ID: 39888467
Thanks!
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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 how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

721 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