Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2019
    Posts
    5

    Question Sequential Numbers based on two data fields from the same tables

    Hi AccessForum,
    I have a table in which there are two fields (e.g. Dept/Disc and DocTypes) that I want to compare and via a query automatically calculate the next sequential number. For ease I have attached the table data sample.
    [IMG]file:///C:/Users/rbelmar/AppData/Local/Temp/msohtmlclip1/01/clip_image002.jpg[/IMG]
    I have used the below expression in the query:
    CalcSN: DCount("[Dept/Disc]","TblDocument Number Generator","[Dept/Disc] = '" & [Dept/Disc] & "'")-DCount("[DocTypes]","TblDocument Number Generator","[DocTypes] = '" & [DocTypes] & "' AND [M_Id] > " & [M_Id])
    Note:
    The Field “SeqNo” is based on the following Expression:


    SeqNo: Format([CalcSN],"00000")
    What I was expected to achieve in this case was to have:
    M_Id Dept/Disc DocTypes CalcSN SeqNo DocNumber Document Title
    35 OHS POL 1 00001 OHS-POL-00001 OHS Policy
    36 COPP POL 1 00001 COPP-POL-00001 COPP Policy
    37 OHS PRO 1 00001 OHS-PRO-00001 OHS Procedure

    However, I am baffled as to why the outcome is not the correct results. Can someone please help me out on this matter?

  2. #2
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You have two DCount()s in the "CalcSN" column in the query. I broke them out into separate columns - this is what the result was
    Click image for larger version. 

Name:	Presentation1.png 
Views:	13 
Size:	53.4 KB 
ID:	37449


    Then I changed the CalcSN formula - this is the result:
    Click image for larger version. 

Name:	Presentation2.png 
Views:	12 
Size:	53.7 KB 
ID:	37450


    I then added two more rows for Dept/Disc = OHS and DocTypes = POL
    This is the result
    Click image for larger version. 

Name:	Presentation3.png 
Views:	12 
Size:	99.7 KB 
ID:	37451

    If this is the results you are looking for, the formula I used is
    Code:
    CalcSN: DCount("[DeptDisc]","TblDocumentNumberGenerator","[DeptDisc] = '" & [DeptDisc] & "' and [DocTypes] = '" & [DocTypes] & "' AND [M_Id] <= " & [M_Id])
    NOTE: I deleted the slash and the spaces in the table name.
    [DeptDisc] instead of [Dept/Disc]
    TblDocumentNumberGenerator instead of TblDocument Number Generator.

    Object names should be only letters and numbers (and the underscore)
    Do not use spaces, punctuation or special characters in object names.




    Edit: Forgot to add "Welcome to the forum".
    Last edited by ssanfu; 02-14-2019 at 07:07 PM. Reason: Added welcome

  3. #3
    Join Date
    Feb 2019
    Posts
    5
    Hi ssanfu,

    Yes the fix you came up with helped, I updated the table name, field as suggested and the query and happy days!
    Thank you so much for the prompt assistance.

    Whilst I have you on this forum do you know of someone who has come up with a VBA code or soething else fit for purpose to retrieve the UserName and ComputerName once they open the form to create a new document number?

    Kind regards,
    Roberto Belmar

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Simplest is the Environ() function.

    environ("username")


    environ("computername")
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 02-08-2019, 11:24 AM
  2. Add letters to sequential numbers
    By bassplayer79 in forum Programming
    Replies: 29
    Last Post: 09-12-2013, 04:20 AM
  3. Sequential numbers...
    By jlgray0127 in forum Programming
    Replies: 1
    Last Post: 04-03-2013, 10:06 AM
  4. Sequential Numbers
    By cactuspete13 in forum Queries
    Replies: 5
    Last Post: 03-27-2013, 12:14 PM
  5. Passing data to sequential form fields
    By jeepfamilyva in forum Forms
    Replies: 0
    Last Post: 06-28-2009, 11:04 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums