Making All Numbers The Same Length, By Adding Zeros
May 4, 2006
Hello all,
I have a table, with a whole series of reference numbers in, which I have moved over from an old database, and am trying to integrate the data into my new database.
The numbers, in order to work with my new db need to be 6 characters long (as they are looked up in a sql-stored table that another application uses), otherwise it won't match up the clients to the reference numbers properly.
Anyway, some of these numbers are 6 digits anyway, such as 123456, but some are just 6543....I need 6543 to be changed to 006543, thereby creating a 6 digit number.
This, I am sure, is pretty simple, and only needs to be done the once - but I can't think of a way of doing it!
I hope someone can help with this one. After many years of using Access for ad-hoc data conversion this has beaten me. I need to produce an ascii text file with fixed column widths, separated by commas, strange I know but the customer is always right. As it is fixed width I have inserted the commas by using a separate column for each one. Numeric columns need to be left padded with zeros. I have constructed a query to do all the column selection and reformatting into a new table which I then export using a fixed length export file spec. Everything works fine except for 3 columns which are calculated by subtracting one column from another. I can get the data to look fine in the output table, the datatype is text, but when I export the table the leading zeros are stripped. This is my expression: String(9-Len(FormatNumber([FULL_FARE_EQUIV]-[TAX_EQUIV],2,0,0,0)),"0") & FormatNumber([FULL_FARE_EQUIV]-[TAX_EQUIV],2,0,0,0). The result in the table is exactly what I want: 000200.00 but when I export it I get a left adjusted 200.00. I've tried using format with a "000000.00" mask which gives the same results. I've tried removing the preceding comma column and including the comma as a prefix using the format mask ",000000.00" and also by concatenation. This looks fine in the table column ,000200.00 but I get an error when I export the table which blanks the column. Error attached.
I know this is probably a stupid question and I'm overlooking something obvious, but... I have a Long Integer field that has an input mask requirement of 7 numbers. Sometimes the number starts with a 0, but instead of showing as 0468165, it shows up as 468165. I want the 0 to show at the beginning. How can I do this? Thanks!
I have to add the row data in two columns together. I used
val([COLUMN 1])+val([COLUMN 2]) and it works unless one of the columns has a zero in it. For example if column 1 is 25 and column 2 is NULL it runs the query but puts a #ERROR in those rows. Please help.
Every month, I receive an excel file. Within the excel file, column A, is populated full of numbers. These numbers varies in length
For eg 000001 100002 1111110 5234000 29788881 31111111
Steps
1)What I want to do is import this file into access
2) if there are only 6 digits, add additional zeros on front of the number, so that max number of digits is 10 For eg using the sample data above
100002 is 6 digits long I want it to display as 000 0 100 002 1111110 is 7 digits long I want it to display as 000 1 111 110 5234000 is 7 digits long I want it to display as 000 5 234 000 29788881is 8 digits long I want it to display as 002 9 788 881
From this, I would like to do a right look up (preferably look up 7), to produce a standard length number
I have a table with a bunch of values for time spent on a variety of projects. Each persons time on a project is entered using a form. What I'd like is to then be able to add some of these times to get a total department time and a total project time in the table to help with our project costs estimating. Anyone have any ideas as there doesn't seem to be an easy way in the table itself to merely add columns. Thanks in Advance.
I have two combos with a cost price in Column(2) where the underlying table has a field called fldCost (Number, Single, Auto). After selecting from both combos, cboF1 has 3.11 in column2 and cboF2 has 10 in column2. (eg: £3.11 and £10.00). I'm trying to add the value from both combos in an unbound text box on the form set to Fixed, 2 decimals with the ControlSource =Nz([cboF1.Column(2)])+Nz([cboF2.Column(2)]).
The result I'm getting is 3.1110 and not 13.11 as it should be.
I've tried setting everything to currency but it makes no difference. I've tried individual unbound boxes to get one result at a time then another to add them but that gives the same result.
I work for a non profit agency, and we offer a Representative Payee service (paying bills for those who can't handle their own money). I've created a database that keeps track of everyone's finances, and gives us all the reports we need. The only thing I have not been able to do is to actually print the checks.
I have a report that pulls up the data (in check format ... including making $1.00 say one dollar and 00/100). But I'm stuck trying to give the checks a check number. I can't figure out how to pick a number to start with, and then add numbers sequentially.
I 've created a report that uses a query to show some specific records of a table. I'd like to use a text box that has a nuber for each filtered record shown.For example: Number Name Surname Age 1 Jim Powell 27 2 John Doe 30 etc Thank in advance
I have a database where on the form we are just put in total deductions. We want to make sure that the total deductions is correct so while we are entering this number we want to do an excel thing by hitting = 1 + 1. Anytime we do that we are getting an error message. I really didn't want to have to set up a table to store these different amounts because we really only need the total. Is there a way to do that? I changed the format from currency to general but I'm still getting the error.
I have a database for donation collection. So Receipt No's are already printered. I need to add these numbers in my database table with one click. Is it possible? There too much receipt I don't want to add every receipt my self.
show the total/sum of the input fields (unbound form).I tried sum function in control source for "Total working day", but I am getting #Error =sum ([working daysperiod1][workingdaysperiod2]...)
I used plus feature; like we do in Excel =[workingdaysperiod1]+[workingdaysperiod2]...... the answer I am getting 22192120.....
Period; Month; Working days 1 - Jan - 22 2 - Feb - 19 3 - Mar - 21 4 - Apr - 20
I'm working on a pretty serious report (at least for me)...I have 3 sub-reports inside, and the point of this is to use VBA and loop through several conditions (in the underlying queries), and print out in a batch for the user. When I add a page number footer it adds about 3 seconds to the processing time for a 14 page report. That doesn't sound like a whole lot, but when I loop through and process this 12 times, that's a lot of added time for the user...(the last thing I want to hear is it's slow!)
So, would it be faster if I set the page numbers in VBA? What event would I use? On Load?!? Also, how would I find the page number variables in VBA?
Basically what I have is a database for tracking/logging parcels that arrive to the office. I want to be able to generate a reference number based on the date of arrival: i.e. the reference number should be ddmmyy### where ### is a sequential number. I know that I could just use the primary key's autonumber, for the sequential number but if I do this then the sequence will not restart at 1 on each date and because we receive a lot of parcels the reference number will grow to be too big to print out on the collection slips in just a few months.
two tables (one with the date and staff on duty that day and the other with the parcel's info') with a one to many relationship
I also have a query (Named: FullLog) that picks up the following data from the tables:
The field named Count is a DCount function that I used to find out the number of times each date is repeated. This is the Expression that I used:
Count: DCount("*","FullLog","DateReceived = " & [DateReceived]) [Note that DateReceived is first converted into a string using CStr()]
This is as far as I have been able to get, I have been looking for weeks for a solution to this problem but I have yet to find one. I don't even know if the DCount function is the correct way of doing it, I did read somewhere that this produces a very slow query.
Effectively what I want to be able to get is something of that resembles the following
I am working on a database used in recording device characteristics/test information. The main table of information has dozens of columns for test/part detailed information. When inputing the data for each specific test, many of the info. details are repeated when testing say 20 devices of the same part all at once. Rather than retype every piece of detailed information in every field, everytime, is there an easier way? Does anyone know of a way to make specific fields copy/paste the previous record's information in the fields automatically when a new record is created? Please, if anyone could help or has ANY ideas, let me know...
I'm having multiple problems with my database like things such as -
i'm currently working on the Query 2 - On the Phone database (ignore Query 1) and i want to search for multiple plot numbers preferably in one parameter prompt with a comma to seperate numbers. (this could be a multitude of numbers so i would like to be able to input as many as needed). Also when i do search on this query since the Criteria is a 'Between' Value i would expect everything between the 2 numbers input to show up - but a lot of numbers out of the range show up too - why is this? (The Numbers are like "69 to 136" and they will show up - but 1-69 and 136-170 would too
I would also like to implement the search results from Query 2 into the Form i currently have made but it just opens up a access table when the search is made?
i cannot link my database as it is too big for the server - But here are the Criteria for Query 2:
Plot No - (criteria = Between [Enter First Plot No:] And [Enter Last Plot No:]) Site - (criteria = Like "*" & [Enter Site:] & "*") Product - (criteria = Like "*" & [Enter Product:] & "*"
The Query is the one im most concerned about , i can live without a form.
Ok so in excel I have some numbers that are stored as text. The reason being that they are zip codes and some begin with 0 and excel doesn't want numbers to start with 0....so when I import these into an access field that has an input mask for zip codes...will it convert these correctly since the field is a text with input mask?
I have 2 fields that I would like to automate if possible
One field is called "p/o number" and another field called "line no"
These fields are part of an ordering database
Let say I have 200 items to purchase form 10 suppliers
And form example 20 items from each supplier
What I do at present is put the order number on each line item and the line number
example
p/o number line no
1 1 1 2 1 3
2 1 2 2 2 3 2 4
What I want to do is just put the first po number in the required line . Put the first line number in i.e. "1" and the macro will complete all the p/o numbers and line numbers for me as per the ones marked in red.
This is probably an easy question by comparison to some of these others.
I have a table where a transaction number is always 8 digits. If the number is not 8 digits, it should start with zeros, such as
12345 should be 00012345
123456 should be 0123456
Is there a simple and fast way to do this? It can also be done in a form, since I enter the data through there. Will it be necessary to change the field from a number to a text data type?
I’ve got a six-character text field with values like: 354, 7237, 10438, all values under six-characters long. I need to run an update query to place sufficient “zeros” in front of each value to make it a six-character value. There are 8398 records to change! Got an idea? Please lemme know. Thanks.
