I would like to expand on this questions. I would like to generate a number based on a reference I use, a format like "14/000" where I use "14" with the last 2 digits of the year. However i want the auto number to generate the the last 2 digit automatically when the year changes i.e. when 2015 comes the field generates "15/000" without any prompt from the user.
View RepliesI need the auto number to be in specified format as ASI-23-0001. So I added the format "ASI-23-"0000 in the formats and I got it succesfully.The fueld name is "Part Number"
I have added the Part Number field to a form as combo and I was able to list the values in the drop down combo.
I wanted to use Dlookup command in the program to auto fill the next field in the form say "Description".
I am using the following code
Me.Text30 = DLookup("[Description]", "item_master", "[Part Number] = Form![Combo36]")
But now the data in the combo is treated as text because of the prefix "ASI-23" and I get data type mismatch error.
My autonumbers are not in order for tables.As in for example for a customer
customerid customer first name customer last name
17 john parker
18 james johnson
19 alex scott
This is right at the top of the table the first sets of data,i want it to be
customerid
1
2
3
4
5
,etc
Why does it have a random number like "17" when I DO NOT have the first 17 customers !
Is there a way to show negative numbers in brackets without selecting the Currency format?
I want to show (75,000.00) and not -75,000.00
I have a table, at the table I'v got these fields:
ID | num1 | num2 | sum
I want that the user put numbers at 'num1' and 'num2' fields and then the 'sum' field will calculate automatically the operator (sum=num1+num2).
I've tried to put any combination at 'Default value' of the sum field (all the fields are numbers , also tried to change the sum field to text...nothing works).
I'm getting a message that 'num1' field is not recognize at the table
(I tried =[num1]+[num2], without the '=', num1.table+num2.table , ...nothing works)
I also tried to do it with SQL command but it dosn't work.
There is any way to do it, is it possible? Or other way to do it at least at Form or at Report ?
Is there a way I can format AutoNumber for a primary key, for example, have the first ID start as C001 then for the next entry Access will automatically go to C002, and so on and so forth?
View 14 Replies View RelatedI would like to format the AutoNumber field so that it shows the current year - #...i.e. 2012-01.
View 4 Replies View RelatedI set my phone number to be formatted like (920)123-4567
I copied data from an excel spread sheet - now the phone numbers are like 920-123-4567.
Is there a way that I can update the phone numbers to be the correct formatting (920) 123-4567?
Hello,
I have extracted 4 digit numbers from a long strings of characters and numbers which I have identified as date. Some are inputted as
mmdd (example: 1129)
ddmm (example: 2310)
I have to calculate the number of days between the start and end dates. I tried formatting as date, but I can't get it to work.
Any suggestion?
Thanks!
Hello,
I am new to the world of databases, and I would like to know the easiest way to have a column with auto incrementing numbers.
I want that everytime a user enter data in a form, the new record goes into the database with a unique number.
thanks in advance
I have used priamry keys in my tables and these are set to auto numbers on the advise of fellow forumites.
One question if at the end of every year I need to upload batches of data to my tables, this would mean I would need to go through each record and manually enter the foreign keys in the linked table.
Surely Access has a better way of dealing with this issue?
I have a field in a query that contains numbers and text (text field). The numbers displayed come from a percent calculation and display with many decimals ie, .99898745987245. Is there a way to eliminate the decimals with code in the query field? For example .99898745987245 to equal 99%? I can’t format the field as a number or percent because it has both text and numbers. HELP!!
Thanks
Hi There,
I have a table that has two numbers, both require incrementing with each record.
These take the form of "00"0"/05" and "Book11 Enc"0.
I want these to update automatically but Access won't let me use two autonumbers in same form. Does anyone have a suggestion to get round it ?
Thanks.
Alex.
I have a field for SSN. If the SSN has a 0 or 00 as the first numbers it auto deletes those numbers. Is there a way in access that when the record is visible the 0's are relpaced so that the SSN will read the 9 digit. Also i have another fiels EIN with 7 digits an i need for there to be 2 9's in the beginning. I was goingto use vba but when i go to yp it i only get .value <
Code:
Me.SSN.Value
Value is tho only option to get. IDK y it does this or how to get it to shop if there is a ay.
Ineed to track Trailer security seals that are sequentially numbered with 8 digit numbers...They are physical seals and no efile with the numbers to upload into Access...is there a way I can put in a range and Access will populate the rest in a table?
View 5 Replies View RelatedI have an Access DB with a VB 6.0 front-end. I have a table with a PK that uses an auto-number. I deleted from data from this table in the past, then re-added it back in again just now. However, now when I go to enter a record in this table from the VB executable, I am getting an error for duplicate keys--as the auto-number tries to use a number that already exists. This happens when I just enter a record directly in the table in the DB also.
View 1 Replies View RelatedI have a table which contains list of data like,
10025.325
100.245
1245.8464
12546.356
etc.. etc.....
Now, I need a data which contains numbers 5 digits before decimal point & 3 digits after decimal point.
like xxxxx.xxx
I have a very basic application to manage the tasks. In the main form "Tasks" one of the field is the priority (Order by) and I have this example:
Task Priority
B 1
A 2
C 3
E 4
D 5
If the user change one priority, I need a code to automatically change the others too respecting the new priority order. For example if the user change the priority 3 to 1 for the task C, the other numbers have to change to become:
Task Priority
C 1
B 2
A 3
E 4
D 5
There is a way to do this?
I have inherited an Access DB that is supposed to be kept "alive" but not made much better. On a weekly basis I get an Excel CSV that I'm supposed to import into one massive table; that works just fine.
However, there is another query that takes values from a table named "New Users From Import Table" and appends them to the current "User" table.
But before running that query I have to manually update a field called "Display Name ID" with the next highest number from the User table but only the highest value that isn't in the 9000 number range.
For example, my next highest "Display Name ID" is 1144, which of course does not include the records that range between 9000 - 9008.
I had to manually enter 89 "Display Name ID" values yesterday and I know there has to be a way to update that field when I pull the data over from the temp table without having to do it all manually.
Here's the query that pulls the data into the "User" table (after I've manually entered the incremented values).
INSERT INTO Users ( [Display Name ID], [User Type], Organization, [Display Name], [Alias Name] )
SELECT [New Users From Import Table].[Display Name ID], [New Users From Import Table].[User Type], [New Users From Import Table].Organization, [New Users From Import Table].[Display Name], [New Users From Import Table].[Alias Name]
FROM [New Users From Import Table];
how to auto increment one field based on the value in another field. What I have is a table with six fields. There is a foreign key assigned to each record and linked to another table. Four fields in this table are number fields indicating accordingly a number for a category, a number for a subcategory, a number for a subject for a subcategory and a number for an instruction for each subject of subcategory. The last field is the text field for each instruction. Numbers are required to be included in the report. Also, number of categories, subcategories, subjects and instructions vary by each record in the the table which this table is linked to. What I'm trying to accomplish is to have numbers in the subcategory to auto increment starting with "1" for each number in the category field, numbers for each subject field would auto increment starting with "1" for each number in the subcategory field, and numbers in the instructions field to auto increment for each number in the subject field starting, again, with "1". I also need these numbers to restart with "1" for each new record in another table.
Basically, the table would look like this:
cat subcat subj instruction
1------1-------1------1
1------1-------2------1
1------2-------1------1
2------1-------1------1
2------1-------2------1
3------1-------1------1
3------1-------2------1
1------1-------1------1
(if the record in another table is a new/next table)and so forth..I have been looking for anything that could remotely work for me. Every VBA code I've tried is either don't work the way I need it to work, other codes making the field to continue increment numbers if the number already exists in the other field.
I need to create a MemberID field that autonumbers and uses a prefix.
EG: YearJoined+0001
I want this as a the PK as well.I tried a default value Year(Now())&format([ID],"0000") but it gives me errors.
How to get this expression to work? It works by changing the numbers to correct date format, however, if the field is null, I want a blank to appear instead of "type"
=IIf([DATE_APPLIED_X] Is Null,"""",CDate(Mid([DATE_APPLIED_X],5,2) & "/" & Right([DATE_APPLIED_X],2) & "/" & Left([DATE_APPLIED_X],4)))
Report.JPG
I have the auto ID number set up set up on my Access database which gives me membership numbers. How do I have a supplementary auto numbering column to give me invoice numbers. You used to be able to do this automatically up until a few years ago. Now I have to enter them manually.
View 8 Replies View Relatedselect salary, pct
from tally
I want salary to be in this format: $3211.00
pct in 23.00%
How can I do that?
Thanks
i have 1 table that link to excel file. the excel file is generate by other software and it's on excel 2.1 format. I've tried to directly link to the file but not working. so i manually open the file and save the file on excel 2002 format, then i use that file to be linked on my mdb. Unfortunately every time i replace the file with the newest data, my mdb not displaying the updated data.
- how can i link old version of excel on mdb?
- what should i do so my mdb will display the updated data after the excel file is replaced?
thanks
I have a query that I want the Now() function in the year only format and in an expression combine with the auto number one extra field to get the following:date YYYY-------Autonumber---------color----------exp1: ---2007--------------23--------------green-------2007-23-greenThanks for the help
View 1 Replies View Related