Creating A Custom Formatted Id Number

Jul 19, 2007

I'm trying to create an auto generating ID number field that contains a date followed by a sequential number for that date. In example...


tomorrows numbering would be
070720002 and so on.

Each new day would start a new sequence of 001, 002, 003, etc.
I have searched these forums and haven't really found any answers. Could someone direct me in the correct direction. Also I need this number to be saved in the table and not just displayed or printed.

View Replies


Queries :: Returning Set Of Numbers From Custom Formatted String - Incorrect Use Of Join?

Mar 20, 2015

I suspect my design is flawed

SELECT tblData2.Prefix, tblData2.LineNum, tblData2.Year, tblComments.comment, tblComments.Address
FROM tblData2 LEFT JOIN tblComments ON tblData2.LineNum = (NumbersOnly([tblComments].[Address])
WHERE (((tblData2.MyYear)<1990))
ORDER BY tblData2.LineNum;

The NumbersOnly function returns a set of numbers from a custom formatted string. The above fails on the join (I think) but maybe there's another way of doing it?

View 4 Replies View Related

Modules & VBA :: Excel File When Formatted In Access Shows Wrong Number

Nov 7, 2013

I am opening an excel file from access and changing the formatting from "dd/mm/yyyy hh:mm" to number with 15 decimal places.Then I am linking the file to the database and subsequently doing a lookup on the date time on the access form against this table.The excel file when formatted from vba in access shows the wrong number

datetime = 03/05/2013 11:26
database number = 41397.4763888889
Excel file number = 41398.4763888889 (which would equal 04/05/13 11:26)

now if I do it manually then I get 41397.4763888889 or if the macro is in excel I still get 41397.4763888889.Also I have tested the code with manually editing the excel file and this works fine.

Private Sub Command288_Click()
Dim s As String
Dim t As Integer
Dim ws As Worksheet
s = LaunchCD(Me)
MsgBox (s)


also this brings up a form and then I select the field required for the lookup and with the button is pressed the rest of the code runs as follows.

Private Sub LoadData_Click()
Dim w As String
w = Forms![frmList1]![Combo0]
w = "[" & w & "]"
Dim dtA As String


View 7 Replies View Related

Creating Custom PivotChart

Jun 28, 2005

I am trying to make a special kind of blob chart. It has to mimic the charts I have attached. I have a high number and a low number for everything that someone might want to put on the axes. The eventual goal is to have a form were the user selects what goes on each axis, and have a few other possible choices to limit how much is displayed. What I am making doesn't have to be as fancy, flexablity is more important. Can Access make this kind of graph or am I on my own, making shapes in VBA?

View 3 Replies View Related

Creating Custom Letters????

Dec 23, 2004


I would like to create a custom letter where I can type the body of the letter and it will print the letter for every customer in the database.


View 1 Replies View Related

Pages - Creating A Custom Theme

Apr 18, 2007


Can anybody help?

I would like to create a custom theme for my INPUT page, however within Access 2003 I only have the option of about 20 predefined themes. I want to insert my companies' logo onto the INPUT PAGE.

What I would like is to save a template, like I have done in REPORTS, so that when I build a INPUT PAGE I can select my own theme to use, based upon my companies' logo, type face et al .,

Thanks and kind regards.

Phil Drury.

View 1 Replies View Related

Creating Custom Date Format After Import

Aug 28, 2004

I am stumped: After importing from an outside source (an excel spreadsheet) I have to change the short-date format
that I have just imported into a very specific custom format: "yyyymmdd" Ex: 20040828. How do I convert into this
new format? Help will be greatly appreciated...

View 1 Replies View Related

Modules & VBA :: Creating A Custom Shortcut Menu

Jun 5, 2014

I'm working to put together a shortcut menu for a form that will be viewed as a datasheet. I'm trying to put together the vba to create the menu. I'd like a lot of the standard options i.e. sort a-z, filter toggle etc. I'd also like to add the menu option that is displayed in the default menu called "Text Filters". I've been unable to find the id code for that option, and since it, when chosen opens another menu, I'm not real sure how to code it. Here is what I have so far:

Sub CreateWIPShortcutMenu()
Dim cmbRightClick As Office.CommandBar
Dim cmbControl As Office.CommandBarControl
' Create the shortcut menu.
Set cmbRightClick = CommandBars.Add("cmdWIP", msoBarPopup, False, True)


View 2 Replies View Related

General :: Creating Custom Menu Bar For Form?

Jul 2, 2012

i would like to create custom menu bars for my forms report

View 6 Replies View Related

Modules & VBA :: Creating And Initializing Custom Class / Object

Jun 13, 2013

I've been playing around with creating my own class in VBA but I'm having problems calling its methods. My class is pretty basic, its called cDentist and the properties are just Name, Address, DOB etc and one method AddDentist. AddDentist will add the details to a sql server table.

So I create an instance of the class in a module called Dentist. At the very top of the code i put..

Option Compare Database
Global Dentist As cDentist

I have a method then in module Dentist, where I initialise the instance of class cDentist and i populate it's properities...

Sub RecordDentistDetails()
Set Dentist = New cDentist
Dentist.Name = Forms!frm_enterdetails!txtName


When I click 'Save' I get error, Method or data member not found. And '.AddDentist' is highlighted. I'm a little confused why it's not seeing AddDentist as a method of class cDentist.

View 14 Replies View Related

Modules & VBA :: Creating Custom CommandBarControl Objects For A Shortcut Menu?

Aug 16, 2013

I finally got tired of having 100 macros managing my different custom shortcut menus, and decided to figure out how to generate the shortcut menus programatically (because lets be honest, Macros are the devil.)

I found a good tutorial here : [URL] .....

and was able to create some basic shortcut menus like the following:

Sub CreateCopyShortcutMenu()
Dim cmbshortcutmenu As Office.CommandBar
Set cmbshortcutmenu = CommandBars.Add("CopyShortcutMenu", _
msoBarPopup, False, False)
'ID 19 adds copy command
cmbshortcutmenu.Controls.Add Type:=msoControlButton, Id:=19
End Sub

Nice and simple, now I have a copy command. But the problem is that I also need some custom commands. Most (possibly all?) of these would be function calls.

View 3 Replies View Related

Custom Generated Number

May 10, 2005

Quote by Pat Hartman
>>>BTW, one somewhat undesirable thing happens when you go with replication. Access changes ALL your autonumbers to random instead of sequential. That means that from that time on, all new autonumbers will be assigned randomly to minimize the problem of conflicts between the design master and replica sets. A random autonumber can range from a very small negative number to a very large positive number. These autonumbers can no longer be used as your customer numbers or order numbers if these numbers are ever printed or given to the customer to be used for reference. A customer probably will be confused by an order number = -2. If this is going to cause you a problem, you'll need to switch to custom generated numbers. Post back for details if you need them.<<<<

I'd be interested in getting some details. Data will be entering that table through a data entry form and through appending from a second copy of the DB. I want to make the PK a random autonumber but then when the second DB is added to the Master DB have the table in the Master DB assign each record a "prettier" more usable id number. I know this is stupid but I'm not sure how to relate code to a table (there's no before update event for a table as far as I'm aware).

Thanks for any help you can give.


View 1 Replies View Related

Custom Auto Number

May 19, 2013

I have created a Fiscal Database and want to create a custom ID Auto number. I would like the ID number start with FCP, the Fiscal Year (comes off of field of form), Fund Control Point (comes from the form) and a 4 digit auto number (to start with 0001).
Example: FCP-2013-021-0001

Of course, this number gets stored to the Table and the ID number is generated/viewable (Un-enabled) in the form when information is entered into the form.The Fund Control Point is a combo box within the form and does auto populate some other fields within the form.

View 1 Replies View Related

Custom Line Number On Report ?

Oct 6, 2006

I try to explain my problem.
There is a report have 50 records.
I print the report first time and Set the number by Data>running sum method. This start from 1 to n...

I require a Form Where i enter to initial number example 22 and say preview report.
The report Set the line number of Details Section Like
24 etc...

How can i do this. Please help

View 6 Replies View Related

Custom ID With Year And Incrementing Log Number

Dec 16, 2014

Been looking around for a solution for a custom ID and can't quite find one that's specific to my problem.

I am creating a log and I want to give each new log entry a new log number. The format would be: IEyy-xxx. The ID is the letters IE, then 2digit year, a dash, then 3digit incrementing number. The ID should reset back to 001 every year (e.g. if IE14-623 is last log entry of 2014, first log entry of 2015 would be IE15-001).

My table has an autonumber that I will use as the primary key- which is not the log number- and looks like this:

These are the fields in my table:

Index Log Number (autonumber and primary key)
IE number
Program Name
Date Created

View 1 Replies View Related

Creating A Serial Number That Is NOT Auto-number

Mar 20, 2012

I have a table called "OrderDetails" with following fields:

OrderID (Primary key)

I want to create a data entry subform that can used to enter order details in this table such that, for a given OrderID, the Num field is automatically set to previous number + 1. For example, for OrderID = 12, if there are 4 products that need to be entered, the 4 records should automatically take 1 , 2, 3, 4.

View 6 Replies View Related

Forms :: Custom Sequential Auto-number Of Different Series

Aug 18, 2015

I am using Ms Access 2007 and I am new to it..I have table of following fileds

ID (as a primary key)

AccountingYear (Filed for current accounting Year e.g., 14-15; 15-16)

Series (text- as R, Y, B etc to denote series code)

OrderNo (field to store custom autonumber Order No e.g., for Series R-1, R-2, for series Y Y-1, Y-2, etc)

I have created a form using all these fields..Now what i want to do is I want to create custom order no as first 2 digit of accounting year+Series+Autonumber of respective series as



Means autonumber starts with 1 for every accounting year of respective to do this and what code or formula should be applied and where the code should be inserted as I am new to Ms access

View 1 Replies View Related

Reports :: Custom Reports Creating Chart Based On Month Not Calendar Year

Jun 15, 2015

I am editing a database that provides the option of creating custom reports, where the user can input a date range of their choice and receive aggregate data for that time frame. Although all of the numbers in the report are correct, I am having trouble with a chart that I inserted into the report.

Specifically, if the date range requested spans 2 calendar years (i.e. April 2014 through January 2015), the data for January 2015 appears at the beginning of the year (so the chart x-axis is for Jan through Dec, and the Jan 2015 data is showing up in Jan (as if it was 2014, not the end of the given range in 2015). When I try with smaller time frames within a calendar year, it adjusts just fine (i.e. shrinking the window so just March-May is displayed on the graph).

How to adjust the axis so that it properly records the data range- so that it would start the axis with April and end in January, for example?

View 2 Replies View Related

Creating A Custom "What's This?" Button In Forms

Aug 19, 2004

Is there a way to use a command button to perform the exact same function as the "What's This" (?) button on the title bar? Is there code I can give it "On Click" that will make it do the same as as the What's This button?

View 2 Replies View Related

Forms :: Creating Number Of New Forms To Edit Number Of Tables

Oct 29, 2013

I am using a template database that I downloaded from the Microsoft Access template website and have been modifying it and adding new forms and tables etc. I have used the forms wizard to create a number of new forms to edit a number of the tables. The forms are "split forms".I can't seem to get many of the forms to stay the size that I set them to! They seem to have a mind of their own and often when I think that I have sized them correctly, I then open the form and they display in full screen mode or larger!

View 8 Replies View Related

Creating A Random Number

Jul 8, 2006

Quick question, how do I have access generate a random number between 1 and 8? I want it to generate either a 1,2,3,4,5,6,7, or 8. (no zero, no nine)

Expr1: Fix(Rnd(8-1)*10*Rnd()) is what I have been using, but this always generates the number 2? I do want the decimals to be rounded to the nearest integer, thus I have Fix.

If someone could please help me, it would be greatly appreciated! Thanks in advance.

View 3 Replies View Related

Creating ID Number In Access

Mar 1, 2012

at the moment we have been given this problem where we have to incoporate a Dmax function into the following code

IDnumber = Left(Firstname, 3) + Left(Lastname, 2)

from this code the ID number creates something that looks like this


however i need a four digit number after this ID, for every different ID i need the numbers to increase by one the following code was given to us to do in Got Focus but this did not work

IDnumber = Left(Firstname, 3) + Left(Lastname, 2) + Trim(Str(DMax("right(IDnumber,4)","Customer tbl")+1))

View 3 Replies View Related

Creating Reference Number From 3 Fields

Oct 19, 2005

Hi there,

I'm making a document control database where the a unique reference number is automatically generated from 2 tables and updates a 3rd table.

xxxx or yyyy/lookup in 2 table/autonumber

I have been trying to do this is a form but because the control sauce is a calculation 'Text1&Text2&Text3' I am unable to update the 3rd table with the combined reference number.

I have limited knowledge particularly of VBA coding

Any help much appreciated

View 1 Replies View Related

Need Help Creating Auto Customer Number

Sep 27, 2004


I am v new to Access and am creating my first DB.

I want to create a unique client office code....

For example, for a (law firm) client name Brown Smegmore in Sydney,
I want to create a code like BS_S_1, Brown Smegmore in Melbourne: BS_M_2

I would really really like some simple help



View 3 Replies View Related

Tables :: Creating A Unique Invoicing Number?

Aug 19, 2013

What I have is a small POS system I'm trying to create a unique invoice number for each transaction (multiple records)

I have two tables, one named "CurrentTransactions" and one named "Past Transactions".

I have everything working except the invoice number my system works by adding pre defined records into the table "CurrentTransactions" and once the sale has been finalised it transfers the data to the "PastTransactions" table.

take the last number from the "PastTransactions"."InvoiceNumber" to create an invoice number in the "CurrentTransactions", and I want to be able to do it using an expression in the default value property as opposed to in the form ( I've tried the form way but using Dmax would require me to almost rebuild my entire POS system )

View 5 Replies View Related

General :: Creating Auto-Sequential Order Number

May 6, 2013

I am trying to create an auto-sequential order number

Example : AB000112 - AB is standard prefix, ''0001'' is the first invoice number, ''12'' is the year 2012.

so the next order number should read AB000212 and the next AB000312

Out of this, I have to issue several debit notes relating to the same order number when the payments are due from customers over a period of time, this will be


Can such numbers be the primary key?

View 4 Replies View Related

Copyrights 2005-15, All rights reserved