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

Forms :: Making A Field Grow In Size?

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

Reports :: Making All Text Box Grow The Same Size

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

Making Negative Numbers Positive

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

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!

Any help much appreciated!

View 4 Replies View Related

Large Field Size = Larger Overall Database Size?

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

Making Specific Fields In A Current Record Copy Themselves When Making New Record

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

Queries :: Search For Multiple Plot Numbers Preferably In One Parameter Prompt With Comma To Separate Numbers

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

Numbers Stored As Text Convert To Numbers?

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

Modules & VBA :: Automate Line Numbers And PO Numbers

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

Counting Frequency Of Numbers And Comparing To Frequency Of Another Set Of Numbers In Same Row

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

Making Me See Red!

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

Making An Exe

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

Making Graphs

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

Making A Dictionary

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

Error In Making MDe

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

Who Is Making My Backups

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

Making One Column The Sum Of Other Two

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

Making Totals Of All, Except For One

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

Help Making A Form...

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

Making A Mde File

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

Making Default Value From Last Value

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

Making An OLE Come Up In The Report

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

Making Reports

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

Making All Forms Available To VBA

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

DB Size

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







Copyrights 2005-15 www.BigResource.com, All rights reserved