Partially Deleting Text From A Cell

Jun 19, 2007

If I have the following value in a cell:

Joe <100,894> Doe

Is there a function in Access that will clear out the <100,894> leaving me with Joe Doe? To my understanding the Replace function only can replace certain characters. How can I delete everything in between the < > as well?

Thanks,
Paul

View Replies


ADVERTISEMENT

Deleting Certain Text Between Characters In A Cell

Jan 10, 2005

I have a table of 1216 records. The Title fields contain extra notes which I would like to remove. All of the notes have /'s around them. I would like to remove all of the text between the /'s and all of the /'s themselves.

Example:
ACCOUNTS OF CHEMICAL RESEARCH /ALL EXCEPT JAPAN/ /FOR INSTITUTIONS/

Should be:
ACCOUNTS OF CHEMICAL RESEARCH

I don't know how to do this, but I can do it in MS Excel or Access--whichever one is easier.

Thanks for help in advance.

-Siena

View 14 Replies View Related

Is There A Shortcut To Copy The Text From Cell Above?

Dec 7, 2007

Hello! I need some serious help! After 25+ years of using Paradox where I work, we are going to Microsoft Office and Access is stumping me on one thing!!! In paradox you could hit Ctrl+D and it would enter the same information as in the cell directly above. I can't find anything that shows that Access has a feature like this. I have our inventory database and I will enter maybe 25 of the same items, but they have different property numbers and locations only. How do I get it to repeat what is in the cell/row above?

View 6 Replies View Related

Tables :: Text Box To Cell Not Bound

Jan 4, 2013

I have a memo cell in a table that I'd like users to be able to insert their name into (from the same form). Basically allowing a user to throw their name into the memo, then auto-clear the box. When they enter their name a date will automatically be added.

Obviously if it's bound it will update but then it can't be cleared, and it doesn't paste this to the bottom of my memo, it tries to place it as the first few characters of my memo.

View 14 Replies View Related

Putting The Same Text Into Every Cell In A Query Column

Jul 22, 2005

Putting the same text into every cell in a query column

Hi All,

I sometimes have to mark every record in a query with the same text in a field, when the field is either empty or contains whatever text.

At present I do this by copying the text, and then pasting it (ctrl-v cursor-down, a thousand times) into every cell in a column.

How can I do this in a more efficient way?

Thanks for your help.

Adrian

View 8 Replies View Related

Want Linked Excel Cell 2b Memo Not Text?

Oct 13, 2005

I linked an Excel table and now, in both the table and report, the cell is limiting itself to 255 characters, even though the Excel cell has more. I checked the "data type" and it says "memo" but it keeps truncating to "text".

Where is this limit coming from and can I change the linked table so it won't truncate the text?

thanks :confused:

View 1 Replies View Related

General :: Odd-numbered Appearances Of Text Within Cell

Jan 9, 2014

My friend extracted some text from a PDF file, pasting it into Excel, and needs to find certain pieces of information. Problem is, the text came out as a long string instead of being broken into cells.

So, what we need to do is find, within the text, the 1st, 3rd, 5th, 7th, 9th, 11th and 13th times the word "Principal" appears, and then report back the name that follows. Within the text, it would read something like

(1234 Principal John Doe)

The number in front of it will change or be in a different format, there are other parentheses in the text (varying number of times within the text), and the name changes, of course. We want to extract the name following the word "Principal"... but only the odd-numbered times they appear. Some cells will have as few as six "Principal" entries, others as many as fourteen.

View 5 Replies View Related

VBA To Find First Empty Cell In A Row And Insert Text

Sep 20, 2012

I'm using Access to export the results of a query to Excel and within the same code I am opening up the spreadsheet to format it. Part of the formatting requires me to find the first empty cell in column A, and then insert the text 'Summary'.

Within Excel, the following code works:

Find empty cell:

Code:
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select
Add term 'Summary' to cell:

Code:
ActiveCell.FormulaR1C1 = "Summary"

I've tried a few different things based on some code I've found on the net from similar situations, but in just about all attempts I get an object defined error. I've tried dimming the piece of code as an object, but when it comes to VBA, I'm just fumbling through.

View 1 Replies View Related

Tables :: How To Insert Text Box Value To First Empty Cell In Table

Mar 17, 2014

I have a simple form (frmAddPaper): txtPaper where user enters name of new newspaper, and cboCity, where user selects the newspapers town.

Also Close and Save buttons.I also have a table, tblCity. Columns as follow: CityID, City, Paper1, Paper2, Paper3, Paper4, Paper5, Paper 6.

Some cities have values (Newspaper names) in just Paper1 field. Others in Paper 1 & Paper 2, and some in Paper1, Paper2 and Paper3.I want to add the txtPaper value, to the first empty column, in the row where cboCity matches City column.

View 14 Replies View Related

Deleting Certain Text In A Field.

Aug 31, 2006

i how i can get some help with this one, i have imported a txt file, once input into a table it has the fieldname inserted along with the data so for example record_id is the name of the field and in each column i have "record_id=###" the # respresent unique numbers. but what i am looking at doing is creating a "create table query and using an expressions like... "left([record_id],"=") but this won't work.

All the data in the columns has the same thing all have the field name with a = then the data i really need.

How can i separate?

PLEASE HELP ME!:confused:

View 2 Replies View Related

Excel Partially Visiable

Feb 19, 2008

Hey guys, I have the below code that exports data to excel. Works wonderful, except when the Excel file opens, only the top portion of excel is visable. I would then have to save the excel file, then close both excel and access. Then once I reopen the excel file it is now visible. I would like the file to open over all other application including my Access application. Is there any way to force the excel file to open fully? Is there something I am missing. It seems to work the first time, but if I re-run the export the problem continues. Thanks

Private Sub cmdGetReport_Click()
On Error GoTo Err_Handler

MsgBox ExportRequest, vbInformation, "Finished"
DoCmd.Close acForm, "frmMonthEndRpt"
Application.FollowHyperlink "R:Call CenterCall Center DepartmentsSupportDaily ReportsLoan Statistics & TrackingAOOutput.xls"

exit_Here:
Exit Sub
Err_Handler:
MsgBox Err.Description, vbCritical, "Error"
Resume exit_Here
End Sub

Public Function ExportRequest() As String

' Excel object variables
Dim appExcel As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet

Dim sTemplate As String
Dim sTempFile As String
Dim sOutput As String

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qd As DAO.QueryDef ' Added 10/06/07
Dim sSql As String
Dim lRecords As Long
Dim iRow As Integer
Dim iCol As Integer
Dim iFld As Integer
Dim strMacroName As String
Dim strFileName As String

strMacroName = "DeleteBlank"
strFileName = "R:Call CenterCall Center DepartmentsSupportDaily ReportsLoan Statistics & TrackingAOOutput.xls"

Const cTabOne As Byte = 1
Const cStartRow As Byte = 4
Const cStartColumn As Byte = 1

DoCmd.Hourglass True

' set to break on all errors
Application.SetOption "Error Trapping", 0

' start with a clean file built from the template file
'sTemplate = CurrentProject.Path & Call CenterCall Center DepartmentsSupportDaily ReportsLoan Statistics & TrackingAOTemplate.xls"
sTemplate = "R:Call CenterCall Center DepartmentsSupportDaily ReportsLoan Statistics & TrackingAOTemplate.xls"
sOutput = "R:Call CenterCall Center DepartmentsSupportDaily ReportsLoan Statistics & TrackingAOOutput.xls"
If Dir(sOutput) <> "" Then Kill sOutput
FileCopy sTemplate, sOutput


' Create the Excel Applicaiton, Workbook and Worksheet and Database object
Set appExcel = Excel.Application
Set wbk = appExcel.Workbooks.Open(sOutput)
Set wks = appExcel.Worksheets(cTabOne)

sSql = "select * from qryAOSummary"
Set dbs = CurrentDb

Set qd = dbs.QueryDefs("qryAOSummary")

qd.Parameters![txtStartDate] = [Forms]![frmMonthEndRpt]![txtStartDate]
qd.Parameters![txtEndDate] = [Forms]![frmMonthEndRpt]![txtEndDate]

Set rst = qd.OpenRecordset
If Not rst.BOF Then rst.MoveFirst

' For this template, the data must be placed on the 4th row, third column.
' (these values are set to constants for easy future modifications)
iCol = cStartColumn
iRow = cStartRow


Do Until rst.EOF
iFld = 0
lRecords = lRecords + 1
Me.lblMsg.Caption = "Exporting record #" & lRecords & " to AoOutput.xls"
Me.Repaint

For iCol = cStartColumn To cStartColumn + (rst.Fields.Count - 1)
wks.Cells(iRow, iCol) = rst.Fields(iFld)

If InStr(1, rst.Fields(iFld).Name, "Date") > 0 Then
wks.Cells(iRow, iCol).NumberFormat = "mm/dd/yyyy"
End If

wks.Cells(iRow, iCol).WrapText = False
iFld = iFld + 1
Next

wks.Rows(iRow).EntireRow.AutoFit
iRow = iRow + 1
rst.MoveNext
Loop

ExportRequest = "Total of " & lRecords & " rows processed."
Me.lblMsg.Caption = "Total of " & lRecords & " rows processed."

'Inserts Month,Year format based on entry in start date field.
wks.Range("A2").Value = [Forms]![frmMonthEndRpt]![txtStartDate]

'The Application.Run will run the Macro(s) that you saved in your spreadsheet
wks.Application.Run "'" & strFileName & "'!" & strMacroName

exit_Here:
' Cleanup all objects (resume next on errors)

On Error Resume Next
Set wks = Nothing
Set wbk = Nothing
Set rst = Nothing
Set dbs = Nothing
DoCmd.Hourglass False
Exit Function

End Function

View 1 Replies View Related

Partially Fill Key Fields

Oct 29, 2006

I have a mainform Transactions with TransactionsID and a subform Site Details with SiteID. For non-database related issues (my users like things the way they are) at the moment I have to use text strings for the key fields instead of autonumbers.

SiteId begins with the TransactionID number (the program does check for uniqueness for TransactionID). At the moment the user copies and pastes the TransactionID into the SiteID textbox, and then just needs to add the site name.

I would like the SiteID to have the TransactionID partly filled in, so that one more step is eliminated. I have tried using forms!fTransactions!TransactionID, but I get #Error. I have also tried copying the TransactionsId text box onto the Site Details form and setting the property to invisible, and then using the forms! and I still get #Error. My first attempt was to change the data control of SiteID to TransactionID, but that didn't work either - it changed all the SiteIDs to their respective TransactionIDs. Fortunately I was working with a copy (rule number one of database design) and no harm done.

Is the issue that I am dealing with is that SiteID is a key field and that key fields can't be calculated? If so I will long for the day when all my key fields can be switched to autonumbers.

View 9 Replies View Related

Deleting Values In Unbound Text Box

Aug 23, 2006

I have an unbound text box. I put a value, run an append query and append the value to the table. I want to know if I can delete the value in the text box automatically when the append query excutes. I am using a macro to run the append query.

Thanks.

View 1 Replies View Related

Database Windows Partially Hidden, Can't Move It

Jan 11, 2007

This one has me stumped:When a particular database opens, its window is partially under (hidden by) the menu and tool bar. I can't click on the window's top in order to move it. I've tried several things (i.e. resizing the screen resolution, moving the menu and tool bars, resizing the window, etc.) with no resolution. Any suggestions.Am using Access 2003, Thanks, Joel

View 1 Replies View Related

General :: Erasing Text From Textbox Upon Enter Without Deleting Variable Value

Mar 21, 2014

I have a form that has a textbox in it. I enter text into the text box by scanning a bar code. I then use openreport.cmd to pass the variable in the text box to the report I generate. The report then has a button that when clicked references the variable that I passed to it.

Essentially the problem I am having is that when I set the textbox back to blank, it also erases the contents of the new variable created. I want to either delete the value from the textbox upon scanning the bar code each time, or highlight the value in the textbox for it to be deleted upon the first key stroke. I had read about setting focus to the textbox, but I have been unable to get that to work. Here is my current code for what I am doing.

Code in the form:

stPartNum = Me.ScannerTxt
Label = stPartNum
DoCmd.OpenReport "RptLabels", acViewPreview, , , acWindowNormal, stPartNum

Code in report button:

partnum = Me.OpenArgs

View 5 Replies View Related

Forms :: Create Partially Completed Datasheet On-the-fly For Data Entry

Dec 6, 2014

I have a primary school database. I'm trying to create a form that allows a teacher to select their class, then select a subject and then be presented with a data entry form in a table layout that lists only their student's names in one column and an empty column to input results for the selected subject.

To simplify my explanation to just three tables, lets say my tables are:
> Students....which stores student names plus a foreign key for their class
> Classes...which stores the class name
> Results...which stores all the results (fields are: ResultID (key), StudentID, SubjectID, Result, DateofResult)

I have no problems creating reports where the teacher selects their class from a combo box to generate a report based on a crosstab query. But this one has me stumped.

View 2 Replies View Related

Queries :: Importing Excel File With Incorrectly Formatted Date Field - CVDate Partially Functional

Jul 22, 2015

I need to import an excel file with incorrectly formatted date field and it worked only to import them as text:

1 jan 2015
1 feb 2015
1 mar 2015
etc.

Using CVdate converts jan, feb, nov and dec to correct date, but gives an error message with mar to oct.

View 1 Replies View Related

Add Value To Cell According To Another Cell In Same Record

Nov 21, 2006

i have a table of articles. A field in the table is ArticleSubject

the ArticleID is made up of 3 letters then 3 numbers. i want the 3 letters to be something according to the subject
for example i want the first 3 letters of the ArticleID to be MAT*** (* is a number) if the subject is Maths
or ENG*** if the subject is English

the subject is picked from a listbox in the same record

how would i do this in a table . i am reluctant to use append or update queries.
but will do so if its the only way.

View 4 Replies View Related

Deleting All Related Sub Records When Deleting The "main" Record.

Apr 12, 2008

I have two tables.

1. Persons (list of persons)
2. Job history (list of jobs)

each person have their own job history. all these jobs are stored in the job history table. when i delete a person i would like the job history for this person deleted as well. each job stored in the job table have a field with person name, so that it is linked to this person.

how can i do this? vba or simple properties options?

- Roy

View 1 Replies View Related

How To Have New Cell With New Values?

Jan 9, 2007

Hello!
I have "ID" column and I want to create new column called "ID_new" where the value of each record should be "1000" plus the value of "ID".
For example:
First record, has "ID"=1, than I want "ID_new" to become = 1001
Second record, has "ID"=3, than I want "ID_new" to become = 1003
etc..


Any tips/hints?

Thank you in advance & best regards
noah

View 4 Replies View Related

Duplicating Cell

Jan 10, 2008

Hi Guys

Not sure what I should do here. I would like to have a cell populate with the value of a specific cell previously.
explain:
in excel, we can use formulas like this:

A1: 00:00 B1: 08:30
A2: 08:30 B2: 13:00
A3: 13:00 B3: 15:00
etc, etc
Notice that each cell in A is the same as the previous B Cell. So the formula for Cell A2 would be something like: =IF(B1>0,B1,"00:00")
and Cell B3 would be =IF(B2>0,B2,"00:00")

The fields I have in Access are:

TimeIn TimeOut

So I would need to get TimeIn to be the same as the previous timeout???
Not sure If I'm making any sense right now :)

View 5 Replies View Related

Copy Cell Above And Add 1

May 2, 2005

I have a subform in Datasheet view. One field contains numbers. When entering data into the datasheet I wish to copy the number from the cell above and add 1. Can anybody help me with the code to do this?

View 5 Replies View Related

Format Cell Color

Oct 19, 2004

This is a very stupid question, I'm sure...
I want to get Access to find a cells that have a certain value and make them a particular colour so that they can easily be seen by database users. I could also make the font a different colour. I've tried getting the builder to do this in the query I run (for the column the fields appear in) but nothing seems to work. How and where do I enter formatting for colour / font colour based on data that is already in the cell... and what do I need to type?

Also would it be faster to get Access to do this before I run a query - so that the formatting is appllied to the whole table or would it be faster to do it during the query process?

Any help would be much appreciated.

View 2 Replies View Related

Sub Cell Information In A Table

Aug 9, 2005

I am not sure if this has been done in a table before. I have seen it in forms but not in tables.

Here is my problem.
I have created a database for a planespotters club. This database has the tables.
ac_logged
ac_maker
ac_model
country
airport

I am trying to display selected information in the ac_logged tbl that is related to other cells. For example: If someone selects boeing in the maker field they will only boeing models in the model field. Also, if someone enters Ireland in the country field only the airports for that country would be listed.

I don’t want to do this in a form but I could settle for a query if I have to.

I was just wondering if this could be done in a table as opposed to any other way.

Hope I have made myself clear on this one. :confused:

View 6 Replies View Related

Replacing Cell Value In A Table

Aug 1, 2007

OK, here is my situation. I have an access database which is updated automatically every night with new data. My companies solution to this is each morning, the entire database is downloaded to my computer to be used by another application.

In one of the tables, there is a field that doesn't work for my purposes, so each day I have to manually open the table, and change the contents of that field for every record. For example, lets say I have the following table:

Field1 Field2 Field3 Field4
Rec1 Wigets Faucet Model1A
Rec2 Wigets Faucet Model1B
Rec3 Wigets Sink Model1A
Rec4 Wigets Sink Model1B
Rec5 Wigets Toilet Model1A

Each day I manually change Field2 so it reads "Total" and whatever is in Field2:

Field1 Field2 Field3 Field4
Rec1 Total Faucets Faucet Model1A
Rec2 Total Faucets Faucet Model1B
Rec3 Total Sinks Sink Model1A
Rec4 Total Sinks Sink Model1B
Rec5 Total Toilets Toilet Model1A

Each morning when I receive the new database download, they all go back to widgets.

Is there a macro or module I can create to automatically look at Field3, and then assign a new value to Field2 based on Field3's value?

Any help would be appreciated!!!!

View 3 Replies View Related

Value Of A Specific Cell From A Subform

Nov 4, 2005

Hi experts,

I have a mainform in which there is a subform. Is there any way that I can retreive the value of the selected cell of the subform in the main form?
Currently what I usually do is that using a text box and setting it's control source to that special field in the subform, we have the value. But this is only for a specific field.
To explain my problem more precisely, I have a subform with 7 columns (fields) and 5 or 6 rows (datasheet view). When I click with the mouse on a specific cell ( or navigate within the subform with the arrow keys) I want to have the value of that cell in my main form.
Thanks in advance.

View 3 Replies View Related







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