Tables :: Splitting A Field That Contains A Digit

Jun 29, 2015

I am trying to split a field that contains a digit, so lets say the field contains the digit 7, I need it split into 7 fields. How I can do this?

View Replies


ADVERTISEMENT

Tables :: Splitting Attachment Field From Table

Jul 4, 2013

I have a database composed of personal statistics. (name, age, height, wt, etc). I have two attachment fields. Photos and Videos. Each of these fields can contain more that one file. The size of the video attachments is starting to get me up close to the 2 GB database limit. If each attachment field contained only one file, I would convert the fields over to a path link. I'm stumped on how to move the files out of the main database to control the size, but maintain the multi-file link to my forms. How to restructure this?

View 4 Replies View Related

Set Field To Specific Digit

Mar 25, 2005

Good day,

I need a textbox to a five digit number. How would I write code to reflect this.

Thanks.

View 1 Replies View Related

Tables :: Column To Autofill Based On First Digit Of The File Number

Dec 26, 2012

I have a datasheet with 7 columns. Two of the columns I'm working with are listed below.

File # Region #
2DE2-12345 2
3DE2-@@@ 3

In data sheet view I would like the Region # column to autofill based on the first digit of the file #. So, if file # entered = 3DE3-@@@@ then 3 would auto fill in Region Column. (data is entered in data sheet view)

I have used Left ([File #],1) to get value but cant make it auto populate the Region # column......

View 6 Replies View Related

Tables :: Splitting Database - Front End Does Not Link To All Tables In Back End

May 28, 2013

I am splitting a database and have created the Back end already. When I create the front end and link to the tables on the back end... The front end does not link to all the tables in the back end. The list that comes up when creating the linkings does not show all the tables in the back end. What would cause this?

View 1 Replies View Related

Queries :: Separate 6 Digit Data In Same Field

Jun 10, 2015

In same field Im saving every time 6 digits data and i need to separate them. (It was not my fault. They created DB like this... )

For example my data is

123412(main-field) and i want to see them 1234(field1) 12(field2)

View 2 Replies View Related

Tables :: Splitting One Table Into 3 Related Tables

Jul 16, 2014

I'm trying to split a table up because I now realize it won't be able to do what I need in the future. It wasn't designed properly at the outset, and I'm trying to correct it now.

This is a database of pregnancies and deliveries.

The single table does not cater well for multiple pregnancies (twins, triplets, etc), and also I foresee problems when mothers come back in future for another delivery.

Therefore I am trying to separate data into 3 tables: Mother, Delivery and Baby.

This is because each mother can deliver more than once, and each delivery can have more than one baby.

I have set up a trial database, with just a few fields in each table to see if this works. An screenshot of the table relationships is attached.

The primary key of the Mother table is linked to the Delivery table, and ditto the primary key of the Delivery table and the Baby table.

I'm not sure how to migrate the data over, in terms of the primary keys of each table, because in the new tables, these should be an AutoNumber field, so that they are unique numbers.

View 14 Replies View Related

Splitting Up Linked Tables

Nov 14, 2007

right i've been trying to think how to do this and i can't work it out... really shouldn't have got myself into the situation in the first place but i'm hoping its easier than i think so here goes

i have two tables TBL_Batches and TBL_Jobs, there are batches of jobs, many jobs to one batch so the tables are linked in a one to many relationship

there is a field in TBL_Jobs called job type which can be typeA or typeB and all the batches contain a mix of typeA and typeB jobs

the end result which i need to achieve is to have two entries in TBL_Batches for every one i have at the moment, one of them containing only typeA and the other only typeB... so where i have batch1 i should have batch1a and batch1b remove batch1 and all the foreign keys in TBL_Jobs changed so they link to the appropriate new batch in TBL_Batches

any ideas?

View 9 Replies View Related

Tables :: Losing Functionality On FE After Splitting

Mar 3, 2015

I've developing a little Project and have some issues after splitting a db.

I have some forms in wich I want to see some tables with the "+" sign on the rows so I can go to their linked tables. The problem that I have is that everything works perfect until I split the access file.

View 5 Replies View Related

Splitting Field - SQL?

Jan 30, 2008

Hello again!

Hoping to use SQL to do this in a query.

In a table I have linked to a text document (have to do it that way) there is a field that holds multiple values.

Meaning there can be some fields with nothing, some with a -, soem with a 3 digit number, some with a single digit followed by a space and then a 3 digit number, and lastly a 10 character text with spaces between each.

What i want to do in the query is when it looks at that field, create 2 fields out of it.

When its the 5 character value (the one that is like '2 345') split it, place the 2 in the first new field, and the 345 in the second new field. If the value in the originals field is not in that format, jsut carry over the value to the second new field.

does that make sense?? haha

thanks in advance!

View 2 Replies View Related

Splitting Values In A Field

Sep 28, 2005

I have a field - Period in the format YYYYMM.

How can I split this value so that I only extract YYYY?

Thanks

P

View 2 Replies View Related

Field Splitting...has Different Separators

Apr 11, 2008

I have been able to do this with a work around but it is slow and cumbersone when lots of records are involved and am hoping there is a "proper way" to edo it.

--(&100,,,, 200 ---() 300,400- (500),,,, --600

6 is the maximum number of alpha/number entities and down to ----john etc. Result to be

100 200 300 400 500 600

There can also be a mixture as in

--(&John,,,, 200 ---() Andrews,400- (500),,,, --600

John 200 Andrews 400 500 600

View 5 Replies View Related

Splitting Field Values

Aug 9, 2005

I have a db with a field that I need to split and put values into two other fields. The format of field1 is either a-b, aa-b, aa-bb, or a-bb. I want to take the value before the "-" and put it into field2 and then take the value after the "-" and put it into field3 and then delete field1. Is there a straightforward way to do this? I want to do it in the query design grid so let me know what should be in the "criteria" line versus the "update to" line.

I'm using Access 2000 and have approx 5500 records to convert.

Thanks!!

Tom

View 2 Replies View Related

Splitting A Field Into Many Rows

Feb 1, 2006

Hi all,

I have a problem this is part of my database

ID NAME DATARANGE ck1 ck2
1 dave 23-Jan-06,24-Jan-06,30-May-06 dataprojector s/table
2 John 23-Feb-06,24-Mar-06,30-Apr-06 s/table

What i would like to do is to run an maketable / updatetable query that splits out the daterange dates into seperate rows and also if there is a value in ck1 or ck2 replace the current value with either a 1 or 0.

So the above table would look like this.

ID NAME DATARANGE ck1 ck2
1 dave 23-Jan-06 1 1
2 dave 24-Jan-06 1 1
.
.
.
?? John 23-Feb-06 0 1
John 24-Mar-06 0 1

ETC

is this possible any help would be greatful

Thanks

Dave

View 3 Replies View Related

Splitting Records From 1 Field

Aug 24, 2004

Lo All,

i need to split records from a field
db - online
table - merk
field - merkCat

in the field i have - 1,2,3

now i send a string bla.asp?cat=1

i want to loop thru the db , but only show the records which actually have the 1 represented
in the merkCat field. Not all fields have that. So far i have selected the db and
i loop thru it , but somehow it wont split up

A little help by pointing me to some cool scrippy would be very helpful

thnx in advance.

View 2 Replies View Related

Splitting Name Field Into Two Columns

May 3, 2012

I have to split a filed that has Lname, Fname into two columns of Lname and Fname. I know how to do this in Excel (using left, right by calculating the len using the comma) but i don't know the functions or the code to do this in Access.

View 3 Replies View Related

Tables :: Splitting Table Into Multiple Sets Based On Row Count

Oct 5, 2012

I want to split a table into multiple sets based on rowcount. Suppose I have a table having 10,000 records. I want different sets which should have values based on rowcount. Suppose if I select set 1 then the table should populate records from 1-2500. If I select set 2 then the table should automatically give the records from 2501-5000. If i select set3 then the table should have values from 5001-7500 and so on.

View 3 Replies View Related

Complex Field Splitting Problem

Jul 20, 2005

OK Guys,
I am building a sales database for a client he has supplied the text files which I have loaded into tables.
Problem on the sales file there are multiple part numbers in the part field these are separated by '-' the same format is on the Qty and value fields. The logic behind this is:
For the 1st part in the part field the qty and the value are the 1st in there respective columns, for the 2nd part in the part field the Qty an value are the 2nd in there respective columns and so on eg


PS001 |BAGR3-BAGR4-BAGR6 |03-01-01 |4.29-5.17-5.99

How would I get above to look like this:


PS001 |BAGR3|03 |4.29
PS001 |BAGR4|01 |5.17
PS001 |BAGR6|01 |5.99

Any suggestions would be great

thanks

Chris

View 8 Replies View Related

Splitting A Comma Separated Field Into 4

Jan 30, 2007

Hi
I'm having a senior moment...

I have a reference field - examples below and need to split into the component parts

example Ref_IDs:
C21/0051,Sheet 18, Sheet 19, E 3, 12/01/07
C21/0052.04, Sheet21 ,E 2
C21/0054, Sheet 23, B 2, 05/01/07

First - ref no, Second - Sheet (note some have more than one), Third - Grid and lastly some have dates, but not all

View 6 Replies View Related

Splitting A Field’s Data Into Multiple Fields

Jun 8, 2006

Hi All

Been handed a contacts database, one field of the DB includes all the industry sectors associated with the customer, with multiple industry references separated only by a comma within the field (e.g. Insurance-Life,Insurance-Broking,Insurance-Motor,Insurance-Pensions,Insurance-Home,Insurance-Health) – I want to split this one field into multiple fields using the comma as the break point (this is something that’s relative easy to do in Excel with the ‘Text to Columns’ tool – but unfortunately it can’t take the 130,000 odd records of the databasel)

Can any one recommend an expression or method that will see the 6 industry references split into 6 separate fields ?

Thanks

Jim

View 1 Replies View Related

Queries :: Splitting Text Field Into Columns

Feb 15, 2014

Is there a function or query that I can split a field into different columns?

For example, I have First Name, Last Name, Address.

But I want to split the address field into Address1, Address2, Address3, Address4 as the initial Address field has a lot of characters with commas e.g. 11 London Road, Liketown, Likeshire, London.

So, I want to be Address1: 11 London Road, Address2: Liketown, Address3: Likeshire, Address4: London.

I thought that I can export the field using a simple query and then re-import it with using the text field into columns option, but it is time consuming.

View 2 Replies View Related

Splitting Text Values From A String Into Separate Field

Jan 25, 2005

I have a table field which long ago was merged from several other fields. When the data was merged into the field it was delimited by "1." then "2." up to "5."
Example: MergedField = "1.Animal 2.Large 3.African 4.Grey 5.Long Nose"

I now want to split it appart in a query where "1.Animal" goes into expression1, "2.Large" goes into expression2, etc.

I need to base the text on where the one number begins and grab everything until the next number in the mergefield is detected.

Can someone show me the syntax for this.

Thanks!

View 1 Replies View Related

Queries :: Update Fields By Splitting Text From Other Field

Nov 28, 2014

I have one field called FULL_ADDRESS. From that field I am going to populate two other fields. One called ADD_1 and the other, ADD_2.

In the FULL_ADDRESS field I have the following text :

"BOX 2747 ROCKY MOUNTAIN HOUSE AB T4T 1L7"

I want ADD_1 updated to:

"BOX 2747 ROCKY MOUNTAIN HOUSE AB"

and ADD_2 updated to:

"T4T 1L7"

While I understand how to do an update if I was using a space as a reference, i.e.

Trim(Left([FULL_ADDRESS],Instrrev([FULL_ADDRESS]," ")-1))
Trim(Mid([FULL_ADDRESS],Instrrev([A]," ")+1))

I can't figure out how to skip the first space from the right, and reference to the second occurring space, so that I get "T4T 1L7" to populate the ADD_2 field, and everything to the left of "T4T 1L7" placed in the ADD_1 field.

View 6 Replies View Related

Modules & VBA :: Splitting Dataset Sorted On Two Fields Based On Change In Value In One Field

Feb 6, 2014

I am new to MS Access, and am not sure if what I want to do is even possible. It is my understanding that comparing subsequent cells within a field in a database cannot be done- so I thought I'd see if there is a way to go around it.

I have a dataset for pedestrian activity, with over 3 million rows and 40 columns - too big for excel to handle.

I need to sort the entire dataset by 2 fields, following which I need to search down the field containing my pedestrian ids (numbered 1, 2, 3... till approx 10000), and when my ped id changes from one to the next, I need to check the value in a field showing the ped location, and if that matches with the ped's previous location, I need to copy out a cell corresponding to the previous cell's time stamp. If it doesn't match, I need to copy out another time stamp from another field.

View 1 Replies View Related

Reports :: Splitting Single Field Into Multiple Text Boxes Or Multiline Text Box

Jun 4, 2013

I have a single field in a table called "Client Contact", where users enter a semicolon between the name, address, and city state & zip. My reason for this was so we could copy client info with a single copy and paste (like from an email). But, on the final report, it needs to have these three parts split up into different lines, or even different textboxes. I can't find a way to do that.

View 1 Replies View Related

2 Digit Month

Jan 4, 2006

Hi,

How can I get a 2 digit month when I use month(now())? I've getting only 1 instead of 01. Please advise.

View 1 Replies View Related







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