I'd like to run a search and replace on a query result to remove carriage returns, linek breaks and tabs from the data. I know that MSWord has character aliases that will allow you to do a search and replace - does Access have an equivalent for these?
Thanks!
Susan
I have imported an Excel spreadsheet into an Access database. In Excel, a line break in a cell is entered by ALT+Enter. After importing my spreadsheet, all my line breaks appear as "□" and the text all runs together (no line breaks) in the table. The "□"s are also printed in reports. I need a way to either (1) make the "□"s appear as line breaks (e.g., by changing some Access property) or to convert the "□"s to something that shows as a line break in an the Access table memo field (e.g., CNTRL+Enter). Can anyone tell me how to make this happen?
I've tried to copy/paste the "□" into the Find/Replace dialog, but nothing appears when I paste. I understand the memo field is unicode, but I have not been able to determine what the "□" character actually is. It is not the same as the only similar squared shaped character I find in the Arial character map (U+25A1: White Square). I can copy and paste the "□", if surrounded by some other text, e.g., "xxx□yyy", into a Notepad document and it shows up as "xxx□yyy". Pasting the same into a Word doc and I get
"xxx
yyy" with the line break properly displayed. I can copy and paste just the □ itself in Notepad.
I'm not sure if this is the right section for this question, but not sure where else to ask it.
We have a database and a query that is run that creates a table for a webpage.
The webpage (created in FrontPage) displays the info we need, but what we have 1 field that we would like to have display in a certain way. What we would like is, instead of having this 1 field string all the words out in a row.
For example:
word 1 word 2 word 3
Is there something we could either do in the query or within the webpage, that will force each word to a new line within it's table cell on the webpage
So would look something like this.
word1
word2
word3
it makes it easier to read on the webpage.
I have a memo field that contains several text strings separated by line breaks. I would like to write a query to add another text string to the end of the field. How do I embed a line feed in the SQL?
View 3 Replies View RelatedI created a form with a memo field. Data is entered. How do I get the data to print on the report on separate lines rather than one continuous paragraph? For example: Entered in form:
2/1/15 visit to house. 2/2/15 ordered new locks for doors. 2/5/15 Locks installed.
Print view on Report:
2/1/15 visit to house.
2/2/15 ordered new locks
2/5/15 locks in stalled.
I programming a eBay upholder, and having an issue with the description, as there cant be any line breaks in the column. I have little knowledge of Vba programming. I think I can use the replace command to change the line breaks to spaces.
View 4 Replies View RelatedI'm trying to clean up a form a bit and have it only show certain subforms/graphs if the data exists. I already have columns in a combobox query to show an "X" for if certain data appears:
Now, I know I could build another query and have some system go through and identify these things, but the easiest thing would be to reference the "X" in the columns of the combobox. Is there an easy way to reference values in the other (non-primary) columns? Or can you think of an easy way to make these subforms only be visible when the data exists? Maybe have an on load event for the subform?
I have a form with two tabs... Its record source is Person and Account.
The main tab has a load of fields relating to the person and the other tab is meant to show the bank accounts that the person has in a subform. However if there is no bank account for the person then nothing will show in either the subform or on the person tab.
I have checked throughly to see if there is anything saying
SELECT Person.*, Account.* WHERE PersonName = etc
which would only get the account and person details if personname was there.
The form is accessed by means of a search form which opens the form like so.
DoCmd.openForm "PersonAccount", , , "[PersonName] = '" & personName.Value & "'"
So any ideas why nothing at all shows not even the Person details?
can someone help:
in a form I've created showing products bought, when I add a new new order it still keeps the previous order's data, how can I prevent that from happening?
cheers
Hi
New to the boards.
The database i have created needs data removing from it when certain criteria arent met. Basically, when the form is filled in and the person is under 25 i need one range of data to be removed from another table leaving the ranges available to that person. how would i initiate this?
any pointers or links to where i can find this out (anything, i am desperate!!! :) ) would be appreciated
Thanks
Please see the screen shot attached.
I have a single form with multiple tabs. At the top of the form appears the name of an individual and below the name are multiple tabs containing information specific to that individual. Each tab has a separate underlying table, which is the data source for the information contained on that tab.
As best as I can tell, I can only use one single data source (a query at the moment) to populate all the data that appears on all the tabs. Is there a way that I can have a separate data source (namely, a table) for each tab?
I created a report to print out a letter that will be sent to all of our clients. In the address section of the letter there are two field for adresses...Address1 and Address2. One is for a street address and the other for a PO Box. Not all of the clients have a PO Box and for these, I do not want the blank line to show in the address. For example:
John Doe
3 Main Street
Franklin, MA 02038
The field and section of the report are set to "can shrink" and "can grow", but it still gives me a blank line in the address for the clients that do not have a PO Box. What else do I have to do?
I want to hide external data and databasetools tabs only from my access program. How i do it ?
View 3 Replies View Relatedi want to hide external data and databasetools tabs from my access program.
how i do it ?
I would like to remove small value datalabels in a stacked column barchat.
If you look at the image attached, the small value datalabels tend to clutter the image.
My graph is a MSGraph.Chart.8 inside a Report.
I am working with Access 2010.
Looks like the only possibility to remove the small values is to do that programmaticaly in VBA.
I would like a method that I could call with two parameters : graphname and a threshold value as of which small value datalabels are not displayed.
I thought I had solved my initial problem of removing the apostrophe character for each zip code field. Most of the fields have data that reads '12345-1234'. I want to remove each (') character and the "-1234" so I end up with a zipcode of 12345.
I created 2 queries for this process. I first run a query with the following statement "Left([ZIP],Len([ZIP])-6)". That gets rid of the "-1234' ".
The I run the second query with this statement "Right([ZIP],Len([ZIP])-6)".
These 2 queries work perfectly if the original zipcode is " '12345-1234' ", but if it is " '12345' ", the entire zipcode is deleted.
I have attached 3 (.jpg)'s to show you what I am talking about.
Here's the statement
Code:
sqlfinal = "SELECT Employees.ID, Employees.Name "
sqlfinal = sqlfinal & "FROM ((qryDeptVBA INNER JOIN qrySkillVBA ON qryDeptVBA.ID = qrySkillVBA.ID) "
sqlfinal = sqlfinal & "INNER JOIN Employees ON qryDeptVBA.ID = Employees.ID) "
sqlfinal = sqlfinal & "INNER JOIN qryAreaVBA ON Employees.ID = qryAreaVBA.EmpID "
sqlfinal = sqlfinal & "GROUP BY Employees.ID, Employees.Name;"
If i simply remove the GROUP BY line and stick the semicolon at the end of the previous line (.EmpID; ) it works just fine. How is adding a group by line causing an error?I tried adding another parenthes at the beginning ((( and ending the joins as EmpID); and that failed with the exact same error.
I have a main form with 3 sub forms. The main form is tied to a table called QUOTES_MASTER. The first sub form is tied to a table called QUOTE_ LINE_ ITEMS_DIRTGLUE. It calculates the subtotal when selecting items. The relationship is one-to-many linked on QUOTE_ID.
The second sub form adds up total of all line items and is not tied to a table.The third sub form adds ESTIMATED FREIGHT to the PRODUCT TOTAL and is not tied to a table. how to get the values from the line items form inserted into the QUOTE_LINE_ITEMS_DIRTGLUE table as they are added.
I also want to insert the total value from ESTIMATED DELIVERED into the LINE_TOTALS field in the table QUOTES_MASTER.I tried this code on the product total sub form but it doesn't do anything and there are no errors:
Private Sub PROD_SUB_AfterUpdate()
DoCmd.RunSQL "UPDATE QUOTE_LINE_ITEMS_DIRTGLUE SET QUOTE_LINE_ITEMS_DIRTGLUE.SUBTOTAL = Me.PROD_SUB WHERE QUOTES_MASTER.QUOTE_ID = " & Me.QUOTE_ID
I have a module which reads a CSV transaction file line by line and adds the correct transactions to an access table and places the wrong ones in a logfile.Now some transactions are rejected twice there is even one rejected six times. Whereas one wrong transaction is processed only once. I am certainly overlooking something obvious in the logic but what. Here is the relevant code.
Code:
Function ImportCSVForConfederation(inputCSV, ORG)
Dim TNO As Integer, TACT As Integer, TABLE As String, TLINE As String, I As Integer, J As Integer, K As Integer
Dim FLD1 As String, FLD2 As String, FLD3 As String, FLD4 As String, LogFile As String, LogPath As String
Dim Lim As String, ITNO As Integer
[code]....
Hi,
I have a form which records computer faults reported by users. On this form there is a subform for comment tracking eg. Phoned user, Date/Time. On the next line would be; Informed user to do this, Date/Time and so on. I have a button on my form which I would like to use to automatically put in a line of data. It is an aknowlegde fault button. What it hopefully would do is when you click the button it will automatically put in the date/time in the subform, Then the word "acknowledged" in the comment field, then prompt for the user name ( for the username field).
I am not sure on how to start the command. If i were to do it in the same form then I would just put something like;
me.Date.Value = Date()
me.Comment.value = "Acknowledged"
me.Username.value = "[Prompt]"
As you can see I have two problems though;
1. The data has to be entered in a subform
2. My Prompt I dont think will work.
Does anyone have any ideas?
Kind Regards
Keith
I am having a little difficulty with my importing in Access. Every time I import my text file, the lines will be jumbled. I have been reading up and I found this recordset code that seems to be what I need:
Code:
Dim strLine As String
Dim intLineNum As Integer
Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Open "C:TestTest.txt" For Input As #1
Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset("tblResults", dbOpenDynaset)
CurrentDb.Execute "DELETE * FROM tblResults", dbFailOnError 'Clear tblResults
[code]....
Basically, this code will extract data from the text file as long as it fulfills the Mid$ criteria. Here's where my problem comes. Each line in my text file is of different lengths and I have to capture the entire line.
I think using the Left$ function would work, but I don't know how to determine the character count such that the entire line of text would be inserted into the table.
Another difference between what I need and the code above is that, I am required to store each line into each row of my table, meaning
Line 1 is placed in Row 1 Column 1
Line 2 is placed in Row 2 Column 1
Line 3 is placed in Row 3 Column 1
.
.
.
Line X is placed in Row X Column 1.
hi all,
got a wee problem... i have a number of graphs showing volume (x axis) and pressure (y axis) for particular pipe sizes.... (it's for pneumatic conveying)
depending on the resolution of the hand drawn graph there could be 100's of points along a single graph line (x and y points)....
My program basically needs to select a point(x and y) closest to the graph line that it intersects. (come on mech. engineers)
Problem is how should i store / input the graphs into the db file?
I'm trying to avoid have to literally input every x 'n y point per line per graph... there are about 20 lines per graph and a 100 odd graphs!!!
I was thinking of inputing a start and end point then use some sort of formulae to draw a logical line and pick up all the xy points i need, then store them...
Any thoughts?
Thanks
Access 2010 report based on query based on 3 views using ODBC with the following sections:
Report Header
Page Header
Detail (1 - 3 lines)
Page Footer
The detail section has these lines...first line exits for all students, other 2 lines optional. Each line includes data from each of the 3 views:
Demograhic and bus/route information
Special needs information
Additional text information
Each student may have multiple lines in the detail section, one for pickup route, one for drop-off route and possible other routes.
Using VBA and the On Format event of the Detail Section, I have formatted the report to not display repeating information (not apply to use "Hide duplicates" feature) and not display either/both of the optional lines in the detail section.
All works well except that on the first line of each subsequent page, most of the data is missing. I have stepped through using the debugger and see that the data is returned by the query, but not displayed on the report.
I can change multiple things on a line graph with VBA.
Me.Graph47.chartType = GraphType ' take 65 for line
with me.graph47
.SeriesCollection(1).border.Color = vbblue ' change the line color
.SeriesCollection(1).border.Weight = LineWeight ' change the line weight to for example 3
.SeriesCollection(2).MarkerSize = MarkerWeight ' Change the marker weight, for example 4
.SeriesCollection(2).MarkerBackgroundColor = vbblue ' Change the marker color,
.SeriesCollection(2).axisgroup = 2 ' put this series on the secondary axis
end with
SeriesColection(1) is line with markers. This is correct.But now I want the seriescollection(2) without line, so only the markers. I cheched the MSDN site from Microsoft. The Excel trick with the macro does not work for me.how to hide the line with VBA for only SeriesCollection(2) in Access?
I have a main form where the user chooses a BoxID. There is a subform on this main form that has a relationship between BoxID on the main form and subform. The users are entering data in the subform and when they have entered 10 lines of info I have a message that tells them to start a new box (I could not work out how to do it any other way as I lack VB skills). The user closes the form or clicks add new record to create a new box. If I view the previous record there should only be 10 lines of information in the subform but there are 11 as it takes the blank line as a new record....
View 8 Replies View RelatedOn a subform that records notes in datasheet view (columns: Date, Note; sorted in reverse chronological order with newest at the top), is there a way to get the blank new record line to appear at the top? Otherwise, you have to counter-intuitively scroll down below the oldest entry to enter a new line.
We could sort in chronological order, but then the most relevant notes would sometimes be scrolled off the bottom of the form and not seen.