Modules & VBA :: InStr (3rd Instance Of Character Required)
May 1, 2014
I use the following code to get the first and second instances of a "/" character. How to get the position of the third instance.
iUPC = "123-7754LF-(A/S red Top)-T19/97876564"
'get number of instances
xTimes = 0
xTimes = Len(iUPC) - Len(Replace(iUPC, "/", ""))
'get position of characters
xInstance1 = InStr(1, iUPC, "/")
xInstance2 = InStr(InStr(1, iUPC, "/") + 1, iUPC, "/")
View Replies
ADVERTISEMENT
Aug 30, 2013
How can I adjust my required field so that you cannot simply enter a space bar character to circumvent the requirement?
Here is part of the code:
If IsNull([txtRequiredReason]) Then
MsgBox "The Reason field is required"
DoCmd.GoToControl "txtRequiredReason"
Else
View 1 Replies
View Related
Sep 15, 2005
I need help with a required field that has different character lengths.
I have an input mask that covers the first 7 characters, however, the users are still entering the incorrect policy numbers, I need to put a stop to it.
I have 3 different policy number lengths involved, 7, 8 and 9.
They are different kinds as well, alpha, alpha/numeric and numeric.
Example below:
P###### - 7 characters
02###### - 8 characters
02M##### - 8 characters
NX####### - 9 characters
HX###### - 8 characters
HXL##### - 8 characters
HXM##### - 8 characters
My question is this -- do I have to create 7 different forms or can I program it to accept only the certain (policy#) characters above. Or can I create a query to insert into my form.
Any help is appreciated, Thanks as always.
View 3 Replies
View Related
Sep 15, 2005
I need help with a required field that has different character lengths.
I have an input mask that covers the first 7 characters, however, the users are still entering the incorrect policy numbers, I need to put a stop to it.
I have 3 different policy number lengths involved, 7, 8 and 9.
They are different kinds as well, alpha, alpha/numeric and numeric.
Example below:
P###### - 7 characters
02###### - 8 characters
02M##### - 8 characters
NX####### - 9 characters
HX###### - 8 characters
HXL##### - 8 characters
HXM##### - 8 characters
My question is this -- do I have to create 7 different forms or can I program it to accept only the certain (policy#) characters above. Or can I create a query to insert into my form.
Any help is appreciated, Thanks as always.
View 3 Replies
View Related
Jan 22, 2014
I have a check for lots of different data in a string and wondered if i can use CASE or similar.Sample code reads...
If InStr(1, Me.txt_sp, "Give & Take", 1) Then TM = "Standard"
If InStr(1, Me.txt_sp, "Give Take", 1) Then TM = "Standard"
If InStr(1, Me.txt_sp, "give and take", 1) Then TM = "Standard"
If InStr(1, Me.txt_sp, "Give and", 1) Then TM = "Standard"
If InStr(1, Me.txt_sp, "priority working", 1) Then TM = "Standard"
If InStr(1, Me.txt_sp, "priority boards", 1) Then TM = "Standard"
If InStr(1, Me.txt_sp", 1) Then TM = "2 Way"
View 12 Replies
View Related
Apr 27, 2015
In my developing application I am making use of searchboxes to narrow down the amount of records. On a form I have a textbox and a subform with a table connected. In the textbox I can type a character that will be used in an 'Instr' SQL query. I am using the code to query one field. (see code below) In what direction do I have to look to make this code usefull to search through two fields. In my case that will be Tag and Function.
Code:
Private Sub mnu3_txt_UnitbookSearch_Change()
Dim SQLstring As String
SQLstring = "Instr(Tag, " & "'" & Me.mnu3_txt_UnitbookSearch.Text & "'" & ")"
ReReadDescriptions SQLstring
[code]...
View 6 Replies
View Related
Jun 16, 2015
I want to that the WHERE clause for a SQL statement that I am using options on a form to build. I intend to use the clause in opening a datasheet form.
This is the code I have for getting the substring
Code:
Dim intPos As Integer
Dim tempString As String
Dim BaseQueryFormStr As String
'BaseQueryFormStr is used to reopen the BaseMasterQueryFrm with the specified parameters
tempString = "WHERE"
intPos = InStr(1, strSQL, tempString, vbTextCompare)
BaseQueryFormStr = Left(strSQL, intPos - 1)
MsgBox (BaseQueryFormStr)
The value of intPos remains=0 and when the program hits the second to last line I get "run-time error 5"
View 10 Replies
View Related
Oct 4, 2013
I have two problems excel instance does not close at the end and two when I try to do the looking using text it works but soon as I try using date/time it returns nothing.
Code:
Private Sub Command84_Click()
Dim objExcel As Excel.Application
On Error Resume Next
[Code].....
View 11 Replies
View Related
Dec 30, 2014
I just want to run a series of DoCmd.TransferSpreadsheet commands to export several queries to one Excel Workbook with seperate sheet names for each query.
However my problem is I want this to be a new instance of Excel that the user will then SaveAs after it is complete. I don't want to save it to a specific path first because the Db is on a shared drive and My Documents will have a different path for each user based on their user name. (corporate environment)
There must be some way to just have it open a new workbook without saving isn't there??
View 3 Replies
View Related
Nov 10, 2013
my code does the following, user selects excel file, opens it, renames sheets, basically needs first sheet to be sheet1. rest don't matter Changes the formats in column a to number and 15dp, saves the file as .xls and then links the file to the database.
Code:
Private Sub Command288_Click()
Dim s As String
Dim i As Long
[Code]....
View 14 Replies
View Related
Nov 20, 2013
what the difference between the two variations of using the New keyword to create a new instance and using the Set. I mean Code 1 would always be better because you dont have to ever use the Set keyword
1.Code:
Dim wb As New Excel.Workbook
2.Code:
Dim wb As Excel.Workbook
Set wb = New Excel.Workbook
Are there benefits and cons to both i.e. memory allocation etc..
View 8 Replies
View Related
Mar 21, 2014
How to set focus to a non Default instance of a form.
Environment
A2007 ADP Project
Document Window Option - Tabbed Documents
MS SQL 2012 Express DB
Windows 7 64 Bit
I using Allen Browne's method to open more that one version of form, storing each form object in a collection declared in a module. No problem there.
Now I'm trying to add a command button on an form to set focus to one of these non-default instances already open.
The form I am trying to set focus to has a the following related properties
Default View: Split Form
Pop-up: No
Modal: No
The form that has the command button on it is of the same type.
Here is the code I've tried:
'Code on Calling Form
Private Sub cmdProjectList_Click()
Dim FunctionResult As Boolean
If AppForms.GoToForm("ProjectList") = False Then
AppForms.Load_ProjectList
[Code] .....
The code compiles and executes with seemingly no problems. It finds the form loaded, then cycles though and finds the form in Forms but the SetFocus call seems to do nothing. When I run the code against a defualt instance ( one not opened using Allen Browne's method) it works fine and sets focus to it as expected.
View 11 Replies
View Related
Jun 7, 2013
I am trying to update a recordset using VBA based on the max "process instance" from another table. After the code executes, the field I am updating is still blank.
Code:
Set rs = db.OpenRecordset("myTable", dbOpenDynaset)
If Not (rs.BOF And rs.EOF) Then
rs.MoveFirst
Do Until rs.EOF = True
emplid = rs![Employee Number]
[Code] ....
View 5 Replies
View Related
Nov 17, 2014
I am using the method from allen browne [URL] .... to open a form and add it to a collection and when removing it it closes. actually, usually it does work so but i have now a form which does not close until i am hitting the reset button in VBE , is there something i could check why it's not functioning as desired ?
Just to add, this form has a subForm as well (might be the cause ?)
View 2 Replies
View Related
Dec 13, 2013
I will try and keep this brief. I have a paragraph of text, I have to paste it into a system which allows me 75 characters in a line and 208 in a page.
I have code which creates text boxes dynamically based on the total amount of characters/208 - this gives me how many textboxes need creating. I also have code which then populates those textboxes 208 characters at a time. I now need to alter it so it puts a line break every 75 characters.
Code:
Private Sub btnsubmit_Click()
DoCmd.OpenForm "NoteForm", acDesign
Dim x As Integer
Dim ctrl As Control
Dim y As Integer
[Code] ....
So I've achieved almost what I want, I just need to amend it so it starts a new line after every 75 characters...
View 1 Replies
View Related
Feb 3, 2014
L=Letter , X=number. Examples consisting of three parts (it can be more than three).
1. LLLLXXXXXX_LLLLXXXXXX_LLLLXXXXXX
OR
2. LLLLXXXXXX LLLLXXXXXX LLLLXXXXXX
OR
3. LLLLXXXXXX&LLLLXXXXXX&LLLLXXXXXX
OR
4. LLLL_LLLLXXXXXX&LLLLXXXXXX
OR
5. LL LLLLXXXXXX&LLLLXXXXXX
I would like to develop a function that takes a string that is made up of three parts or more and each part is separated by an underscore "_", space, or "&". There is no character that takes precedence over the other. It will always be only a single character which seperates the parts.
The check will always be done from the left. It should carry out the following actions:
1. Display all characters from the left until it reaches the first seperator "_" underscore OR "space" or "&"
2. Check only Part1 if it is made up of 4 letters and the rest 6 numbers (see 1-3 above). If that is the case only return part 1.
3. If part one only has letters and no numbers (see above 4-5) return all parts. Part one can consist of two letters or more.
I have started the function for step 1 above that searches for the character "_" underscore and display all characters until the first "_" is reached. I would like to include the same for "Space" or "&" in this function.
Once that is done I would like to do step 2 and step 3.
Code:
Function SearchforChar(strTest As String) As String
Dim test2 As String
Dim strUntil As String
strUntil = "_"
test2 = Left(strTest, InStr(1, strTest, strUntil) - 1)
[Code] ....
Answer3:
Full name with all parts should be returned for all above four cases under situation 3.
View 9 Replies
View Related
Jun 16, 2015
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)))
[Code] .....
View 9 Replies
View Related
Sep 3, 2014
In attached file i am updating the YU field in YABANCI_UYRUKLULAR table where CALISMA_IZIN_NO fields is matching from YU table. But there is a problem after update i see one of the record's YU field is 10 character, it must be 11 character as in the YU table.
View 11 Replies
View Related
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 ?
View 3 Replies
View Related
Jul 6, 2013
When I enter character in input box then show run-time error with this code.
Code:
Private Sub cmdDelete_Click()
Dim password As String
If Command49.Visible = False Then
password = CStr("0" & InputBox("Enter Password"))
[Code] .....
View 3 Replies
View Related
Jun 12, 2015
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
< (less than)
> (greater than)
: (colon)
" (double quote)
/ (forward slash)
(backslash)
| (vertical bar or pipe)
? (question mark)
* (asterisk)
. (full stop)
, (commer)
how i can do this in some sort of update query.
View 1 Replies
View Related
Apr 7, 2015
I need to automatically generate a 5-character value for my Business Key. Without any user interaction.
2 character -> from 01,02,03,04 to 05
+
3 character -> Sequential Number (001,002,003).
The Sequential Number must reset on each new day.
View 8 Replies
View Related
Jul 14, 2014
I'm populating my listbox from access but it doesn't like my SQL statement, where am I going wrong
sSQL = "SELECT * from Process where ParentName = '[cboCategory]'"
Process is a table and cboCategory is the excel form control.
The error is "No given value for one or more required parameters.
View 3 Replies
View Related
Jun 11, 2013
I have a list box that displays records. When i double click a record it opens the form needed.However I want it to open the form but default to a specific tab. But when it goes to the tab, the subform within it needs to match the ContractID of the record they double clicked on in the search form?I currently have this which opens the Client Form based on the ClientID of the listbox query search results and the client id of the main record.
Code:
Private Sub SearchResults_DblClick(Cancel As Integer)
If CurrentProject.AllForms("frm_Clients").IsLoaded Then
DoCmd.Close acForm, "frm_Clients"
End If
DoCmd.OpenForm "frm_Clients", acNormal, , "[ClientID] = " & Me.SearchResults.Column(0)
So in theory If doubleClick then open Client Form on Tab3 where subform Contract field ContractID is matches the ContractID in on the search results?
View 2 Replies
View Related
Nov 3, 2014
I have made a form based on related tables. it requires me to fill out every field, which I don't want. I didn't make them required. Why does it do that?
View 3 Replies
View Related
Jun 4, 2014
I have a form with three text boxes and i am trying to figure out how to ensure atleast one of these boxes is required and would error if none of these are completed.
View 1 Replies
View Related