Return Only Part Of A Large Data Field

Nov 27, 2006

Hey all

I have a table that contains a list of news items. For each item there is a link to an appropriate image and a load of text.

I display a list menu of news items showing the picture and the first few lines of text. At the moment for the first few lines of text I have a separate field and just copy and paste the first few lines of the main article into it.

My question is is there a way where instead of having to have a separate field with just the first few lines in I can somehow just retrieve the first few lines of data from the full article field when displaying the menu listing?

Hope this makes sense, and any ideas greatly appreciated.

Many thanks

View Replies

Tricky Question - Form Part Data Entry, Part Not

Dec 16, 2004

I have what I think is a difficult problem to overcome...

I am designing a form to create an invoice. The user will select a workstream and a date range in form frmInvByHrs. Within this I want two sub-forms, one is frmInvByHrsTsht and the other is frmInvByHrsBill. I want the first one to display all the staff and their hours done, and the second one to be in data entry mode where you can enter the hours you want to bill. Each sub-form is based on a separate query.

Is it possible to do this? ie. to have one sub-form in data entry mode, and the other not? It seems to me that the data entry mode is controlled by the MAIN form regardless of the sub-form settings!

If this is not possible, do you know how I can acheive this?

Thanks

S

View 1 Replies View Related

Forms :: Handling Large Amount Of Data In Memo Field

Jun 9, 2013

My DVD library database I have a memo field which has the description of the movies.

In some cases, like my tv show records, I have a lot of data because of the episodes and episode descriptions.

I'm currently displaying this data using a split form with the datasheet on the left

I must use a small font in the memo field in order to display all data.

I could use a larger font and simply scroll but I'm looking for an alternative.

I've even thought of creating a hyperlink filed and point to the movie on IMDB's web site. But when I click the link nothing happens even though I copied and pasted the link into the field.

View 8 Replies View Related

Queries :: Match Postcodes Ad Return Part That Matches?

Sep 7, 2014

I have been given a list of UK postcodes, with the following format L15TG or TS14TGU.

I need to be able to match these postcodes to a list of postcodes I have stored in the database, however, my list are only UK outcodes, so L15TG is just L1 and TS14TGU is just TS14.

So I need to match the records and return the part of the string that matches i.e take L1 from L15TG.

View 5 Replies View Related

Working With Large Data Sets.

Mar 31, 2008

Hi guys,

I have a query that returns a large data set (~100k rows/month).

I am currently inefficiently exporting week-by-week to Excel to carry out some manipulation (mainly a set of "if" statements to calculate differences between records that have the same site id).

Is there a way to write a function in Access to carry out the calculations? I'm not too familiar with Access VBA apart from coding functions for forms etc.

View 12 Replies View Related

Number Too Large For Field

Mar 28, 2006

This number is too large [220020220020] for a field in my table. I currently have it set to Long Integer. What's the proper setting for a number this large?

Thanks

View 3 Replies View Related

How To Handle A Large Data Cleaning Task?

Jan 11, 2006

I need to create a program that will regularly import a text file of over one million records into an Access table. I've been give a list of about fifty different updates to perform on the data to clean it.

I can't imagine performing all these updates in one query. However, creating fifty individual queries seems horribly inefficient from a processing perspective.

I'm accustomed to stepping sequentially through a table in FoxPro, which seems ideal to me for this type of situation. What is the best way to handle this in Access?

View 4 Replies View Related

Large UNION Query Truncates Data

Aug 18, 2003

I ran into errors recently when I tried running a large UNION query (about 6,900 news articles) in a desktop Access database, that will eventually be migrated to SQL Server. I've got two tables, each having the same typed fields. I joined then through the following query:

SELECT ID,Name,Body FROM Table1 UNION SELECT ID,Name,Body FROM Table2;

The problem was that in the resultant table containing the conjoined records, one of the fields (Body, a MEMO field) copies only the first 250 characters or so, truncating the rest of the data.

I was thinking this might have something to do with telling Access how to type the data when copying it over. Is there a way to explicitly tell a query the data type of each field to be used so that the data can be copied over properly?

Thanks!

View 3 Replies View Related

Large Data Imports Expanding File Size

Dec 7, 2007

Morning all,

I'm having a problem with mdb file size. I'm importing a large amount of data from a number of tab delimited text files via a simple transfertext function. The process goes: empty the tables in the database, then import the data into the tables.

All this works fine, but the file size rockets to over 1.5Gb. When I then compact and repair, it goes down to 420Mb. I'm not deleting and recreating the tables, and at no point is there 1.5Gb worth of real data, so what's causing this?

N.B. I realise I can call compact and repair following the import, but this is going to take too long as they are user-initiated imports.

View 4 Replies View Related

Forms :: Display Fields With Large Amount Of Data

Mar 27, 2013

Best way to display "notes: field that gets updated/added to multiple times, in a form. (Access 2007)

I've got a DB to track information about product changes. Each month we have a meeting and discuss problem products. I'm concerned with a notes/comments section. Currently it is a memo field in the source table that can be added to, so the notes are cumulative. But we want to auto-add a date to each new entry (in case the person inputting the data forgets, and so that notes can be sorted by order on a report).

There may be many comments on a certain product or there may be none. Sometimes the note may be very long, other notes on the same product may be very short. I created a new table with a one-to-many relationship to case ID (to link it to the main form), auto-populate a date field, and field for notes in order to link multiple notes to each case (and of course an autonumber PK).

I can put the subform in the original form, but then the note field will be the same size for every note, though notes are not all uniform in size/length, and it ends up taking up a LOT of room on the main form. So I created a button to click to open the subform, but it brings up all records for all cases, not just the currently selected case. Again, have the problem of one uniform field size for all notes, regardless of size/length.

Then I considered a report to view past history (field size can shrink or grow as needed) then a button on the report that opens a form for data entry/entry of new note. But again the report displays all records for all cases, not just the currently selected case.

View 2 Replies View Related

Adding Large Amount Of Excel Data Into Access

Jun 16, 2015

I have 250 separate worksheets with a lot of data to put into Access. Problem is the data is 120,000 rows in each worksheet and a lot of duplicate date eg..DATE, NAME, TIME,are some of the column headings and there are multiple rows with same DATE or NAME. That is just how I received the data. I would like to transfer all records into Access as quickly and efficiently as possible.

View 4 Replies View Related

Changing Field Length In Large Database.

Feb 6, 2006

I am trying to change a field lenght of a large database (access 97) It is at 50 right now and want to change it to 25. When I try to change it and save the table I get a error when the bar is about 3/4 the way accross the bottom.

Microsoft can't change the data type - there isn't enough disk space or memory.

then

not enough space on temporary disk

This is error 3183. In the help it says that the TEMP DOS enviroment variable location doesn't have enough space. (summerising). Now I have a 100+gb drive that isn't close to being full. I also changed the MaxLocksPerFile registry dword to 8,000,000 as my table has around over 3 million records. I have tried this on a xp and 2000 machine same results. Next I am going to try it on a 98 machine. I could do it by breaking up the table into 2 differnt ones and do each one individually but there is an autonumber field used as a reference number (I know I know). So that would screw up the numbering scheem (Yes I know there are ways arund that also). this database is about 400MB

Couple of questions
1. Is there a way around this? I have not found a solution on line yet.
2. Will lowering the field length make the database smaller (the data that is in there now is less than 25 charecters and the field is set to 50.) I have gotten mixed info on this. there are a few fields I would like to reduce.

Thanks
sam

View 2 Replies View Related

Add Primary Key Field To Existing Large Table

Apr 28, 2008

I have a table with more than 700,000 records. There are no unique fields or unique combination of fields. I would like to add an AutoNumber field but when I try I get the message:

File sharing lock count exceeded. Increase MaxLocksPerFile registry entry.

If I answer yes I get an error that the new field was not added. Is there any way to add a key foield to an existing database?

View 3 Replies View Related

General :: Get The Next Large Number In Access Field

Feb 19, 2013

I have a data where I want to create a query fulfilling the below conditions. Suppose I have two table: Table 1 and Table 2 If a value ex.98 (Table1) matches with the value with 98(Table 2),it should pick up my second higher value 103. suppose 103 is the next high value of 98 . Please see the data value.

misprepaid.asmvalue from Table2 Required Result Con 989898 then 103
if value of table1=98 then 103 from table 2 (next large number) 103103103 then 149
if value of table1=103 then 149 from table 2 (next large number) 149149149 then 175
if value of table1=149 then 175 from table 2 (next large number) 175175175 then 198
if value of table1=175 then 198 from table 2 (next large number) 198198198 then 199

[Code] .....

View 9 Replies View Related

General :: Large Data Entry - Access Mail Merge

Sep 10, 2013

I have created a rather large data entry form for one of our departments which will be used to run a mail merge document. The merge is run off of a query of the form, rather than the tables themselves...I think that is what I've read is the right thing to do?

Anyways, most of it is working great so far but I've run into an issue where a few of the form fields are combo boxes. In access and in the query the data looks correct, but when you look at it in word (in edit recipient list) and after the merge, it will have a file path name instead.

So a combo box that has "Medical Plans and Visual Plans" in that field in the query, comes over saying "c:Users
ameAppData

Ex: New Hires are provided information about Medical Plans and Visual Plans.
Ex: New Hires are provided information about MeC:UsersbrooksAppData

It always shows the first two letters before putting the filepath name in there.

I have tried doing it multiple ways...a lookup to another table, lookup right in the field itself....get the same results.

View 3 Replies View Related

New Field Value From Large Table Column To Be Highlighted In Report

Sep 3, 2015

I have a daily report that shows data from previous day for production. When we have new products produced, I would like a field to be highlighted if it is the first time it has come up. I do not want it a unique field just from yesterday, but to analyze the table of all of the production days and highlight if a particular field from a column is unique. Can this be done?

View 6 Replies View Related

Date Problem - Part Constant - Part Now()

Nov 3, 2006

Hi everyone,

I have refined my query from previous threads to involved a module function. This calculates more acurately no of working days between dates and takes into account a holidays table. (All credit to Arvin Meyer on the module:) )
However because the Leave Year starts at the 1 July and finishes 30 Jun I need to compose the date for any current year Year(Now())

Enclosed scrdmp shows my query design. I can easily get it to work as you see it, but obviously as each year rolls over, the year needs to change.

Have looked at many posts but can't find what I'm looking for. This one will get me over the hurdle.

Many thanks,

View 6 Replies View Related

Part List Data Base

Mar 25, 2006

I am trying to create a database to create a purchase list of parts for a final assembly. There are several subassemblies that make the final unit. There are also subassemblies within subassemblies.

I want to be able to create a list of parts for purchase for the completed unit.

I have a table of part numbers that assigns part numbers to parts as ewell as subassemblies. in this table there is field that designates what type of part it is, System, assembly or base part. I am trying to query the "system" to get a quantity of all base parts contained in it. The feils in the table are part number, part name and what type of part it is.

I am having trouble with the queries or what should be in the table.

View 3 Replies View Related

A 2 Part (Data Entry Form) HELP PLEASE

Oct 3, 2006

Im trying to make a data entry form which will add a new record to a database. My problem is when I associated it with a table. it starts off with a record already populated in the fields. Can someone tell me how to start off with a blank form to add new information.

Part 2

I wish to have a combo box that will drop down with SSN#s and populate the Address field automatically so that a person with additional orders will not have duplicated addresses but just one main address.

Thanking you all in advance!

View 4 Replies View Related

Displaying Only Part Of A Field

Jul 23, 2005

On a report I have a field that has a value that can be anywhere from 15 to 25 char. Can I set up the report field to only display the 1st 10 char. ?

jon

View 9 Replies View Related

Filtering Out Part Of A Field

Apr 7, 2008

Hi,

Apologies, I have no idea if this should go in reports, queries, macros or modules and VBA as I'm a bit stuck but hopefully it's fairly simple.

I've been teaching myself access and it's been going well. What I have now been asked to do is produce a report that generates the shift patterns for everyone in the office as an HTML document. Now, the data is all exported from another program and I've had no problem getting the data into access easily. The problem I have is the format some of the data is in.

The major one that I need to solve is showing what time people are meant to take their lunch break each day. The field for break is filled in in the following format.

07/04/2008 12:45:00

Now, I have the date from elsewhere so I really don't want the date to show up so I need something that removes the date from this field. Is this going to be easy to do? (Ideally I'd like it so that the above example actually just returned 12:45 but if it has the 00 on the end that would not be the end of the world)

Apologies if this is in the wrong part of your forum.

View 3 Replies View Related

Search Part Of Name Field

Jul 23, 2005

Hi Guys!

I'm working on this database in which I have 2 forms:

one form has personal information (PersonalInfo)
and the other one is a search form (Search)

In the search form I have a field in which I want to type any part of the Name field in PersonalInfo form and I want to retrieve all the records that have this part of the name.

Here is what I tried:

In the PersonalInfo form, I want to the criteria of the NameField and typed the following:

Like %[Forms]![Search]![SearchField]%

but it didn't work.

Any help will be very much appreciated.

Regards,
CS.

View 4 Replies View Related

Extracting Part Of A Field

Jul 27, 2005

I found a thread from last year that is close to solving this question but not 100% so I'll post this new thread.

I have a field called ITEM_NUM in a database with the following structure:

XX-YYYYYYYYY
XX-YYYYY
XX-YYYYYYYYYYYY

I need to extract the Y portion of the data and a Query format would be the best.

FYI, the X portion consists of 2 Letters/numbers then the Hyphen and the Y portion is variable in length.

thanks for your help!

View 1 Replies View Related

How To Select Part Of A Field

Sep 6, 2005

I have joined 3 tables using the query design and I have 3 different fileds. There are fields that have information that I don't need. e.g. I don't want the first 10 characters of field 1. How do I do this in query design (even in SQL view). Or do I need to do this in the table itself before joining the table. Hope my question makes sense. Thanks for any input.

View 1 Replies View Related

Appending Part Of A Field

May 5, 2006

I have a table with names in a field called [Name] in the format Mr John Smith
I need to append the names to another table but to 3 separate fields for title, initial, surname
I know the format for example Left([Name],1) to take only the first letter, but how do I tell it to take from the left up to the first space for the title, how to take between the first and second spaces for the first name etc?
Any help gratefully received

View 1 Replies View Related

Updating A Field With The + As Part Of The Value

Jun 12, 2007

Hi all,
I am trying to update the value of a field from say, "Word" to "Test120+". I am using the SQL code as below....

UPDATE tblTable SET tblTable.Field = "Test120+"
WHERE (((tblTable .Field)="120") AND ((tblCurrent.Field02)="TT"));

But the end results are that the value has been changed to just Test120 !! The plus (+) sign has been excluded. Is there anyway I can force the plus sign to be used in this update query or has Access some sort of bug because the plus sign is used as an arithmetic character that prevents it from being used as a value in a query ?

Any workarounds or advice greatly appreciated.

Thanks in advance,
Mitch....

View 4 Replies View Related