Modules & VBA :: Code To Find Particular Character In A String
Aug 29, 2014
I've got a string variable with a value that could be typically
"ABCD|123|R"
The string needs to be split into its three parts, the pipe symbol being the separator. Then the middle numeric string must be converted back to a long.
I know i could do a loop which identifies each character in turn, but my question is:
Is there a VBA function that can pick out the position in the string of the "|" characters, so i dont need a loop ?
I have a program that has been working for a year or so, it imports records form a text file created by a web site. Someone has entered a hex("1A", "A8" in a name field. It is shutting my program down. I tried a alphanumeric check to replace it and a length check to try and skip the record but the program thinks it is at the end of the file and quits reading records. Is there a way to get rid of characters like this in vba?
"5407 MUYF3I23 GIS..LE "
Here is what I have tried
Code: If Len(strread) <> 591 Then MsgBox "Invalid Record Read " & strread GoTo Skip_Loop End If If AlphaNumeric(CStr(Trim(Mid(strread, 32, 11)))) Then !HT_FName = CStr(Trim(Mid(strread, 32, 11)))
We have a spreadsheet that i upload onto a table, i use on of the fields to build a file path but if there is a special character it causes problems,i need to replace the below with spaces if they are in my field called path
I have a command button on a continuous form(form 1) and I need this button to open another form(form 2) when I press on it. So far so good.
When I press the button, I need some VBA to open the form(form 2) , search for a particular table name based on the open form(form 1) current record and use that table name as the newly opened form (form 2) data source. I have ways to do most of those task but for one thing:
How do I make access search for a table name containing a particular string? Here's what I am working with:
Code: Private Sub Commande26_Click() On Error GoTo Err_Commande26_Click Dim stDocName As String Dim stLinkCriteria As String Dim stDataSource As String
I am looking to find if a field contains a number and then build a case statement depending on which number is found. The field will contain data just like this:
Quote:
Repaired frequency response and grounding issues. Replaced 2 Hybrids, capacitors, and connectors. Tested MER/BER and operation to specs.
Here is my code that did not work:
Code: If Me.txt_work_comm1 Like "*Hybrid" Then 'Sets up auto priced based on number of hybrids entered '1 hybrid If Me.txt_work_comm1 Like "*1" Then strCriteria = "repair_item = 'Charter RF Amplifier Repair + 1 Hybrid' AND profile_types = 'Alpha'"
[Code]....
To Summarize: 1. I need to find if the word "Hybrid" or "Hybrids" is in the field 2. Then I need to know how many to determine a price
I am trying to use a textbox value to call vba code and can not get it to work.
I keep getting an error on the call str1 line.
Code:
Sub formscript() Dim str1 As String str1 = [Forms]![fscripts2]![t3] 'MsgBox str1 If str1 = "" Then str1 = "err1" Exit Sub Else Call str1 ' this is where it call the script based on value in textbox End If End Sub
I am trying to add 30 or 45 days to a certain date and insert that date in a table. The field is declared as datetime on the SQLServer and has been tried with all date formats, and none, on the access side.The problem I am encountering is that the date is being inserted as 12:00:00 in the table, no date is being stored.When I print out the SQL statement to the immediate window, the date in question shows the correct date.
Code:
Dim a As Date Dim b As Date a = DateAdd("d", 45, Me.DateOfExit) b = DateAdd("d", 30, Me.DateOfExit) strSQL = "INSERT INTO MyTable ( No, Name,DueDate, OriginatedBy, ID, Status) " & vbCrLf & _ "VALUES (" & Me.No & ",'" & Me.Name & "', " & b & ", '" & Me.Facilitator & "', " & Me.ID & ", '" & "Required" & "');"
I have tried adding the date add code to the SQL string, but that produced the same result.
I found code at this website that is supposed to determine if a specific program is running on your computer. I have a couple questions about the code here. One, when I run it just the way it is and call it in the immediate window with the ?fIsAppRunning("word") command, it tells me "True" whether I have Microsoft Word open or not.
My other question is this: After the code is working properly, how can I add new programs to it? There are two programs the end user might need open to complete a process. One is called Extra and one is called tn3270 (this is how they show up in the Processes tab of the Task Manager). I think in order to add them to this code, I need to know a "class" to associate with them.
I'm trying to search a for string within a subform to find information stored on the mainform to which the particular subform belongs.
The problem is that the subform is generated from a query which uses a number from the main form to generate.
So the subform record is only generated when the correct mainform record associated with it is loaded.
Now to solve my problem I've made a new query that brings up ALL the results that could be generated by the main form and from that I can search to find my search term I'm after and read off the ID number to tie it back to the mainform.
But all of this is done manually, I want a way to do all this using VBA in a way that the user can't edit any records as they are doing it.
I have the following code that creates a long string that works most of the time but sometimes gets cut short. I can't understand why it does this. When it cuts the string short it cuts it short in the same place. Everything gets in the string up to/or about the following code '</Practice Name>'This string is needed to upload info to a server.
How to get the last character of the string in query?
I have a table name PlateNo and I want to get the last character for registration purposes.
Ex. ZMD-123 - I want to get the number three(3) and if its possible every time the user types the last digit in criteria it will show all plate number ending in the entered number.
I have a weight field that I've been populating with the weight and the unit of measure. I want to seperate those into 2 different fields. I've created a new field called UnitofMeasure and now I need to go back and remove all the instances of 'oz' and 'lbs' from the first field.
I have a string where alphabetical and numerical characters are mixed up. I need a string function where I can find the index of the first non-numerical character. I tried with the mid function but could not get it to work.
I have a file that I need to import on a regular basis. There are two different issues with the import:
1. the first digit in the file indicates if it is a record I want to keep. In this case a 1 indicates a repair record and an 8 indicates it is just information such as email, contact info etc.
2. I have a field in the file that may change the import specifications
For example:
If the record is a Non wheel repair then column 167 is a 50 character description If the record is Wheel repair then column 167 is a 28 character description and the remaining 22 characters are broken down into 10 different fields
So I need to do the following
1. Read the first character in the line and determine if it is a number 1 and if not discard it 2. Read a field in column 109 that is two characters long and if it says it is a wheel repair the import will break column 167 -216 down to the appropriate 11 fields and if it is a non wheel repair it will import columns 167 - 216 as one big description field.
I have used the import specifications with Access but it seems this will have to be done in code in a module or something and I am not very good with VB. (only know the basics)
One other issue is I have dates in the file that are 130225 and 1302 (so full date and then Year/Month) if i take out the / date separator in the import spec the full date works but the Year/Month doesn't.
I have a form where I want a textbox [txtMaxOrdLimit] to be visible only if another text box on the same form [PaNumber] contains the letter D in the string. This is the code I have on the forms On Current property but I'm missing something because textbox [txtMaxOrdLimit] doesn't show on the form at all.
If Me.PaNumber = "*D" Then Me.txtMaxOrdLimit.Visible = True Else Me.txtMaxOrdLimit.Visible = False End If
I need a query to find all the field header names that contain string "PL-" and along with records contain a value with the field name containing "PL-" within a Access DB table.
I would like to use code to change the connection string for linked tables in my front end database. I have tried using the connection property of the TableDefs collection, this seems to work while I step through the code but does not change the existing connection property. Any suggestions?
I have the code below which is giving me grief as I have copied it from a previous usage and it worked fine. I have tried it in a beforeupdate and now as a seperate button on the form and it gives me the same error message; I have tried to put a save command in to save the record without any luck
the code is
If DCount("[Batch]", "tbl_Data_Wine_Batch", "[Batch]=" & Me.Batch) > 0 Then Cancel = True End If
The error message is Run time error 2001 you cancelled the previous operation.
The problem comes up in the dcount expression.
Any help would be great as it is an important part of the project and we can move on once we have overcome this
I'm having a small difficulty in using the code example ghudson posted for Browse [Find a directory or file]. First off, the code works great. Thanks a lot for posting it!
The problem I am having is when linking a folder via dBrowseDirectory. The folders I am linking are on a shared network drive and I am trying to figure out how to set the initial directory to default to the networked PC. This way the user doesn't have to navigate to the shared drive every time. All they would have to do is navigate to the particular folder to link (2 clicks instead of 10+).
The dBrowseFiles function allows to set an initial directory, however I cannot figure out how to set it for dBrowseDirectory. Is there any way to do this by altering your code for dBrowseDirectory?
