Converting Alphanumeric Characters (and Spaces) To Numbers
Jun 21, 2007
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?
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 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'm working on an eBay uploader access program for Ebay's File Manager
Our description contains line feeds and carriage returns. I have very little knowledge of Vba, and I can t find any tutorials on how to replace the characters with spaces on how to program this.
The table is called tblCustomer The field is called *Description
Hi I have big problem and i have to find solution within one hour
I have numbers like this "space"numbers"space"numbers"
I would like to cut the spaces I know there is trim function but i'm pretty beginner, i read help but i can't still use it and trim will work only on first space? I'd be very thankful for giving me some practical example or explane?
I have to create a report to figure out the amount of time that is spent running the printers. I got the time figured out for the time that the operator is running a job. I also have figured out how to total up the time that the printer is down. When I went to figure out the down time I realized that the down time is in numerical format. I can't change it because it is used that way in other reports. How can I change the numbers to time? Do I do that in the properties window for that field? Both the field for the time that the operator is running a job and the total time that the printer is down are created in the query.
I have a short text field where numbers such as "15.00", "2233.56", "-300.00" are stored. Now I want to convert the text field to numeric so that I have actually 15.00 or -300.00 stored as a double. I am going to do calculations on these fields. How can I achieve this conversion?
I have a query to create in access based off a query already created in SQL
The SQL query converts a 4 digit number into a date using this code:
CAST(CASE WHEN LEFT(OPDT, 2) > 12 OR LEFT(OPDT, 2) = 00 THEN RIGHT(OPDT, 2) ELSE LEFT(OPDT, 2) END + '/' + '01' + '/' + CASE WHEN LEFT(OPDT, 2) > 12 OR LEFT(OPDT, 2) = 00 THEN LEFT(OPDT, 2) ELSE RIGHT(OPDT, 2) END AS SMALLDATETIME)
OPDT is a digit number in text format. The function converts 9606 into 1996-06-01
I am trying to write some VBA to convert a date into a week number.
My work colleagues do not not what system is used to define the week numbers. So I have gone through all their records for a few years and deciphered this pattern:
I need the code to define that there are 52 weeks in a year. The last sunday of Dec is the beginning of Week 1 for the following year. Except when it is a leap year where Week 53 exists. otherwise it only goes up to week 52.
Start day for the week is Sunday. End day is the Saturday.
Some examples to check against:
22nd December 2013 = Week 52 2013
29th December 2013 = Week 1 for 2014 ----
21st December 2014 = Week 52 for 2014
28th December 2014 = Week 1 for 2015 ----
20th December 2015 = Week 52 for 2015
27th December 2015 = Week 1 for 2016 ----
25th December 2016 = Week 53 for 2016
1st Jan 2017 = Week 1 for 2017 -----
Tried various methods already
iso 8601, wrong week start day
intWeek = DatePart("ww", datDate, vbSunday, vbFirstFourDays) Was right for 2013 but wrong for later dates.
In my transaction table, I would like to find out the latest transaction dates of each family models. It sounds a simple Max function can get the desired result. However model number consists of the first 6 characters for the family model and the rest for the versions (variants). The Max function fails to work in this scenario. Is there any other way to group the model numbers once the 1st 6 characters are identical (the rest is ignored)?
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'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.
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
