By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,827 Members | 2,220 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,827 IT Pros & Developers. It's quick & easy.

DCount function microsoft access

P: 1
Hi, I am having a trouble in microsoft access function. I am going to make a sales database.

So the database will have 1 table of sales and inside it will have the data of who is the person in charge for that sales and generate sales ID by using this format:

01 for sales A
02 for sales B
03 for sales C

so the sales ID will be

01-001 for first case handled by sales number 1
01-002 for second case handled by sales number 2
02-001 for first case handled by sales number 2

I try to put this inside a form so once we input the name in the form, it will generate the ID automatically, but the problem is every time I use the DCount, it count the whole sales. I have been trying since yesterday, please help

Here is my code:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Case_Owner_AfterUpdate()
  2.     Dim num As Integer
  3.     If (Me.Case_Owner = 1) Then
  4.         num = DCount("[Case_Owner]", "Sales", "[Case_Owner] = 1")
  5.         Me.No = "01-" & num + 1
  6.     ElseIf (Me.Case_Owner = 2) Then
  7.         num = DCount("[Case_Owner]", "Sales", "[Case_Owner] = 1")
  8.         Me.No = "02-" & num + 1
  9.     End If
  11. End Sub
It is on the sales table
Nov 4 '13 #1
Share this Question
Share on Google+
3 Replies

Expert Mod 2.5K+
P: 3,283

Your questions and your code may be a bit confusing, and may become very clumsy if you add more sales people.

First, becuase you say:

"01 for sales A
02 for sales B
03 for sales C

this implies that "01" is NOT an integer, but a text field. Please verify that it is either 1, 2, 3... or "01", "02", "03".... This is a start.

Second, your code:

Expand|Select|Wrap|Line Numbers
  1. If (Me.Case_Owner = 1) Then
could get incredibly cumbersome once you start to have beyond two or three sales people.

A more effective way would be this:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Case_Owner_AfterUpdate() 
  2.     Dim num As Integer 
  3.     num = DCount("[SalesID]", "Sales", "[Case_Owner] = " & Me.Case_Owner) 
  4.     Me.No = "01-" & num + 1 
  5. End Sub 
Notice how I also have the DCount looking at the SalesID (or whatever field you may have as a Primary Key (which should really make no difference, because it's going to count records either way....

However, if your 01, 02, 03 is a text field, then the DCount would look like this:

Expand|Select|Wrap|Line Numbers
  1. num = DCount("[SalesID]", "Sales", "[Case_Owner] = '" & Me.Case_Owner & "'")
However, you may still have a few challenges facing you, because it looks like you want your SalesID to have leading zeros for numbers less than one hundred:

01-001, 01-002 ... This would require finding out how long the integer is, then adding leading zeroes, etc...

Please clarify your data types within table Sales and provide a little more detail for your issues.

Grace and Peace,
Nov 4 '13 #2

Expert 100+
P: 634

to give a definitive answer we need more infomation as to what wxacty is stored in and/or what Data Type [Case_Owner] is?

I suspect it is Text with containing "01-002" for instance.

If this is the case the your DCount should be

Expand|Select|Wrap|Line Numbers
  1. DCount("[Case_Owner]", "Sales", "Left([Case_Owner],2) = '01'")

Expand|Select|Wrap|Line Numbers
  1. DCount("[Case_Owner]", "Sales", "Left([Case_Owner],2) = '02'")
If this is not the case then please provide the info indicated.


Nov 4 '13 #3

Expert Mod 5K+
P: 5,397
This type of question comes up very often here on

What we usually find is that the database is not normalized:
> Database Normalization and Table Structures.

Once the database tables are straightened out this then becomes a very easy thing to accomplish.

With a very simple example:

In tbl_xaction:
[xaction_primarykey] autonumber
[xaction_foriegnkey_salespeople] long related to tbl_salespeople
[xaction_salespesonsequence] long
[xaction_foriegnkey_itemsold] long related to tbl_itemsold

So in the normal corse of things you would do an agregate query SQL Aggregate Functions
that I would use a parameter to filter down to just the [salespeople] id of interest and return the current maximum value (Min, Max Functions) in the sequence field.
If you do this in VBA, the SQL would look something like:
Expand|Select|Wrap|Line Numbers
  1. SELECT tbl_xaction.[xaction_foriegnkey_salespeople]
  2.    , Max([xaction_salespesonsequence])
  3.        AS MaxOfxaction_salesperson_seq
  4. FROM tbl_xaction
  5. GROUP BY tbl_xaction.xaction_fk_salespeople
  6. HAVING (((tbl_xaction.xaction_fk_salespeople)=[askme]));
Note in the HAVING clause, there is "[askme]" which in VBA would be replaced with the sales person primary key... there are alot of ways to do this... Use parameters in queries and reports

However, in the VBA, with a recordset open to this query you then only need to pull the value from the field [MaxOfxaction_salesperson_seq] add one, and then create your record etc...

If you will search on ADezii and Rabbit you will several code snipettes that will also help should you not want to handle things this way.

You can use a calculated control on a form/report (or in a query the field) to create and format the final sequence number as you need/see fit.Create a calculated control
Nov 6 '13 #4

Post your reply

Sign in to post your reply or Sign up for a free account.