Parsing The Left And Right Characters Of A String

Aug 19, 2005

I have the following string: NE1220904
I need to remove the left two characters and the right four characters of a string in a query. I have found how to remove one side or the other, but not from both sides. Thanks in advance for the help!

View Replies


ADVERTISEMENT

Left Three Characters From String Using Query

Sep 8, 2005

In MySQL I can use the LEFT(3, field_name) command, but I essentially want access to pull all records from a table where the the first three digits of a number field match those pre-selected by a form drop down.

This database is in Access 97. Is there a way to compare the first x number of digits using SQL only, or do I need to run each line through code first, and then check it (I really don't want to do that)?


example numbe rin field:

123456789

SQL checks to see if 123 matches value selected on a form.

View 1 Replies View Related

String Parsing.....

Feb 7, 2006

hi, im new to both access and sql and was hoping i could get some help...

i have a string of numbers seperated by commas stored in my database. i need to count how many numbers there are in each string and then display all the results with less than 36 numbers contained.

am i able to do this with an sql statement, or am i likely to need vba??

(example string: 9.89007472991943, 12.3332061767578, 14.4694166183472, 16.287145614624, 17.5347270965576, 17.6327610015869, 16.1364498138428, ...)

thx in advance

View 1 Replies View Related

Parsing String Into Separate Columns Of A Table

Sep 3, 2007

:confused:

I have a Microsoft Access table with the following columns: A,B,C,D,E,F.
In first row of Column A, I have the following string value: "Al,Peggy,Kelly,Bud,Buck"
What I would like to do is parse this string as such:

Column B:"Al"
Column C:"Peggy"
Column D:"Kelly"
Column E:"Bud"
Column F:"Buck"

Is there a simple VB funtion to accomplish this?

View 1 Replies View Related

Queries :: Parsing String With Multiple Delimiters?

Aug 13, 2015

I have a field in our database with five distinct strings, separated by a delimited (a slash or "/").

I've been able to successfully write the formula to parse the first field:

ARL: Left$([pipeline]![Short Description],InStr(1,[pipeline]![Short Description],"/")-1)

The second field:

BRANCHMGR: Mid(Left([pipeline]![Short Description],InStr(InStr(1,[pipeline]![Short Description],"/")+1,[pipeline]![Short Description],"/")-1),InStr(1,[pipeline]![Short Description],"/")+1)

and the last field:

DATE: Mid$([pipeline]![Short Description],InStrRev([pipeline]![Short Description],"/")+1)

how to get the third and fourth strings parsed. I know its going to include some nested InStr or InStrRev commands -- just having trouble figuring out the structure. Could potential need a LEN command as well.

Typical field might look like this:

"Name1/Name2/Description/Update/Date"

View 7 Replies View Related

Parsing Through A String And Counting Number Of Occurrence

Feb 12, 2013

I have about 150k rows that look like this

SHORT_DESCRIPTION
unable to view
unable to send out some stuff
what is going on

Is it possible for access to query through each field, and tell me the total number of occurence for each word? In this small example, the output would be:

Unable 2
to 2
view 1
send 1
out 1
some 1
stuff 1
what 1
is 1
going 1
on 1

Is this possible?

View 6 Replies View Related

Left Function To Extract First Four Characters

Nov 27, 2006

I have seen variations of this question and have tried to modify for my purpose, but have been unsuccessful. I need to extract the first four digits of the Social Security Number. We no longer view the whole number, but a variation of the first four of social and first four of first name. I have tried many variations of the Left Function to extract the data from the field:

Expr1: SSN(Left([SSN],4))
Expr1: Left([SSN],4)

Etc.
I get various errors, mostly involving the comma. I am at a loss since I usually search for the code and find that it works. In this case, it doesn't work. I am also trying to extract the first four of the first name and will later combine the two although I broke it down since I couldn't get the basic "left" function to work.

Please help if possible. :)

View 11 Replies View Related

Importing Excel File - Characters Like < > Are Left Out

Mar 8, 2006

I have a problem whereby I want to import an Excel file into my Access db but certain characters like >< it doesn't accept. what other options do I have?

View 1 Replies View Related

Queries :: Left Function With 2 Static Characters

Jul 23, 2015

I have to query a record with 2 of the same static characters. "-".I can get the left function with the first " but I can't get the rest up to the second "-"

Example: B-4352B-PXP02W01-10

TagNo: Left([EventInstanceID],InStr([EventInstanceID],"-")) Brings me the first char plus "-" The next section is needed also but the remainder is not.

Needed: B-4352B

Is there a Mid Function that I could add to go to the second chaacter instead of stopping at the first one?

View 6 Replies View Related

Queries :: Remove Part Of A String From Right To Left

May 28, 2015

I have a manual date format that looks something like:

02/16/2015 09:06:15 AM PST

I would like to truncate the text so that just the date is showing. For all that I have found, I can remove a string from the beginning? any tips on removing a string from the end?

View 6 Replies View Related

Queries :: Create Join Using LEFT String Manipulation

Apr 28, 2015

I think I know the answer but want to check. I've been asked to create a query, without querying a query first, but it's the only way I know.

I have two tables

Table1 will have data in a column that is 9 characters long
ULCABC123
ULCABC124
ULCABC125
PLTABC123
PLTABC124

Table2 will have data in a column that is 6 characters long
ULCABC
PLTABC

Question: Can I create a Join from Table2 Field with the Left(Field,6) from Table1

I was thinking something like this. (but then I can't enter design mode)
Query1 - Test

Code:
SELECT Table2.ORDDETTYPE, Table2.DESCRIPTION
FROM Table2 INNER JOIN Table1 ON Table2.ORDDETTYPE = Left(Table1.ORDERDET,6)
GROUP BY Table2.ORDDETTYPE, Table2.DESCRIPTION;

I presume the only way to do this is first query Table1 (and call Query2) and return the first 6 characters and the create another query (Query3 in this case) using Query2 field joined with Table2 field.

Query2

Code:
SELECT Left([ORDERDET],6) AS NEWORDDET
FROM Table1;

Query3

Code:
SELECT Table2.ORDDETTYPE, Table2.DESCRIPTION
FROM Query2 INNER JOIN Table2 ON Query2.[NEWORDDET] = Table2.ORDDETTYPE
GROUP BY Table2.ORDDETTYPE, Table2.DESCRIPTION;

View 2 Replies View Related

Queries :: Alphanumeric String Manipulation - Move Data At The Left To Right

May 23, 2013

I have several thousands of lines of data which I wish to manipulate programmatically, if at all possible. I think that all of the possible permutations are summed-up by the following examples :

123 A text string
2-8 Another text string
A-C Another text string here
3-20 And some more text
3A-126B More text
Some text without any numbers or letters at the left

What I need to do is :

Move the alphanumeric data at the left to the right. So the data would end up looking like this :

A text string 123
Another text string 2-8
Another text string here A-C
And some more text 3-20
More text 3A-126B
Some text without any numbers or letters at the left

I suppose it hinges on identifying where the first space in from the left appears, cutting the string at that point, and

Is this possible, programmatically ?

View 8 Replies View Related

Modules & VBA :: Body Of Outlook Email - Left Justify String

Mar 5, 2015

I have the following string of text that I currently push out to the body of an Outlook email (using the DoCmd.SendObject function with an Access report):

Code:
sMsgBody = "Please find attached the specified Final Costing Report for WO# " & WO & vbCr & vbCr
sMsgBody = sMsgBody & "Dealer: " & rs.Fields(0).Value & vbCr
sMsgBody = sMsgBody & "Model: " & rs.Fields(1).Value & " : " & rs.Fields(2).Value & " : " & rs.Fields(3).Value & " : " & rs.Fields(4).Value & vbCr
sMsgBody = sMsgBody & "Margin $: " & rs.Fields(5).Value & vbCr
sMsgBody = sMsgBody & "Margin %: " & rs.Fields(6).Value & vbCr

Which is great and works fine! It appears in the email like so:

Original.PNG

Now, it has been requested that the data to be left justified to the widest line... so, in this scenario, it would look like this:

Edit.PNG

I've searched for ways this might be accomplished so it's always left justified to the widest line, but haven't come across any definitive solutions yet.

View 9 Replies View Related

Strip Last 21 Characters From A String

Sep 21, 2005

I need to be able to strip the last 21 characters from the following strings using a command in a query. Any ideas? I know I need to Instr command but not sure how to do it.

Greg
Impulse Manufacturng

96803-386868-386869-163241
INSP-F96191B-384761-389552-162206
INSP-F96191A-384758-389551-162205
F96191A-384758-389551-162205
F96191B-384761-389552-162206
INSP-97407-389462-389463-164428

View 5 Replies View Related

Want To Know How To Chop The Last 3 Characters Of A String

Nov 24, 2005

I am new to VB and want to know how to chop the last three characters off a string. Am writing dynamic SQL and have to take the AND off the last WHERE clause if needed.

Any help greatly appreciated

View 5 Replies View Related

Removing Characters From A String

Jan 5, 2005

is there a way to do this in VBA in Access I looked at trim but it does not help much

Dim MyString, TrimString
MyString = " <-Trim-> " ' Initialize string.
TrimString = LTrim(MyString) ' TrimString = "<-Trim-> ".
TrimString = RTrim(MyString) ' TrimString = " <-Trim->".
TrimString = LTrim(RTrim(MyString)) ' TrimString = "<-Trim->".
' Using the Trim function alone achieves the same result.
TrimString = Trim(MyString) ' TrimString = "<-Trim->".

I have a select statment setting the data that will be in a combo box
I would like to trim this data...

for example the select statment returns something like
category>subcatgory>field>subfield

so I want to trim it down to be
field>subfield

View 3 Replies View Related

Trimming Off First 7 Characters In A String

Oct 21, 2005

My DB is on SQL Server with an Access adp front end.

SQL uses the Windows NT user name and password for security and also pulls in the user name into a field in every record so we know who has added and modified it.

For one table we are actually using the user name field on the form and in a report for reasons other than security.

The user name is filled in with first the name of the network group, then a slash, then the user name itself. So there are 7 characters I don't really care about.

Is there a way in VBA to remove those first seven characters and only show what comes afterwards? Thanks for the help!

View 3 Replies View Related

Modules & VBA :: Cut Off First 6 And Last 2 Characters Of String

Mar 24, 2014

I would like to cut off the first 6 and last 2 Charaters in an after update event but not sure how, I cannot use mid as the length of the string may change but never the first 6 or last 2, can some one show me how it's done ...

View 4 Replies View Related

Characters Before Searched String?

Dec 9, 2011

Is there a function, similar to mid, except that it will return all characters prior to the searched string instead of after?

View 4 Replies View Related

Queries :: Replace 2 Characters In String With 0s

Dec 8, 2014

I'm familiar with writing a replace query to replace characters or strings but what I'm trying to do this time is a bit unique to me. I have a string of numbers that will either be 8 or 9 digits in length. The first 1 or 2 digits will be the State code (1-50 hence the discrepancy in number of total digits), then a 2 digit agency code, then the last 5 digits are a producer code. What I need to do is change the 2 digits for the agency part both to 0. So basically characters 6 and 7 if you're counting from the right. To me it feels like I'm trying to do it backwards so I'm having a hard time writing it.

View 12 Replies View Related

Strip A Filename String Of Any Disallowed Characters

May 8, 2006

I am writing some search results to an excelsheet for reports using the time and date functions to build a filename. I downloaded a StripSpecialCharacters() module, but it doesn't take them all out (only takes ascii above 127).

Does anybody know of another function to do it. searched the archive to no avail.

Thanks in advance

View 5 Replies View Related

Modules & VBA :: Check If String Contains Numeric Characters?

Nov 7, 2013

Is there an easy way in VBA to loop through a recordset and determine if the text string in a text field includes numeric characters?

I'm trying to do something like this:

Code:
Do Until rst.EOF
rst.Edit
If Left(rst!FldText,10) contains any numeric characters Then
rst!FldType = "Mixed"
ElseIF Left(rst!FldText,10) contains "PO" Then
rst!FldType = "PO"
Else
rst!FldType = "Std"
End If
rst.Update

View 2 Replies View Related

Queries :: Possible To Query A Field For Special Characters In A String?

Oct 11, 2013

Is it possible to query a field for special characters in a string? I want to find any records that have characters other than A-Z, 0-9

View 9 Replies View Related

Parsing

Nov 18, 2005

Hi

I have text file which contains different types of Record (identified by RecordType).Its a simple FLAT file having specific record structure for each type of record.

My requirement is
1.Parse the .txt file,identify record type
2.Based on corresponding record structure apply validation rules
3.Generate error report where data violates validation rules.

I am new to access . If any of u have sample code on how to parse any file and show error report accordingly, that will help immensely

Thanks

View 1 Replies View Related

Parsing Names??

Apr 11, 2006

I am trying to do the following
If any one of you have experienced Abacus ticketing system, each reservation is comprised of a group of names as follows

1.1 Gul/Mohammad Atif Mr 2.1 Gul/Mohammad Adil Mr
3.1 Bond/James Mr

I have to copy the above data from the abacus tcketing system into a text box.
My question is how can i devide the above three names into three seperate text boxes.

View 4 Replies View Related

Website Parsing

Mar 25, 2008

I need some assistance parsing some information from a web address. Here is an example of what the web address is.-http://www.websitename.com/ct20/pages2/client_bcphone_service_order.aspx?client_id=160399 4&contract_document_id=924182&cipid=206123444-What I am trying to do is get the client ID and the contract id. The client id being 1603994 and the contract id being 924182. Any ideas would be appreciated.Gregg

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved