Removing Excess Text--Problem With VB
Jan 27, 2005
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
Help??
-Siena
View Replies
ADVERTISEMENT
Apr 10, 2006
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?
View 5 Replies
View Related
Jul 30, 2007
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
View 2 Replies
View Related
Dec 7, 2006
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:
Me.[NOTES] = Now() & " " & Environ("UserName") & " - " & Text27365 & vbCrLf & Me.[NOTES]
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?
View 5 Replies
View Related
Feb 2, 2006
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:
Text1: IIf([Check325]=True,[Combo77] & " " & [Combo59] & " " & [Text65] & " " & [Text69],"") & " " & IIf([Check329]=True,[Combo78] & " " & [Combo61] & " " & [Text66] & " " & [Text70],"")
Text2: IIf([Check326]=True,[Combo102] & "" & [Combo188] & " " & [Combo91] & " " & [Text94] & " " & [Text97],"") & " " & IIf([Check330]=True,[Combo103] & " " & [Combo92] & " " & [Text95] & " " & [Text98],"")
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
Any assistance would be gratefully accepted.
View 3 Replies
View Related
May 23, 2006
Dear All,
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.
View 1 Replies
View Related
Apr 25, 2014
I have a list of consumables;
Syringe 50ml
Syringe 20ml
Syringe 5ml
Syringe Cap
White Needle
I want to remove only the number and the ml part from the list, so I would end up with;
Syringe
Syringe
Syringe
Syringe Cap
White Needle
If I use
PHP Code:
Replace([DrugNameVial],"50ml","")Â
I get the desired result for the 50ml syringe size.
I have tried every possible combination of "**ml", "##ml", "Like [0-9]ml all with no success.
How this can be resolved without having to individual enter each syringe size "5ml", "20ml" etc
I can't even just take the text from the right till the first space as this would lead to problems with other consumables in the list.
View 5 Replies
View Related
Jun 28, 2005
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!
View 2 Replies
View Related
Aug 18, 2005
I am looking to create a make table query to eliminate some portions of numbers from a field.
i have 23899999999
I need to parse 899999999 into a new field.
Is there a formular for that. Do I use a LEN formula.
View 2 Replies
View Related
Jan 25, 2006
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
THXS for Help
Steve
View 8 Replies
View Related
Feb 21, 2007
Hello,
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
and I want it to come out like this
DGName
P1000
P1000SMLS
P1000CA
UD000CB
V-NET
Thanks
Sean
View 2 Replies
View Related
Mar 15, 2007
Hi there I am extracting user names from e-mails in an access database.
I built the following command to do it
Left([E-Mails by Source - Test]![E-Mail],InStrRev([E-Mails by Source - Test]![E-Mail],"@"))
where [E-Mail] is the e-mail address.
It works great. But I still get the @ and the end of the name.
Any idea how I can get rid of it (other than using find and replace)?
Thanks for your help.
View 1 Replies
View Related
Sep 20, 2006
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?
thanks in advance
nel
View 4 Replies
View Related
Nov 1, 2006
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.
Thanks
View 3 Replies
View Related
Nov 20, 2007
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
results
client status date
123 A 9/3/07
View 3 Replies
View Related
Jan 10, 2005
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.
thanks
View 1 Replies
View Related
Apr 23, 2008
I have two tables: “tmptable” and “CC_CODES”
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
View 1 Replies
View Related
May 12, 2006
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?
View 2 Replies
View Related
Jul 5, 2005
hi
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
any ideas, as some will know i'm a newbie
View 1 Replies
View Related
Aug 14, 2006
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
Any suggestions appreciated
View 1 Replies
View Related
Nov 5, 2006
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
View 3 Replies
View Related
Mar 2, 2007
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?
Thanks in advance
View 4 Replies
View Related
May 21, 2007
Can this be done I dont really need the scroll bar in one of my subforms, can I remove it and prevent users from using it?
View 1 Replies
View Related
Oct 3, 2005
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.
View 2 Replies
View Related
Nov 29, 2005
Hello,
I want to remove the first four characters from a field in a query.
The data are not always the same length, but it will always be the first four.
Example.
vmc_ml120e21/p -> ml120e21/p
Is that possible and how??
Thanks in advance.
View 6 Replies
View Related
Nov 29, 2005
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?
View 1 Replies
View Related