I want a field to be a serial number made up of a letter of the alphabet followed by 3 digits. I want the digits to increase by 1 in each of the following record. For Example the serial number in the first record would be A001, then followed by A002, A003, ....., A142, A143, A144 & so on. I am fairly new at Access but I have experimented with AutoNumber, it won't work if the Field is not a pure mumber, can't get autonumbering to happen if I treat the field as text. I don't know where to go from here.
I've searched through the forum looking for what i want to do with no luck so hopefully one of you kind people could help.
I'm setting up a DB that logs in a batch of parts to which i generate a batch no. As the batch progressess through the works when a problem arises we generate fault records (I've set up another table for this and linked the batch no fields together). I want to put a serial no. on this form consisting of 'year' 'batch No.' and ?'Auto number'? From reading posts is it best to put the 3 items in seperate fields (less hassle)
My 2 questions are
1, how do i extract just the year from a date field?
2, This is the main problem. The auto number. If i use auto number to generate a unique number to log the record the number will be massive in no time at all. This is fine as the unique identifier for the record in a table but not for the serial number of the printed report. Idealy I want to have the batch number then a serial number that resets to 1 on a different batch No. For example
21098/1 21098/2 Batch No./serial No. 21098/3
22309/1 22309/2 22309/3
Just some more info this database is a multi user database split front and rear.
I hope i've explained this well enough, many thanks for your help in advance
In working with a database, I need to convert 39,000 text fields (i.e.: 6002.2 , 2723 , 6004.55 (Notice the spaces)(commas denote next line)) to number fields. The number of spaces is random, so I can't say "cut off the last n number of characters." There may also be characters that I'm not seeing. I'm not too keen on going through it by hand, and I have access to OpenOffice.org Calc (for those who don't know, it's basically a free version of Excel) so I can pull it into there and convert it, but it still won't get rid of the spaces though. Any ideas?
Hi, I have a table that uses SerialNo as primary key. From time to time (its a long story...) I need to go in and update the SerialNo by adding x to it. Here's the problem: lets say the table has SerialNo's 1,2,3,4,5, and I try to add 1. I get 4 KV error's because of course, 2,3,4,5 already exist in the table. (So I would end up with 1,2,3,4,6 instead of 2,3,4,5,6.)
I really thought that Access should be smart enough to figure out that there really won't be any key violations after the updates are done, but I guess not.
I would like to produce a serial which will have 4 members.The first, the second and the fourth members will be always CAPITAL Letters and the thirth one is always NUMBER.Here is an example for the format: AC3H
Each member will change in any line and never match... I would like to produce it by a button or a button for each one.The last important point for this issue is; I would llike to identfy the total number of serials.
I have a table that has a field for SerialNumberStart and QtyRequired, from those two I have a calculated field for the SerialNumberEnd.
I want to print a report from this table that if for example I have StartSerialNumber 34 and SerialNumberEnd 40 prints one report for each serial number.
Access does not have an option to sort alphanumeric strings properly with the result that sorting a column with the following (sort of) data is almost impossible.
I need to be able to get addresses sorted correctly and they always have a mixture of alphabetic and numeric characters.
The steps I have followed to try and achieve this are as follows:
Create a matchfield containing the data for sorting (typically the street number followed by the flat number/name)
Split the matchfield into separate fields where there are spaces. "Room A13" becomes "Room", "A13"
Then split the fields/columns by separating the alphabetic and numeric portions - i.e. "Room A13" becomes "Room", "A", "13". This I have not been able to achieve successfully.
(Thinking about it the first step of separating by spaces is probably not necessary. All that is needed is to separate the numeric and alphabetic data)).
Once you have separated the data into alaphbetic and numeric content a sort is straightforward.
Why can't Access cope with what I would regard as a fairly basic requirement (i.e. to be able to sort alphanumeric strings correctly).
Does anyone have a suggestion how I can solve this problem until Access is improved?
I know that I could have done a better effort searching the forums and other places but you'd think that the answer to this would be easy: how do you set a Validation Rule for a field where a person can only enter an alphanumeric value?
I'm setting up an invoice tracking database and one of the recurring problems end users have is the inconsistent entering of an invoice number with symbols. We want to eliminate this and have them enter ONLY alphanumeric values. In other systems I've used, this has been easy to setup. But not in Access. Can someone advise? I've been trying to deal with this for two days.
I know that there may have been many posts on this and I have searched for a total of about 2 hours in these forums before posting, so please don't shoot me down for asking this.
I simply need to know how I can create a unique, alphanumeric order number. I was going to use Autonumber, but from what I have read on these forums, that is not the way to go, as Autonumber is simply to identify the record.
What I need is an number like so: ST000. It needs to start at ST142 and increase by one each time. Obviously I would like to avoid gaps from records being deleted or not completed etc.....
I really have no clue how to do this, and would appreciate it if anyone can point me to a post that would help, or offer me the solution.
I have a text field in a table that contains an alphanumeric code. i.e.
DEL998 DEL999 DEL1000 DEL1001 SUN998 SUN999 SUN1000 SUN1001 SUN1002 etc.
I want to run a query to find the highest number for a particular alpha code. In the example for DEL I would want the query to return DEL1001.
I have created a select query that asks for the alpha code, selects all codes starting with that code, sorts them in decending order and only displays the first record.
The problem is that because the field is a text field the numeric is not sorted like a number. So in the DEL case the query returns DEL999.
I have a list of street numbers, eg 1, 4, 8, 33, 44, and also some that contain some alphanumeric characters: eg: 21A, 21B, 21C.
I need to find out how to determine if a string has an alphanumeric character or not. I have used the access help but it is very vague and also Ive googled. One answer I found was to write a module, but Im not sure if thats the best way.
I'm learning as I go and this is probably a basic question but if I have an alpha numeric field of variable lenght, i.e. AUI856Z....how to I format it so that it is 19 characters long with leading zeros, i.e. 000000000000AUI856Z.:confused:
I have a database I have worked on for the sister company of the place I am employed.I have a field I am trying to make alphanumeric that has been numeric. (PO Number on the main form). I had make it alphanumeric a couple of months ago, but it disabled the Edit Customer Information button on the bottom right of the form to where I cant enter shipping addresses and things like that. It should be able to enter multiple shipping addresses.
I need making the PO Number field button alphanumeric and making sure it doesnt mess-up the Edit Customer Information button. I have attached both versions of my database. The GM at the sister company would also like me to create a Spin button where you can take an old record and keep all of the previous information on it, except it gives it a new Work Order# and you can change the date to something newer.
I have searched this forum but didn't find the answer, I hope I'm not the only one with this problem. I have two text fields in a table that are updated through an excel file import. In the excel file, both fields contain data that is mostly numeric, but there are always about a quarter that contain letters as well. When I go to import the excel file, it sets to null any value in the fields that contains letters. If I sort the excel file in descending order for the field, it will import, but I have two fields that do this, so this brings more problems. Does anyone know why you can't just import anything in any order into a text field?
hi i found this code here and it works IF the number comes first and is preceded by a letter 123AA
but it does not work if the letters come first AA123
here's the code
Public Function GetString(WholeString As String) As String Dim i As Integer Dim Temp As String Temp = CStr(WholeString) For i = 1 To Len(WholeString) If InStr(1, "0123456789.", Mid(Temp, i, 1)) = 0 Then GetString = Mid(Temp, i) Exit Function End If Next i GetString = Temp End Function
Public Function GetNumber(WholeString As String) As Double Dim Temp As String Dim i As Integer Temp = CStr(WholeString) For i = 1 To Len(Temp) If InStr(1, "0123456789.", Mid(Temp, i, 1)) = 0 Then GetNumber = Mid(Temp, 1, i - 1) Exit Function End If Next i GetNumber = Temp End Function
the probelm is with this line but i'm not sure what it is
GetNumber = Mid(Temp, 1, i - 1)
(i also get runtime error 13) but my data is in the same format as the example i downloaded.
any ideas anyone?
thanks in advance and thanks to the person who created the code
I've got most of what he wants sorted but this last task I am completely flummoxed. All of his projects are allocated an ID (named Project Reference), starting from P010010 and increasing by 1 each time. I've made a form that allows a new project to be recorded by entering all the details and hitting the 'record' button, but he wants the Project Reference field to be automatically filled in each time (understandably), increasing by 1 from the last record.
So if the last record was P010311, then when the form opens the Project Reference should automatically be P010312.
I've looked into this and found many guides talking about DMax and DIM and strCriteria and whatnot, but no matter how many of them I follow and try to adapt to my own database I can't get it to work at all.
The table the ID comes from is called General, and the field is Project Reference. The ID should automatically be filled into a text box called txtRef whenever the form opens and a button to add a new record is pressed, being 1 higher than the previous ID.
Is any body know how to retrieve the manufacturer's hard disk serial No.? I want to use it in my access application to avoid piracy. At present I am useing a code which gives me Volume serial No., which got changed when ever my user format his hard drive. Instead of HDD firmware serial, code for retrieval of any other permanent No. like mother board or CPU firmware serial can help me to solve my problem
I have the following code for the text field (Before Update)
If IsNull(Me![DyNo]) Then Me![DyNo] = Format(Nz(DMax("[DyNo]", "[tblAllDet]", "[TheYear]='" & Year(Date) & "'"), 0) + 1, "00000") End If Me![DyNo] = Format([DyNo], "00000")
The code starts giving numbers from 00001, 00002, 00003 and so on. The problem was that when I have to search, I have to type the zeros before the number i.e. 00007, 00008 etc. otherwise the search form doesn’t show the relevant record. Ideally, I would like the numbering system to be 1, 2, 3, etc. (without the zeros). I tried changing the code to “0” but with the single “0”, I am unable to insert records beyond No.10. Is there a way to change this code so that I get only 1, 2, 3 etc and not with preceding zeros ? Grateful for help.
Hi, I have been trying to make a datbase so I can input the serial number of an electronic device (cell phone boards) , and also would like to be able to add additional info about the device. Most importantly I have to be able to "search" for the device by typing the serial number in a search box.
So lets say I have serial number 11111, 11112, 11113, 11114 listed in the database as "intermittent power failures", before I use the device I can type the serial number in the database and be able to recall the details. So if I type in 11119 and no hits come back, I know that one is not in the system yet; meaning it ethere does not have a problem, or the problem has not been discovered yet. If I type in 111111 for example, it would come back as a hit, saying it has an "intermittent power failure" problem. I have spent about 4 hours messing with access, I used it a while back in office 03, however I have never did anything like that, and to make things further complicated we now only have office 07. Does anyone have a template which is similar (I can modify the values) or know how to assist me to be able to create a database like this?
I'm in the process of streamlining the process to maintain our various access reports and queries.
My company's accounting software stores all record dates in Gregorian Serial format (don't ask me why). Currently for our reports we have a local hard-coded table that is manually updated periodically with dates in MM/DD/YYYY format and the coresponding Gregorian serial date number. To go from date to serial is a fairly straightforward access function of:
(Datediff('d','1/1/2000',[Date to convert])+730120)
For example if [Date to convert] was 2/7/2006 the output would be 732349. That's all well and good, however I am in need of a query I can build that will on the fly generate a list from say 1/1/1997 through 1 year after day being run just to be safe with two columns, one with date and one with the serial date equivelant so I can link to the accounting tables for date restrictions.
This way I just have to have the equation in one query instead of a hard coded table or do the calculation in every form or query that needs to look up these tables.
Unfortuantely I've been staring at accounting data (and am not an accountant) way too long and my brain is fried, suggestions?
How to write an expression/criteria in a query that will return only ID numbers with alpha numeric values. Example: My Id field contains both numeric 479621, 680530 and alpha numeric 132NAV100, 174NAV26a values. I want to run the query to return only the alpha numeric values.
I am trying to use access to connect to SQLServer with an insert query. I need to generate alphanumeric number for each record. The letter will move to the right as the alphanumeric number increase.
For example: A0001 , A0002.... -> A9999 0A000, 0A001 ... => 0A999 1A000, 1A001 ... => 1A999 2A000, 2A001 ...2A999 9A000,9A001 ...=>9A999 00A00, 00A01 ... => 99A99 000A0, 000A1 ... > 999A9 0000A....=> 9999A Last number will be 9999A
I have an existing form where users type in information and it generates a couple of reports. In one of the fields, Customer PO Number, the user enters a number from a customer. Up until yesterday all of the customers we have been dealing with have used numbers only for their PO numbers. However, we have a new customer that requires alphanumeric PO's. Is there a simple way to change this field from a number to alphanumeric without having to redo each form, report and/or query. I am using MS Access 2010.
