Making All Numbers Certain Size?
Nov 26, 2012
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
0100002
1111110
5234000
9788881
View Replies
ADVERTISEMENT
Jan 26, 2014
I have a subform that shows notes entered but if there is more text in the field then you cant see it all. Can I make it so that this field will grow so the whole input is visible or can I make it so when you click on it it opens in a larger window showing all the txt?
View 1 Replies
View Related
Nov 17, 2014
I made a report that contains 10 textboxes beside each other and i changed the option (can grow) of the textboxes to yes the growing is okay, but i want all the textboxes to grow the same size as the bigger one.
View 5 Replies
View Related
Feb 4, 2006
I have a query where I have some negative numbers (f.e. -403,26) which I like to make a positive (i.e. 403,26).
How do I do this??
View 3 Replies
View Related
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!
Any help much appreciated!
View 4 Replies
View Related
Aug 29, 2005
Will keeping your field size shorter result in a smaller MDB file?
Or does Access only use as much space as there is real data in its fields.
Way back in the dBASE III days, dBASE would pad all your "real" information with as many spaces as necessary to fill up your field. I suspect that the MDB structure is probably smarter than that.
Another question on the same topic - I believe there is a maximum number of characters in a record (4000?). Can your field sizes add up to more than 4000, as long as the actual data, all combined, never totals 4000...?
Thanks............
..dc
View 3 Replies
View Related
Mar 28, 2005
;) Hey everybody,
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...
Thanks
gunwax
View 9 Replies
View Related
Aug 12, 2014
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.
View 14 Replies
View Related
Jan 10, 2007
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?
View 1 Replies
View Related
Aug 24, 2014
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.
Example
1 1
2 2
3 3
2 1
2 2
2 3
View 5 Replies
View Related
Oct 28, 2014
I have a table with fields like this one but the weeks go all the way up to 52. What I am trying to do is count the number of consecutive zeros and if it is more than five, count how many of the following fields have a number in them and if that number is less than the number of zeros preceding it identify that person.
For example Joe would be identified below because he had 6 consecutive zeros and then he had 5 weeks of numbers immediately following the string of zeros. Bob would not be identified because he had 5 consecutive zeros and then 5 sets of numbers immediately following the string of zeros so the zero frequency isnt higher than the number frequency immediately following.
ID
Name
Date of Hire
1
2
3
4
5
[code]...
View 5 Replies
View Related
Jan 25, 2005
On my database I have a text box that accepts a map grid that starts with two letters. If the letters arnt within certain perameters then the box turns red and a message box appears informing the user of this. My probem is that I am using a continuous form and if one box goes red, they all go red if the grids are OK. my code is below, can anyone help?
Private Sub Grid_BeforeUpdate(Cancel As Integer)
If IsNull(Me.Grid) Then Exit Sub
If Me.Text289 = "UK" Then
Select Case Left(Me.Grid, 2)
Case "ST", "SY", "SU", "SZ"
Me.Grid.BackColor = 16777215
Case Else
MsgBox "Invalid Biagram for your chosen Database.", vbExclamation
Me.Grid.BackColor = 225
Cancel = True
End Select
End If
If Me.Text289 = "Germany" Then
Select Case Left(Me.Grid, 2)
Case "MB", "NB", "NC", "MC"
Me.Grid.BackColor = 16777215
Case Else
MsgBox "Invalid Biagram for your chosen Database.", vbExclamation
Me.Grid.BackColor = 225
Cancel = True
End Select
End If
If Me.Text289 = "Desert" Then
Select Case Left(Me.Grid, 2)
Case "NJ", "NH", "NK"
Me.Grid.BackColor = 16777215
Case Else
MsgBox "Invalid Biagram for your chosen Database.", vbExclamation
Me.Grid.BackColor = 225
Cancel = True
End Select
End If
End Sub
View 1 Replies
View Related
Sep 20, 2005
i developed a program and i used ms access for the database.
i compiled the program i made.
the question is, can i install the program i made into another computer eventhough it doesn't have a ms access? because i tried to install but it doesn't work at all. what do i need to do in order to make it work.
your response is greatly appreciated.
View 3 Replies
View Related
Apr 18, 2006
I am not sure where to post this so could someone please move it to the appropriate area thanks.
I want to make a graph that will show how many employees are in the company for each year (Going back 10 years and keeping up to date) I have never done this before and our teacher feels that its best if we learn on our own (basicaly he's an idiot).
How do I go about using the pivot chart?
I have made a query which adds up the total number of employees at the current time.. but apart from that I havent a clue. I want the graph to have the years on the bottom and numbers up the side (1,2,3,4,5 etc) and then the number of employees that were in the company during that year will be plotted.
I will use a table that is to be used for archiving to mke the query as it will have all the employees who were ever in the system record.
The table is thus:
Employee_ID
Emp_First_Name
Emp_Surname
Age
Emp_Home_Number
Emp_Mobile_Number
Emp_Address_1
(so on and including postcode)
Date_Started
Position_ID
I have no idea what to do other than to rename the axes and change the increments, but they are jsut numbers, no dates or anything.
Any help apreciated.
View 6 Replies
View Related
Dec 11, 2006
Does anybody know if its possible to export data from Access into a "filename.LEX" file for use in MS Word?
If so - Do you have any idea what the fieldnames/file structure looks like.
I have tried oipening a .LEX file to check this out, but with no success.
Thanks
View 4 Replies
View Related
Nov 15, 2007
Hi guys,
I am having error message when I tried to make my mdb file to mde. The error message is "MS Access unable to create an MDE database". Reading through the help message it says that this problem always occurrs in trying to compile large db into MDE. So what can I do to solve this problem. Also what is MS access memory capacity. thanks
View 6 Replies
View Related
Apr 14, 2008
:mad:Is there anyway, I can tell who is making my backups. Is there any thing I can use to find out who is creating a backup. I have sent out emails to the users, but no one will confess who is doing yet. I have 200 users now. I have talk to our IT people, they mention it would take a lot of there time to track it down.
Does anyone know if there anything I can do?
Thanks
View 14 Replies
View Related
Jan 3, 2008
Hi!
I have a table called "products"
One column is "Unitary Price", another one is "Quantity" and another one is "Tax"
And I want another column called "Total" that shows:
Unitary Price*Quantity+Tax*Price*Quantity
Is this possible to be made?? :( I'm a bit newbie to MS Access, so try to explain as most detailed as you can! For example, if I have to write [Unitary Price] between brackets... I tried doing this on the "Default Value" field of the column but I couldn't make it.
Thank you!!
Luis
View 9 Replies
View Related
Sep 12, 2005
I'm trying to make totals in a query, but instead of using all, I need to leave one out, how to do that? for example the table looks like:
A 2 3
B 4 6
C 2 5
D 3 5
E 2 3
How can I leave row 1 out? So that the query will look like:
11 19
View 2 Replies
View Related
May 17, 2005
This is probably a stupid question but could you please tell me how i can set up a form in access to look similar to the one in the attached picture?
View 4 Replies
View Related
Oct 12, 2005
:confused:
Hello I have a small DataBase with 4 forms (Access 2002)
1. frmMainMenu
2. frmAddEmployee
3. frmAddEmployeeRegion
4. frmEmployeeStatistic
The MainMenu calls the other 3, when I execute the mde file the only form opened is the frmAddEmployee! it should open the frmMainMenu because this one calls the others....! I've tried several times....the same result!
Any idea what I'm doing wrong?
Thank you
View 4 Replies
View Related
Nov 17, 2004
hi,
i'm trying to create a form for data input. When creating a new record, I want to make a field, "Date," to read from the last record and use that value as the default value. This way, I don't have to input the same date in every time I make a new record. How would I do this? Thanks =)
G
View 3 Replies
View Related
Jan 17, 2007
How can I make an OLE come up in the report as a photo or drawing instead of a link?
Can anyone help?
View 2 Replies
View Related
Jun 20, 2007
I need two reports made but I don't know the programming. The first report comes from a table that has contact numbers and their scan times. Their number comes from one column and their times come from another column in a table that i have. the time comes in this format 6/11/2007 3:46:40 AM. One report I need is those who just scaned in for that certain day. The second report is one to take their times and calculate how long they were scanned in per day, and per week. Is there any such way to do this and how would I.
View 12 Replies
View Related
Aug 21, 2007
Good evening,
I am trying to use VBA to address a field in three different subforms on a form. When I am in the VBA environment writing the code, I noticed that only two of the subforms are listed in the Project window on the upper left. I ignored that and wrote the code anyway, and when I execute the code, it works fine for the two subforms in the Project window, but when it gets to the third subform, it throws this error:
"MS Office Access can't find the field '|' referred to in your expression."
The code is this:
Private Sub Test_Number_AfterUpdate()
[Form_Consumables Entry].Test_Number.DefaultValue = [Form_Test Director Panel].Test_Number.Value
[Form_Squawk Entry].Test_Number.DefaultValue = [Form_Test Director Panel].Test_Number.Value
[Form_Event Log Entry].Test_Number.Value = [Form_Test Director Panel].Test_Number.Value
End Sub
Can anyone tell me how to make all of my forms available to VBA?
Thanks!
Erik
View 4 Replies
View Related
Sep 18, 2006
Hi,
What should be done when the size of a database keeps getting bigger and bigger?
Regards,
B
View 14 Replies
View Related