Parse InStr "-"
Mar 13, 2008
Hi, i have a column with data in a query that looks like the following (p.s. sbenj, mehere,.. thanks for the advice earlier)
Code:Link200-1200-1-1200-1-2200-13-1
Now everything before the 2nd hyphen is considered the base number. I.e.
Code:Link200-1200-1200-1200-13
I need to parse into a seperate field everything before the 2 hyphen.
Now i've tried the following which i thought would work, which would enable me to concatenate expr1 & expr3 unfortunately it doesn't agree with 200-1 because it doesn't have a second hyphen,
SELECT Table.Link, Left([link],InStr([link],"-")-1) AS Expr1, Mid([link],InStr([link],"-")+1) AS Expr2, Left([Expr2],InStr([Expr2],"-")-1) AS Expr3
FROM Table;
Result from this is
Code:EXPR1 EXPR2 EXPR3200 1- Error#200 1-1 1200 1-2 1200 13-1 13
Any Thought?
View Replies
ADVERTISEMENT
Aug 8, 2007
Hi there,
I need alot of advice from you guys! Basically I have a table that contains
address data, and I want to isolate the country name - it may be either by
itself in a field or in a string.
To do this, I have created three tables:
one with supplied data containing address data (tblProcessData)
one with a list of countries (tblCountryName) and
one with a list of alternative country names (tblAlternativeCountryName).
tblCountryName has all the 'correct' country names (eg. UK) and
tblAlternativeCountryName has any other spellings of this (eg. United
Kingdom, Great Britain, GB etc. etc.) I have joined the two together using
the Primary Key of tblCountryname to a number field in the
tblAlternativeCountryName (one-to-many relationship).
My question is, what is the best way of isolating the country name in the
table? I was attempting to run an update query to find the country name
based on 2222 records, but when I went to run it as a select query, it comes
up with 142208 records, and the instr value is 0. Why is this?
SELECT tblProcessData.[6],
InStr([tblProcessData]![6],[tblCountryName]![CountryName]) AS Expr1,
InStr([tblProcessData]![6],[tblAlternativeCountryName]![AlternativeCountryName]) AS Expr2
FROM tblProcessData, tblCountryName INNER JOIN tblAlternativeCountryName ON
tblCountryName.CountryNameID = tblAlternativeCountryName.CorrectCountryName;
Any help would be hugely appreciated!
View 6 Replies
View Related
Nov 13, 2007
I need to bee able to query out all entries that are not like the following format S01-19-01-3. Users are entering incorrect data such as So1-19-o1-3, S0119-01-3, S01-19-01-3. Users are supposed to enter the data with One letter, 3 dashes and 7 numbers. If they enter any other way I need to be able to identify it with out searching through some 4000 records. Please help
View 3 Replies
View Related
Dec 12, 2007
I'm examining a previously written query and I'm trying to figure out exactly what the minus sign does when placed before the Instr function. An example of a query that successfully flips a name field is below and includes the -instr function. I've also included another query below this one that is much more simple and does the same thing. Thanks in advance for your help!!!
1)
EXERCISE1 SET EXERCISE1.NAME_A = IIf(InStr([Name],",")=0,[Name],IIf(InStr([Name],",")=InStrRev([Name],","),Trim(Mid([Name],InStr([Name],",")+2,20)) & " " & Mid([Name],1,InStr([Name],",")-1),Mid([Name],InStr([Name],",")+2,(InStrRev([Name],",")-InStr([Name],",")-2)) & " " & Mid([Name],1,InStr([Name],",")-1)));
2)
SELECT EXERCISE1.NAME, IIf(InStr([NAME],",")=0,[NAME],Trim(Mid([name],InStr([NAME],",")+1)) & " " & Mid([NAME],1,InStr([NAME],",")-1)) AS FLIPPEDNAME
FROM EXERCISE1;
View 2 Replies
View Related
Dec 11, 2007
Hello,
I am trying to write a query that will search a field for a string until it discovers a comma. If there isn't a comma I want the field left as is. If there is a comma I want it to grab all strings before the comma and then take the string after the comma and flip the arrangement to another field..ie (flipname)
example if a field has [Smith, John] I want it displayed as John Smith
Here's is the code I was attempting to use below, it generates syntax errors!
SELECT Exercise1.name, Iif(Instr[name],",")=0,[Name], Mid([name],Instr([name]),+1,instr([name]),",")-1 as expr1
FROM Exercise1;
Thanks for your help!!!!!!
View 10 Replies
View Related
Mar 6, 2008
I need to be able to take a list of instructions like this one, that is in a table, and have it break out in a query so that when I go to use it in a report it will look like the second example.
1) Set the oil out so that it is at room temperature. 2) Mix the oil and the alcohol together. 3) Place oil mixture on stove and bring it up to73 degrees. 4) Pour the mixture through a cheese cloth. 5) Add 1 cup
1) Set the oil out so that it is at room temperature.
2) Mix the oil and the alcohol together.
3) Place oil mixture on stove and bring it up to73 degrees.
4) Pour the mixture through a cheese cloth.
5) Add 1 cup
I have tried different ways of using instr in my query. I can get it to start the next line at any point, but I do not seem to be able to get it to stop when it comes to the next instruction. As a result I get something like this.
3) Place oil mixture on stove and bring it up to73 degrees. 4) Pour the mixture through a cheese cloth. 5) Add 1 cup
4) Pour the mixture through a cheese cloth. 5) Add 1 cup
View 7 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
Jul 22, 2005
Hi,
I'm having some problems with a simple query and it's driving me nuts. The invoice numbers in our system have hyphens in them, and I'm trying to find invoice numbers whose part before the hyphen matches a number I enter. Here's my SQL:
SELECT dbo_NIHB_ClaimLog.InvoiceNum, Left([InvoiceNum],InStr([InvoiceNum],"-")-1) AS InvoiceNumLeft
FROM dbo_NIHB_ClaimLog
WHERE (((dbo_NIHB_ClaimLog.InvoiceNum) Is Not Null) AND ((Left([InvoiceNum],InStr([InvoiceNum],"-")-1))=23316));
I'm getting a "Data Type Mismatch In Criteria Expression" error, and I can't figure out why. I've wrapped a Clng() around the Left() function, but that doesn't help either.
Am I missing something here?
View 5 Replies
View Related
Sep 27, 2007
I am having a bit of a problem with my update query. i have a field that shows a forename. i am importing data from an excel file. The forename populates with forename and middle names and they are seperated by spaces as opposed to commas. I have used the following InStr function in my update query however it works fine when the records forename field has a middle name but it deletes all data in the records forename field if it contains only one name which many do. How can I adjust the function to ignore those records that do not hold more than one name in the forename field. As you can imagine some forename and middle name combinations hold many names.
For instance the filad may have Ivor as a name which I would want to keep But if the Field showed Ivor Bigun then Bigun needs deleting.
Left([Forename],InStr([Forename]," "))
View 2 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 28, 2006
Hi,
I need to create a query that will parse a word and separate it into a list of letters.
I have had a go myself but am unable to do it... does anyone else know what to do?
Thanks.
View 1 Replies
View Related
Nov 11, 2005
Probably very simple but I cannot get the syntax right - it has been far too long since I used Access.
I am trying to seperate a field NAME from these formats:
LAST/FIRST M
LAST/FIRST
To two fields: Field LAST and field FIRST
Can someone please help?
Thank you! It is Friday and I want to go home!
View 1 Replies
View Related
Jun 24, 2006
I am tring to seperate a field using a query that has data like this:
02 04 08 18 20 25 31 32 35 39 41 45.... (10 sets of numbers)
into there own fields...
I cant seem to find something along those lines!
View 1 Replies
View Related
Aug 18, 2006
I am looking to parse names that go into a table in the field "FULLNAME", but I only want this qry to run if there is 1 space in the field.
UPDATE tblStandardLayout SET tblStandardLayout.FNAME = Left([FULLNAME],InStr([FULLNAME]," ")), tblStandardLayout.LNAME = Mid([FULLNAME],InStr([FULLNAME]," ")+1);
This would be because I dont want it to try to parse names that are not just "First Last"
View 1 Replies
View Related
Mar 27, 2008
I am a novice here so your patience and help is very much appreciated.
I am trying to parse text from a field.
Here's an example of the source data (all in "field1"):
Example 1
***** Regular *****
Time..08:19 DATE..03/27/2008
Request NO...20081306101
...and this is how I would like it"
New Field 1: Regular
New Field 2: 08:19
New Field 3: 03/27/2008
New Field 4: 20081306101
Because the values change substantially, I would like to be able to select the data in between two known text strings as opposed to using the MID function, an example of which I have successfully used to return "Regular" is:
MID([field1],InStr9[field1],"*****")+6,7)
But because the values change (e.g. "Emergency") I would rather have the end point based on the string "*****" rather than a count of characters (e.g."7")
Any suggestions?
View 3 Replies
View Related
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 4 Replies
View Related
Jun 21, 2013
I need to select the second word from a product description. Here are two examples;
2156015 Dunlop SP-30
1756514 Goodyear Sport
I found the correct code on another website to do this in Excel;
=MID([field], FIND(" ",[field],1)+1, FIND(" ",[field],FIND(" ",[field],1)+1)-(FIND(" ",[field],FIND(" ",[field],1))))
It works a treat. In order for it to work in Access you apparently change FIND to Instr.
Now this worked for the person on the other forum but when I try it, it doesn't and I get the #Error returned.
Here is what I am using. Description is obviously the field I am selecting from;
TEST: Mid([Description],InStr(" ",[Description],1)+1,InStr(" ",[Description],InStr(" ",[Description],1)+1)-(InStr(" ",[Description],InStr(" ",[Description],1))))
View 9 Replies
View Related
Dec 7, 2006
Hi All,
Can someone get me started in the right direction?
I have an unbound field on a form "txtScan". This field is populated by a barcode scanner.
The data looks like this ~Field1~Field2~Field3~Field4~Field5~Field6~Field7~ Field8~Field9
I am using ~ as a delimiter. Since I am encrypting the barcode, I can pretty much do anything to the barcode.
I need to parse
~Field1~ into txtFirstName
~Field2~ into txtLastName
~Field3~ into txtCompany etc.
A key point is that one of the fields may be blank so, if field 2 in the string is blank, "Field2" does not get populated.
I've never tackled anything like this before and don't know where to start.
Any help
TIA
View 2 Replies
View Related
Jul 9, 2015
I have a User Defined Type which stores an array..I have 2 instances of this UDT, one storing matched data, the other storing exception data..I was to parse these arrays to an Excel output routine. However, the parameter variable contains no data when accessing it..
Here is my code:
Code:
Private Type TOutputRow
RACFID As String
FullName As String
Access As String
LastLoggedIn As Date
End Type
[code]...
View 7 Replies
View Related
Aug 31, 2015
I am attempting to create a report and pass on the information from the current form to it. It is supposed to create a student ID and I am unable to get the information into the appropriate fields.I can get all the info from OpenArgs into one box but it wont parse it all out. The Form is within a navigation form. I know the below option is not the best way to do it, but other options I have found it has issues with sending when the form is within a navigation form and I prefer not to redo the entire thing at this point to remove the Navigation form.
Code:
DoCmd.OpenReport "rptStudentID", acViewPreview, , , , OpenArgs = "lblSSID|" & Me.SSID & "lblFirst|" & Me.firstName & "lblLast|" & Me.lastName & "lblDate|" & Me.dateAdded & "lblImg|" & studentList.Column(4)
The Parsing which is mostly just a copy from the above site
[code]....
View 6 Replies
View Related
Apr 26, 2006
I know there are many posts on this but still cannot find what I want ....
I have a string ....
... <surname>bloggs</surname> <fornames>Jane</fornames> etc.etc.
... <surname>williams</surname> <forenames>Jo</fornames> etc.etc.
In a query I know how to:
Find the Start and End Postions as follows:
StartSurnamePos: InStr([string],"<surname>") returns 19
EndSurnamePos: InStr([string],"</surname>") returns 34
I also know that by adding 8 to StartSurnamePos I can get Bloggs but how do I stop it there.
Using left, Right or Mid how do I pull out just
bloggs Jane
Williams Jo in seperate fields?
Thanks in advanced.
View 1 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
Feb 7, 2015
I have a question about errors on null value.I have made a small database for tryout, it has to be implemented in another one.And the small database is working.I have one table where there is one field called BatchInput.I scan a barcode into it and let two query's breaking it apart. I scan this batch into the table field
BatchInput: 20 MAY 2004H149-082-79 A4147011A05
Then I have my first query (Qrybreak1) extracting the date and deleting H14
Date: Left([BatchInput];11)
PartCertNr: Right([BatchInput];Len([BatchInput])-14)
The result is this:
Field date: 20 MAY 2004
Field PartCertNr: 9-082-79 A4147011A05
The second query (Qrybreak2) I look for the first space:
space: InStr([PartCertNr];" ")
Then with the result I cut it into two pieces
PartNumber: Trim(Left([PartCertNr];[space]-1))
CertNumber: Trim(Right([PartCertNr];[space]+2))
[code]...
And query (QryResult) even wont start, giving a popup with Invalid procedure call..How could I handle Null on the part where there is no space after the partnumber (missing Certnumber)?
View 7 Replies
View Related
Aug 26, 2014
I have Access 2010. If I run this query
Code:
SELECT Reference,
InStr(1,FunctionThatReturnsLongString(), ResponsiblePerson) As MyField
FROM MyTable
WHERE
(Reference ='ShouldBeOut'
OR
Reference = 'ShouldBeIn1'
OR
Reference = 'ShouldBeIn2')
It returns 3 rows, with values in MyField of 0, 23 and 355.
Now I add 1 more where clause to filter on MyField and the query looks like this:
Code:
SELECT Reference,
InStr(1,FunctionThatReturnsLongString(), ResponsiblePerson) As MyField
FROM MyTable
WHERE
(Reference ='ShouldBeOut'
OR
Reference = 'ShouldBeIn1'
OR
Reference = 'ShouldBeIn2')
AND
(
InStr(1,FunctionThatReturnsLongString(), ResponsiblePerson) > 0
)
So, you would think that it should return 2 values (ShouldBeIn1 and 2), but it doesn't. It only return the value where the Instr returns value of 23. The one with the value of 355 also disappears. Why would that be? Surely Instr does not return a byte?
View 10 Replies
View Related
Mar 29, 2007
I am having trouble parsing the month from a date like 05/12/2007 and I want to return the leading zero with the month. Example of 05/12/2007 and I want to display 05 only.
Please send any suggestions.
Thank You,
MJ
View 2 Replies
View Related
Sep 10, 2007
Here is what I need to do:
I have 1 table that contains groups responsibilities, there is a field Code-group that the initiator uses to track the groups & their % of impact to the overall project hours. Sadly enough he enters the data as such (EX:
PROJECT1 GROUP1-25%, GROUP2-50%, GROUP3-25%.
The problem is that he has varied instances of groups listed in each record & wants me to be able to parse the data out regardless of how many groups are listed. I know how to parse data when there are a specific number of groups what I can't figure out is how to parse data regardless of how many groups is in the field.
Any help is totally appreciated.
Phoenix
View 4 Replies
View Related
