I want to know if there's a way to sort alphanumeric fields where the number of digits aren't always the same.
Example: I have the following: 2a, 10a, 3a.
Currently, it sorts: 10a, 2a, 3a.
Is there a way to get it to sort 2a, 3a, 10a without adding a 0 before 2a and 3a?
I'm just to work with Fox Pro, and I am therefore puzzled with how to define the exact number of digits in a field in a table in Access. If I need 9 digits + 2 decimals in a number filed, how do I define that in design view?
Also, I need to know how to export a table into a text file, with a format without any spaces, and each record is divided with a new line. This an old IBM text format file.
Thanks, Torsan
I have a table having the following fields:
StaffNo TextField PrimaryKey
Name
....
...
The data in StaffNo will be alpha numeric, like AKA-111, AKA-112, LMN-100, LMN-102
Here AKA and LMN describes the Sites where employee is working.
On Add New Employee, When user enter AKA- in StaffNo, on exit the next number on that site should be generated. i.e AKA-113.
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.
Flat A1
Flat A10
Room A13
Room A2
Room A21
B5
Flat 5b
10k1
10 k3
10 k12
10 k20
10 k2
etc
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?
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
Open the database – then open the Report: rep_Billing
I have a query that is doing some sort and count functions. The column [TYPE] is sorted then counted. Instead of sorting by the full name can the query sort based off of the first 2 characters?
Example:
On the report the first company is: AMERICAN POSTCARDS
First it separates the colors, which is good.
Then separates the [TYPE] then counts the total amount that color was that type.
However I do not want the full type to be separated out, I want it based on the first 2 characters so that the “N-“ will all stay together as a group.
Final count should say: AMERICAN POSTCARDS
GREEN RA 3
RED N- 14
RED PR 16
RED RA 12
BLUE NC 1
I know I can get this info from the report, but it has to be done at the query level because it is going to be exported into another system at that point. Thanks.
Let me know if I need to make this clearer. Thank you all.
Travis
Dear All,
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.
Thanks,
Access 2013.
Table field is set to: Number/Byte, 0 decimals.
The field is used to sort records in a particular order.
In the form it is set to Standard, no Decimals and no Default Value.
When I create a new record and type in: 1, it makes it 10, 2 becomes 20 etc.
If I delete the value and type it in again, it does not add the 0.
It only does it in the form, not when I enter it in the table directly.
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.
View 9 Replies View Relatedhave a query with a field that contains numbers with 3,4,and 5 digits. I want to format it so it displays all as 5 digits by adding 0s to the front of the number. EX:
324
4856
45634
change to:
00324
-4856
45634
Help please. Dont want to do it in VBA either. I would rather build it as an expression field or something.
I have a form I created in the past that auto-fills some of its fields based on the user's selection of a record from a combo box linked to another table. The form has been working perfectly however I recently tried to add a couple of more fields to the form that need to be autofilled. I added the new fields to the source table and added columns to the combobox that should allow my VB code to fill those boxes. However only one of the new fields is filling correctly. After trying a few different things I notice that the problem seems to be with whatever data field becomes the 21st column in the combobox. Is there some sort of limit on the number of columns contained in the data source for such a box?
Option Compare Database
Private Sub EmpIDAutofill_Change()
Me.EmployerCatAutofill.Value = Me.EmpIDAutofill.Column(1)
Me.StreetNumAutofill.Value = Me.EmpIDAutofill.Column(2)
Me.JobCityAutofill.Value = Me.EmpIDAutofill.Column(3)
[code]...
dear frnd,
i have a access program for printing cheque. i want to use a text box which automaticaly convert number ito digits from user input text box.
I need a field to permit only numbers. Upon selecting number for the data type I select Long Integer for a longer number... But it only allows 9 digits.. I need it to allow to enter in longer numbers... Any help?
Thank you very much in avdance
Hi there
Im working on an update query to add 2 zeros to the front of a field where it is less than 8 digits long. I'm not sure how to go about this, can anyone help me out?
Cheers
Hi guys,
I have a text box, which requires a 8-digit number entry. I have wrote the code to check that the length is 8 and if it isn't a label will turn visible next to the field telling the user entry must be 8 digits.
This works for when i enter a number less than 8-digits but when i input a number with more digits as it does not fit into the text box it turns it to a scientific format value and when the code runs it shows the length is actually 8! And therefore the entry is valid!
I.e. when i input 123456789 the text box shows 1.23E+08 which is 8 charecters!
I dont want any error messages to pop up as i handle them by code and use labels to show the error message next to the problem field and i would like to keep this consistent. Is there any way i can do this by a function or a property in the table or form?
I appreciate any help/advice
cheers
hello,
simple problem. how would i grab just the last six digits of a social security number?
example: 123-45-6789 to 456789
I'm guessing that I should use the Format function, but I'm not quite sure how to write it. Thanks!
G
I have a form I would like to sort by:
first - by App_Flag (a yes/no field) - have all the clients with "yes" on top
second - by App_Date - from the earliest to the latest
The query sorts it fine but the form does not - even if I requery the form (added a requery button).
Can a macro or coding make it sort it the way I need it?
I have a report to print that only shows the last 4 digits of the Social Security field. How can I do so for the entire list of social security #'s.
Thanks in advance.:cool:
I have a form with an unbound textbox. I want to be able to enter a sequence of digits and spaces (e.g., 02 950 4187); however, when I paste this into the box, I always have to delete the two spaces until the number is 9 digits long. Then it will accept the number and go look for the prize. How do I get the textbox to accept the number I paste in?
View 5 Replies View RelatedThe field SECL DDI has the users phone number unfortunately over time these have been entered in different formats so there are 5 digits, 6 digits, 7 digits etc...Can I run a query that counts the number of digits in each filed and then tell me how many of each exist
Ie
4 digits 3412
5 digits 5000
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.
I have an Access table with records that have a two letter, two number alphanumeric identifier (AA01, AA02 etc)
I am trying to create a form whereby, when the user types in the first two letters, the next sequential number from the table is generated.
The letters can be totally random and selected by the user, but the numbers do need to be sequential.
I would like to enter a couple of alphanumeric groups into a field on an input form. After I enter an alphanumeric group, I hit the enter and the data will add into the field and refresh to empty box ready for next entry. If I continue to enter another group and hit enter, the next group will be added to original field with a comma and a space in between. build the VBA in after update event to accomplish the task.
View 14 Replies View RelatedI am trying to hide the first 6 digits in a textbox and only show the last for digits in a text box.
So far I a putting my code in the Format Format section in Property Sheet of a form. Its not seeming to work.
Code:
Left([Card Number],Len([Card Number])-6)
I have a form where data can be added and it has different tabs. The 3rd tab has idVersion field and Comment field where users can add the version and comment, if needed. When they need to add another version, the blank fields will be added as next row, so on and so forth. The problem is users are adding a lot of versions but not sorted. So for example:
Row 1 of the form: idVersion field is "1" and comment has "Test 1".
Row 2 of the form: idVersion field is "8" and comment has "Test 8".
Row 3 of the form: idVersion field is "2" and comment has "Test 2".
Row 2 of the form: idVersion field is "5" and comment has "Test 5".
So how can I fix it where after they saved the version and comment, when they close the database and re-open it again, the data will show up as:
Row 1 of the form: idVersion field is "1" and comment has "Test 1".
Row 2 of the form: idVersion field is "2" and comment has "Test 2".
Row 3 of the form: idVersion field is "5" and comment has "Test 5".
Row 2 of the form: idVersion field is "8" and comment has "Test 8".
So even if they add a new version and comment (example version "3"), it will be automatically sorted when the database is reopen.