I am trying to update a memofield from a form.My file.. sign_midi.asp has this partial code.First I pick up the record I want to update.
And then present it in a form.
"
<tr>
<td align="right" height=10 valign="top"><b>Beskrivning :</b></td>
<td align="left" height=10 valign="top" width=250><TEXTAREA WRAP="soft" name="M1" cols="65" rows="8"><% = rs("text")%>
I am sure that in earlier versions it was easy to write the result of a calculated field on a form, back to a table, but I can't find the method in the help file for the current version of Access.
Is it possible to have IIf function which checks if there is a text1 in another field and if true writes text following text1 in to field containing IIf function.
Example(field1 containing text1)
Na avtocesti Maribor - Ljubljana pred priključkom Blagovica v smeri Ljubljane je zaradi prometne nesreče zaprt vozni pas.
I want to check if it contains red text and if true, write blue text.
field1.................................field2 ***v smeri A***.................A......... ****proti smeri B****.........0........
I have a query with many fields. One of them is the combination of two fields and is called "Components Reference". What I try to do is, every time this query is updated, the records of only this field "Components Reference" to be written to the field of another table which also takes records from an another query. I wish I was clear enough.
I'm having a bit of a problem with a query that I am trying to run that searches a memo field, and I hope someone can help me!
Basically, I have a fairly simple database, 2 tables - 1 of the tables has a memo field which has 'keywords' listed in it (a mixture of words/phrases separated by commas, although I've tried with just spaces in between).
When I search using a Query box for 2 keywords using the following query, it can only find the correct records when I enter the keywords in the reverse order in which they appear in the memo field... query: Like "*" & [Enter the 1st keyword to search for] And Like "*" & [Enter the 2nd keyword to search for] & "*"
If the memo field has data eg, "funding, teaching" (as opposed to "funding, nursing" for example) and I search for 'funding' and then 'teaching' - no results appear. If however, I search for 'teaching' and then 'funding'... ta da! Result found.
Obviously, whoever searches the database isn't necessarily going to know what order the words were entered into the memo field!
So, is this a peculiarity to Access, or is there something I'm doing wrong?
I have a table that contains a memo that is delimited by line breaks. For each of these breaks i need a new record in the query results that i can then use in a Labelling application.
My current query looks something like:
ID | Product | Pack Size 1 | item a | 1x1000,1x1050 2 | item b | 1x1000,20x25 3 | item a | 1x1000
(Where the , is a new line)
Whereas my ideal output is:
ID | Product | PackSize 1 | Item a | 1x1000 1 | Item a | 1x1050 2 | Item b | 1x1000 2 | Item b | 20x25 etc.
I think im supposed to use the Split() Function though i dont have a clue wherw to start.
Is there a way I can take each entry in a memo field and put it into a text field in a separate table. The database is getting really big and the customer notes field for each record has lots of entries. The memo field looks like this:
I want to take each line and put put the date in a date field and the text in a text field in a separate table linked by CustID. Is there a way to do that?
I like to store SQL code as a memo field in a table. I then use a form to collect selection criteria from the user, and this is used to create a 'WHERE' clause which is then appended to the stored SQL. This has always worked, but in one recent case the SQL failed. On examination I discovered that although the SQL is correctly stored in the memo field, when Access retrieves the code it inserts a space character in the middle of one of the words, thereby of course creating an invalid SQL string. It's easy to work around it, but why this happens? A bug in Access?
I have a query that I made for about five fields, where the criteria is
Like "*" & [Forms]![DATA SEARCH]![Box] & "*"
Where box is the name of the field that I am searching for.
However, some of the fields in my form are left blank, which makes this refuse to give any results when I try to query it with a form. The other problem that I have is that the fields are bland in different parts of the 1,000 some-odd row table, which was imported from MS Excel.
I haven't seen anyone run into this particular problem on this forum...
I'm importing data from an excel spreadsheet to an MS Access (2007) table. One of the fields in the table is a text memo field able to support more than 255 characters...
Issue: The issue is that any cell in Excel that is greater than 255 characters is truncated when imported to MS Access even though the field is a memo field. There isn't any documentation on Microsoft's website about this and I don't see any way to work around it other than manually copying the data from excel to MS Access.
Two Solutions to address moving an Access Memo field into Excel when string has > 255 characters. All my 'reports' use Excel VBA (Access Reports are not used). The Excel reports can have 40,000 records. Speed to create the report can be an issue.
Describing 2 Solutions below to address moving Access memo fields with > 255 characters into Excel.After running this code
Code: 720 ObjXL.DisplayAlerts = False ObjXL.Columns("X:X").Select ObjXL.Selection.NumberFormat = "@" ' set column to Text 730 ObjXL.Worksheets(intWorksheetNum).Cells(intRowPos, 1).CopyFromRecordset rsNutsAndBolts
The Comments column are limited to 255 characters. So, the CopyFromRecordset (recordsetvariable) creates the 255 character limitation.
The reason? The 255 character limit is because CopyFromRecordset sutomatically uses the Characters property of the Range object. The 255 limit would not be there if the Cell Value property is used to assign the string to that cell.
Dim sRx as String ' String Prescription sRx = "String with > 255 characters ... you fill in the rest ...." Cells(1, 1).Value = sRx ' Cell's Value property assignment can be very large
Solution 1:
The record set is still in memory. By using a loop, a cursor can start with record 1 (memo column) and assign that value to the Excel row / column using the .value as shown above. Basically, this moves one memo field at a time, record by record. e.g. Read First recordset in Access, copy to variable, assign value to Excel row/column Then move next on each Access and Excel.
Solution 2:
An Access Memo filed [RxNotes] can have up to 750 characters. Cut it apart into three new fields that end up out in the very right Excel columns AA, AB, AC.
Note1=Mid([RxNotes],1,250) Note2=Mid([RxNotes],251,250) Note3=Mid([RxNotes],501,250) Then using Excel Object - Concat the cells back cell by cell... X2=CONCATENATE(AA2,AB2,AC2))
Then delete the columns AA, AB, AC to hide the evidence..Neither solution is all that elequent. Read about this and by golly, it made a difference
ConcatComments = "'" & CommentString
Before using the CopyFromRecordset be sure to add a single quote in front of the large string.
Turns out the interface between Access and Excel look for this to prepare Excel immediately for the string to be a string, not something else. Some of my strings had weird print characters that kind of looked like Japenese characters. It seemed random, it always happened if the string was 255 or more characters (ramdonly, not always). The single quote doesn't show up in Excel, but got rid of all the noise.
I am in the process of building a new database in SQL to replace my MS Access database. However, I will continue to use the Access forms, queries, and reports. The new tables will house much of the same data. In multiple tables I have hyperlinks that were created and added in the original Access tables. To import these hyperlinks into the new SQL tables I have converted them to 'Long Text' before exporting, thus changing them into strings.
For example:
Hyperlink - Email - Add Additional Mailbox to Outlook (2010) has been changed to:
Email - Add Additional Mailbox to Outlook (2010)#ServernameServerfolderDocumentationRea dy to GoOutlook TemplatesEmail - Add Additional Mailbox to Outlook (2010).oft#
The obvious issue that I am running into is that after the SQL database table has been linked to the Access database it still displays the entire string when I open the table. The form has a textbox and search button that is used as a search function. This runs a query that returns all "search results" for the desired information. Is there a way that the query can convert the string back into a hyperlink so that the query displays just Email - Add Additional Mailbox to Outlook (2010) as a hyperlink and not the entire string?
I am trying to do some simple table operations. I have a field (Date) containing dates, and an empty field called Day.
I want to extract the day number from the Date field, and write it to the Day field.
I didn't get very far until I ran into trouble when setting my recordset. I get the error "Too few parameters, expected 1". Clicking "Debug", will highlight the code line "Set rs = db.OpenRecordset(sqlString, dbOpenDynaset)".
So far, my code looks as follows:
Code: Private Sub Command16_Click() Dim db As DAO.Database Dim rs As DAO.Recordset Dim sqlString As String Dim dataDay As Byte 'Open connection to current Access database Set db = CurrentDb()
[Code]...
I am not very familiar with the various types of recordset settings. I just want to be able to read data from the Date field, and write data to the Day field.
We need to move rich text from an Access memo field to a Word text box. So far the best Ive been able to come up with is in the code below. In this code pprs!What is a record set field of a table memo field that is bound to a text box enabled for rich text. The rich text seems to be stored as html as so I can get word to convert it by enclosing it in html tags.
Dim What As Word.Shape Set What = doc.Shapes.AddTextbox(msoTextOrientationHorizontal , doc.PageSetup.LeftMargin, 225, 534, 0) Dim sPath As String sPath = "G:Temp.html" Open sPath For Output As 1 Print #1, "<HTML>" & pprs!What & " </HTML>" Close #1 What.TextFrame.TextRange.InsertFile (sPath)
I have a Date Field and I would like to complete another field depending upon the contents of that date field.
In this way: if the Date is, say, 4/27/12 I want the other field to read: April 2012 and so on. Reading the month, putting it into words, reading the year, writing it out. This second field is NOT a date field.
Initially the query below used Inner Joins, however that limits my list, when i want to see ALL elements from the MasterTable no matter what, thus my inner joins became left and right joins as follows.
Simple Query: Query1
SELECT MasterTable.Station, MasterStable.Description, MasterTable.Area, AreaTable.Percent, GangTable.GangID, GangTable.Speed FROM (AreaTable RIGHT JOIN MasterTable ON AreaTable.AreaID = MasterTable.Area_ID) LEFT JOIN GangTable ON AreaTable.AreaID = GangTable.Area_ID;
This should simply display All Records in MasterTable, (Multiple Times if Necessary) listing all the elements of AreaTable that are Linked to the MasterTable, and all elements from GangTable that are linked to AreaTable. It does this, and displays them nicely. But I can't edit the fields. I get the result:
1 | Station One | Area 1 | 45% | 204 | 1000 1 | Station One | Area 1 | 45% | 304 | 500 1 | Station One | Area 1 | 45% | 404 | 750 2 | Station Two | Area 1 | 45% | 204 | 1000 2 | Station Two | Area 1 | 45% | 304 | 500 2 | Station Two | Area 1 | 45% | 404 | 750 3 | Station Three | Area 2 | 75% | 254 | 800 3 | Station Three | Area 2 | 75% | 354 | 600 3 | Station Three | Area 2 | 75% | 454 | 700
So you can see that Area 1 has multiple Gangs (204,304,404) and Multiple Stations (1,2). If you do a simple set up like this, you'll find that you can't change the Description field (Rename "Station One" to "Hello World"). It just doesn't work, no matter which way I've tried, I can't seem to make a Query that presents all the information from MasterTable and All the Information IN AreaTable and All the Information in GangTable which will allow me to also edit the fields.
Any Help would be most appreciative, I'm tearing my hair out on this one. Thanks, Jaeden "Sifo Dyas" al'Raec Ruiner
ps - It just seems that with Junction Tables and all the many to many relationship designs I have tried, you'd be able to change the non-related fields. I understand that you can't change the "ID" fields, but the others should be editable.
I have an access database and some asp-code on my computer which only works one way? I can read from it, but not write?
...and when I put the asp-code and the database on anohter server it works fine. ..what setting do I have to do, to make it work on my computer?
I have another asp-site with a mySQL-database on my compter which works fine...but it doesn't work with my access-database...hmm...I can only read from it.
I'm looking to create a "Same As Billing Address" check box that would automatically udpate the address in an order to reflect the customer's billing address.
I understand how to sync a combo box to an option group ( -- great article by the way)
I Guess what I'm looking to do is:
1. Sync a "Same As" Checkbox to multiple text boxes in the form (Unit, StreetAddress, City, Province, Postal) such that these fields are UPDATED (writen to) with the same information as the billing address
2. [U]IF the "Same As" Option is selected, I need the updated fields to lock, allowing no further update... but must by unlocked if the "Same As" option is NOT selected.
Please help me out with this one guys.. I really need it.
I have a form which has an unbound text field which is a calculated filed showing a date which is x months after a separate field on the form.
I need to write this field back to a table in the database when an "add" command button is clicked.
The table is called Assets The form is called Assets List The text field on the form is called txtWED and needs to be written back to the table "Assets" in the field "Warranty_End_Date"
If possible can I add this as an expression on an "add" command button?