Hi, I got this code from a member here (thank you, dbickin!), and I've altered it several times to fit my needs. This time, though, it's not working. My goal is to delete all of the text to the left of "C-W." My previous goals were to delete text to the left of /, -, and (. Maybe it's not working now because C-W is three characters rather than one.
Here's the code... 3 samples of what WORKS, and the last one doesn't.
Function RemoveNote(TextIn As String) As String
Dim TextOut As String ' buffer for result
Dim i As Integer ' index into string
Dim InNote As Integer ' flag showing if we are inside a note
InNote = 0
For i = 1 To Len(TextIn)
If Mid$(TextIn, i, 1) = "/" Then
InNote = Abs(InNote - 1) ' toggle flag
Else
If InNote = 0 Then ' not in note, so copy character
TextOut = TextOut & Mid$(TextIn, i, 1)
End If
End If
Next
RemoveNote = TextOut
End Function
Function RemoveNote2(TextIn As String) As String
Dim TextOut As String ' buffer for result
Dim i As Integer ' index into string
Dim InNote As Integer ' flag showing if we are inside a note
InNote = 0
For i = 1 To Len(TextIn)
If Mid$(TextIn, i, 1) = "-" Then
InNote = Abs(InNote - 1) ' toggle flag
Else
If InNote = 0 Then ' not in note, so copy character
TextOut = TextOut & Mid$(TextIn, i, 1)
End If
End If
Next
RemoveNote2 = TextOut
End Function
Function RemoveNote3(TextIn As String) As String
Dim TextOut As String ' buffer for result
Dim i As Integer ' index into string
Dim InNote As Integer ' flag showing if we are inside a note
InNote = 0
For i = 1 To Len(TextIn)
If Mid$(TextIn, i, 1) = "(" Then
InNote = Abs(InNote - 1) ' toggle flag
Else
If InNote = 0 Then ' not in note, so copy character
TextOut = TextOut & Mid$(TextIn, i, 1)
End If
End If
Next
RemoveNote3 = TextOut
End Function
Function RemoveNote4(TextIn As String) As String
Dim TextOut As String ' buffer for result
Dim i As Integer ' index into string
Dim InNote As Integer ' flag showing if we are inside a note
InNote = 0
For i = 1 To Len(TextIn)
If Mid$(TextIn, i, 1) = "C-W" Then
InNote = Abs(InNote - 1) ' toggle flag
Else
If InNote = 0 Then ' not in note, so copy character
TextOut = TextOut & Mid$(TextIn, i, 1)
End If
End If
Next
RemoveNote4 = TextOut
End Function
Looking to have a time field that a user can enter how long they worked an issue. Format should be ##:##, but I'm afraid that if I use text, calculations will be impossible. How does someone do this if it's expected that the value will frequently go over 24 hours?
I have large sets of data that has labels on them. For example "25.56 lbs". I just want the raw numbers. Can someone help with what to type in the update to field of an update query to just keep the numbers? Thanks
I have a form with a unbound text field which when a user inserts text and then removes the focus from that field, the text gets inserted into a memo field. By using:
However, I have noticed an issue with this, in respect that when the user enter text then moves to the next record, the previous entered text is still there, This could then cause this old text to get inserted into the wrong record. How can I set this field to blank once it I have moved to the next record or closed the form?
I have two questionaire forms in a database. There are no tables or queries involved. The answers to each questionaire are compiled into text boxes, one on each Form. On the main form I have another text box that puts all the answers together so that this can be transferred to another program. An example of what is in each text box is as follows:
These are just small extractions. My problem is, if an answer is not necessary in the first lot of text, but an answer has been supplied in the second lot, how do I remove the spacing that has resulted from my Formula in Text1. -
e.g: Normal answers would show perhaps Yes Twice 16 25 Red Peter Ford Football 1965 - Whereby the first 4 answers are from Text1 and the remaining answers from Text2. If some answers are not necessary from Text1 I would get the following result: SPACE SPACE SPACE Twice Red Peter Ford Football 1965
Thank you for taking the time to look at this Question.
I am using a form in which i only need the last sections.
example:- G/001 - Ceiling - Tiles - Perforated. this is what i get at the second, but i would like to drop the "G/001 - " and just have "Ceiling - Tiles - Perforated.", has anybody got any ideas on how to do this please.
I'm not good with access at all, i'm basically after removing all workgroup protection from a database file. I have full access to the file, admin passwords etc, i'm just having serious difficulty trying to find a tutorial or any information regarding how it can be removed!
I use the template service call management . In the work order section there is a field called Entered By . I would like to remove it as I do not need it and if you do not enter it the work order will not complete . Is there a way to remove it or make it so you do not have to enter anything there >> I have tryed to remove it but somehow it is connected to some thing else and I get error
I am totally new at this database stuff. I have been searching the archives for a couple hours and confused on how to accomplish what I need to do. I saw references to using a module but I have not used that feature yet.
I am using Access 2003. I need to remove/delete " - " space dash space, "-" and "&" from a string in one field (DGName). I'm trying to do this with a make table query
DGName P1000 P1000 - SMLS P1000-CA (not a type error) UD000 - C&B V-NET
I posted an earlier question about an Access query export to Excel putting leading apostophe (') before all data. To remove them I thought I would use Find/Replace but the Apostophe is not recognised!
Is there a way (programmatically) to remove the first character of each data entry in each cell in a column?
Can anyone please help me, I currently have a series of queries that are called by a macro, these create a number of tables. But when each query runs it provide a prompt stating what it is doing, my question is can these be stopped so that the queries run without a user being present.
I have a table that has mutliple records for the same person. I know how to create a query to remove duplicates but i don't know how to create a table to remove duplicates but keep the most recent record. Is this possible?
ie client status date 123 A 1/1/07 123 C 2/5/06 123 A 9/3/07
When I ve gone in and deleted a record from my table (new table just testing it) ie. enter number 1 deleted it and than gone in and entered (on the form) another enter the (auto number) goes to 2. I want to know how to i get it to go back to the previous number.
Both tables contain the same data; however the “tmptable” contains updated data. How can I compare both tables and remove all the duplicate in the tmptable, leaving only the updated data.
I will be comparing the fields “IsMandatory” from both tables
I have a field that contains serial numbers. The serial numbers are entered in difference formats. Some will have dashes, spacing and periods separating the numbers (example: 06-65432 or 06 65432 or 06.65432). I am trying to create a search field for this and the different formats makes it difficult. Is there a way to remove all separators so that all parts of the serial number are together?
i have a form with various fields, on this form i have an archive button that places certain fields in an archive form. however this does not remove the information from the main form. pls see attached
I am running a query to return records from a table. I want to eliminate records from the query if the values contained in one field are duplicated. I have tried using the DISTINCT keyword but it only works if the entire record is unique or if I was retieving just one field
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?
This may seem like a silly question, but I can’t find the answer to it on this forum. People only say 'Remove the table links' but never how. I can’t seem to be able to remove the table links completely, so I only have one database file (no backend or frontend). I can’t seem to do it though the Table link manager or any other way. Any help?
I have a date field that shows the date like 12-Sep-05 (medium date). I have a report in which the date has to display without the placeholders (e.g., 12Sep05).
I believe what I need to do is create a field in the query to remove the placeholders but I don't know how to write the code. Will anyone be willing to show me? Any help would be appreciated.
I have a bunch of server names that have names like aaa.bbb, xxx.yyy. All i need from these server names is the name before the first "." so in the first example all I would need is aaa. I've been using this formula in excel MID(A1,1,FIND(".",A1) - 1). This works great.. Is there any way to do this in access?