Queries :: Extract 3 Numbers From A String

Jul 11, 2014

I am trying to extract the first instance of 3 numbers from a string using an update query to update another field in the same table(Master Equipment) titled "Code". the field is called "Equipment". the strings vary in length as well as the location of the 3 numbers needed, but I have examples of all possible locations the 3 number series could be located below. As you will notice, some fields do not contain 3 numbers together, for these I don't want to return a value.

07-FLP58351
07-MFDP58201
1PBE97601
1PT91105-2
2WPF/FF438582
A-WR-1
AAV58101-01
AC47201-01

View Replies


ADVERTISEMENT

Queries :: Truncate And Extract Value In String - Delimiter?

Apr 14, 2014

In Access 2007, in a table “Source” -> "UserName" column, I'm trying to truncate and extract value in the string:

a. Value within the parenthesis preceded by "".
b. Value outside the parenthesis preceded by “”

“Source” table contains the column ‘UserName’
“Final” table contains the columns ‘UserName’, “Expected_Output_1” and “Expected_Output_2”.

I tried using the following expressions to extract the output results, but unfortunately there’re not displaying the results in correct format.

For extracting results of “Expected_Output_1” I used: Mid([UserName],InStrRev([UserName],"")+1)

For extracting results of “Expected_Output_2” I used:

Mid(Mid([UserName],InStr([UserName],"(")+1,InStr([UserName],")")-InStr([UserName],"(")-1),InStrRev(Mid([UserName],InStr([UserName],"(")+1,InStr([UserName],")")-InStr([UserName],"(")-1),"")+1)

Attaching the database for your reference.

Expected results should match the snapshot attached or table “Final” in the attached DB

Example:
UserName = C103E000001 then the “Expected_Output_1” = E000001, “Expected_Output_2” = nothing

UserName = NAHoM\_INTRA_MTS (NAHoMDNAGAIntranetAdmin) then the “Expected_Output_1” = _INTRA_MTS, “Expected_Output_2” = DNAGAIntranetAdmin

View 1 Replies View Related

Queries :: Using Query To Extract Data Based On Numbers

Feb 18, 2014

I have a table with a field named BinNo which has a list of items with a bin no in the format 1.234( this number can be any number up to 6000.9999).

Is there a way for me to have a field on a form where I enter a number (i.e 1) and it lists all the items with a 1 before the decimal point but not 10 or an other number with a one in it. If I enter 10 then it will only give me the items with 10 before the decimal point and not 11 etc.

I have a query that pulls all the data into it but I only want the query to show the items relating to the number I enter into the form field.

View 2 Replies View Related

Queries :: Stripping Letters From A String To Extract Information

Feb 15, 2015

I have a need to strip of letters from a string but i needs to look for / as the length would change, below is an example of the data I am working with

record 1 example ) REP/1349/999/426066/XX/9
record 2 example ) REP/UDKBS01N/1/448174/XX/

what i need to extract is

1) = 1349
2) = UDKBS01N

I need to get the information between the first / and the second / is there a function in access to get this.

View 5 Replies View Related

Queries :: Returning Set Of Numbers From Custom Formatted String - Incorrect Use Of Join?

Mar 20, 2015

I suspect my design is flawed

Code:
SELECT tblData2.Prefix, tblData2.LineNum, tblData2.Year, tblComments.comment, tblComments.Address
FROM tblData2 LEFT JOIN tblComments ON tblData2.LineNum = (NumbersOnly([tblComments].[Address])
WHERE (((tblData2.MyYear)<1990))
ORDER BY tblData2.LineNum;

The NumbersOnly function returns a set of numbers from a custom formatted string. The above fails on the join (I think) but maybe there's another way of doing it?

View 4 Replies View Related

Modules & VBA :: Separate Numbers And String From Alpha-numeric String

Jun 7, 2013

MS-Access VBA code to separate numbers and string from an alphanumeric string.

Example:

Source: 598790abcdef2T
Output Required: 598790

Source: 5789065432abcdefghijklT
Output Required: 5789065432

View 13 Replies View Related

Extract A Number From A String?

Sep 16, 2013

I do have a module to extract a number from a string :

Public Function ExtractedValue(strSent) As Double
If IsNull(strSent) Then
Exit Function
End If
Dim strTemp As String
Dim intLoop As Integer

[code]....

My problem is that the string I do have is like:

19.0 EA
195 PAC
42 M
150 L

the problem is that when the number is like 19.0 EA the value I get is 190, in reality it should be 19. The rest is fine as there are no dot's.

View 3 Replies View Related

Modules & VBA :: Extract The Text Within The Brackets From String?

Nov 23, 2014

I have a column containing records of the timestamp of an event. I need to extract the date out of each of these records and put them in a separate table. The date and time of each record is contained within a bracket.

E.g.
ES~1~1412179200(Oct 02 00:00:00 2014)~1~ITM_W64MQMONITORLOG00~
0~0~ES~1~1412179203(Oct 02 00:00:03 2014)~1~ITM_Log_Entries~
0~0~ES~1~1412179204(Oct 02 00:00:04 2014)~1~ITM_Log_Entries~

As you can see, the number of characters of each record are different, so I am unable to do the Left$ count thingy.

Is there any other way to do it? I only need the date and time contained in the brackets.

View 2 Replies View Related

General :: Extract Text And Numeric From A String

Feb 12, 2013

"Att Mr/Mrs Vezi Your DEFAULTED account has been handed over to XXX. Payment DUE IMMEDIATELY Ref 1315519509. Tel 0009377500"

I need to extract the word "Ref " plus the number that follows it from the above string. The problem is that the word "Ref " is not always in the same place and the number following it is not always the same length. (ie it could look like "Ref 123456 " or "Ref 1234567 ") The only commonality between the records is that the number will always follow the word "Ref ".

View 1 Replies View Related

General :: Extract Postcode From String - No Other Filtering

Dec 31, 2014

I have tried to find this on the forum but it seems all the answers are to do with manipulating the strings based on what the postcode is whereas I just want to extract the postcode as a separate string as simply as possible.

Basically, I have a text field called Add5 which has the last line of the address including the postcode.

Example: "Northampton NN1 7PQ"

I am trying to end up with two strings like this

Add5 = "Northampton"
Postcode = "NN1 7PQ"

I only need to do this in one place, I don't think it needs a module.

View 5 Replies View Related

Pulling Numbers From A String

Feb 12, 2006

I have a column of data set up as follows:

Review July 2003
Review October 2003
Review July 2004
Review October 2004
Review July 2005
Review October 2005...and so on.

I know the data is not normalized, but if I want to use the Year part of the string in a WHERE clause, how would I do that? or in other words how do I extract the Year from the string.

Thanks

View 5 Replies View Related

Join On Fractional Numbers/String?

Jul 6, 2006

Greetings all:

I have a database tracking customers and inventory, and I've tried to add in a feature where I can meet customers needs with future inventory. Key to this process is three tables:

tblCustomer - containing my customer information
tblInventory - with inventory data including a part number
tblNeeds - a table I've created for this purpose, containing CustomerID and Part Number.

Here's the premise: when speaking with a customer, I may learn they want a product I don't currently have in inventory, or may have never had in inventory, but that I may get in the future. I do this "on demand" in the form of a report, and it works really well ... except:

I have found that several similar products from the same supplier have similar product part numbers. What I would like to do is be able to match my query on a fractional part of the part number. In all cases, the part number would be the same over the first six characters (in a text field). Any ideas how I might do this? In my current query, I use a join, but I can't do that for fractional strings....

I've attached the query I am currently using (and running a bound report from this query):

SELECT DISTINCT tblCustomer.CustID, tblCustomer.CoName, tblInventory.ProductDescription, tblInventory.ProductPartNumber, tblInventory.UnitsAvailable, tblInventory.ProductID
FROM tblInventory INNER JOIN (tblCustomer INNER JOIN tblNeeds ON tblCustomer.CustID = tblNeeds.CustomerID) ON tblInventory.ProductPartNumber = tblNeeds.PartNo
WHERE (((tblInventory.UnitsAvailable)>0));

And what I want to do is match the tblInventory.ProductPartNumber on the tblNeeds.PartNo by matching only the first six characters....

Thanks in advance,

Don

View 3 Replies View Related

Modules & VBA :: Pull Numbers From String And Get Max Value?

Jan 3, 2014

I have a series of IDs in an 'articles' table stored as text, e.g.

hb-123456789-e-068
hb-123456789-e-0069
hb-123456789-e-70
hb-123456789-e-00027

and I'm trying to pull the max value of the number after the -e- for a given set of them. In this example, I'd want to return the number 70. I'm then going to use that to create the next ID and populate another field.

The IDs are not used as the primary key. And while the previous IDs used leading zeros inconsistently, new IDs will not have leading zeros.

Here's what I have so far, but it doesn't seem to pull the number after the -e- at all. I think this section here is the problem, even though the same logic works in a query:

Code:
selectedERef = Val(Right(rs![masterArticleID], Len(rs![masterArticleID]) - InStrRev(rs![masterArticleID], " - ")))

Code:
Public Function MaxArticleERef(hbID As Long) As Variant
On Error GoTo err_handler
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSql As String
Dim maxERef As Variant

[code]....

View 3 Replies View Related

Modules & VBA :: String Into Numbers - Using Array For Lookup

Jan 15, 2014

I have to decode a string into numbers and to avoid to find out the values for 47 options by select case I though about an array.

I want to decode

Number Letter
10 A
11 B
12 C
13 D
14 E
15 F
16 G
17 H
18 I
19 J
20 K
...

For example the string "ADEG" would give as result
10 13 14 16

So I would have to loop through the string and "decode" each letter into a number.

As I have still problems to understand array, need to define the dimension of the array, it has fix 47 entries to decode

Dim myarray (47,2) as variant
mayarray=(10,"A",11,"B",...)

Correct?

View 10 Replies View Related

Queries :: Search For Multiple Plot Numbers Preferably In One Parameter Prompt With Comma To Separate Numbers

Aug 12, 2014

I'm having multiple problems with my database like things such as -

i'm currently working on the Query 2 - On the Phone database (ignore Query 1) and i want to search for multiple plot numbers preferably in one parameter prompt with a comma to seperate numbers. (this could be a multitude of numbers so i would like to be able to input as many as needed). Also when i do search on this query since the Criteria is a 'Between' Value i would expect everything between the 2 numbers input to show up - but a lot of numbers out of the range show up too - why is this? (The Numbers are like "69 to 136" and they will show up - but 1-69 and 136-170 would too

I would also like to implement the search results from Query 2 into the Form i currently have made but it just opens up a access table when the search is made?

i cannot link my database as it is too big for the server - But here are the Criteria for Query 2:

Plot No - (criteria = Between [Enter First Plot No:] And [Enter Last Plot No:])
Site - (criteria = Like "*" & [Enter Site:] & "*")
Product - (criteria = Like "*" & [Enter Product:] & "*"

The Query is the one im most concerned about , i can live without a form.

View 14 Replies View Related

Modules & VBA :: Define And Setup Array - Find Respective Numbers For A Text String

Apr 21, 2015

I need to find the respective numbers for a textstring when for

abcdefghijkl stand the numbers
79 81 82 83 84 85 86 87 88 89 91 92

The textstring to "decode" is for example is 'adgjk'

The result (79 83 86 89 91) should be added into a table by Looping.

rs.Addnew
rs("Letter")= myarray??
rs("corNumber")= myarray?
rs.update
rs.movenext

Something like this.

But I cannot define and Setup the Array, which should be the best way for doing this.

The Array does not change its Content nor its Dimension.

Both, letters and numbers are strings.

View 14 Replies View Related

Queries That Extract Repeated Item

Nov 14, 2007

I'm trying to set up a query where it returns me only the items that are repeated.

ie...I have a table that has clients that are one time clients and other that are repeated. Each client has a client number. I would like to get only the clients whose client number appears more than once. Is that possible?

View 1 Replies View Related

Queries :: Extract Max Date And Time - How To Get Corresponding ID

Jan 2, 2014

I have a (simplified) table with

ID | Date | Time | machine | value
304 1-1-2014 06:00 115 0.54%
305 1-1-2014 06:00 111 0.56%
306 1-1-2014 07:00 111 0.52%
307 1-1-2014 07:00 115 0.53%
308 2-1-2014 07:00 111 0.56%
309 2-1-2014 07:00 115 0.58%
310 2-1-2014 06:00 111 0.54%
311 2-1-2014 08:00 115 0.53%

I try to find the ID corresponding to the maximum date+time grouped by Machine.
In this case I would like to find 311 (maximum date/time for machine 115) and 308 (maximum date/time for machine 111)

The way to extract the maximum date + time is not difficult

SELECT Max([Date]+[time]) AS datevalue, tbl_TexControl.Machine
FROM tbl_TexControl
GROUP BY tbl_TexControl.Machine;

But how do I get the corresponding ID? Not with Max([ID]) because then ID 310 is selected which is not the maximum of the time.

View 5 Replies View Related

Queries :: Extract Names From Full Name?

Dec 17, 2013

Model Full Name: "Jones, John P." (this was the one not in MS' examples)
Last Name Extract = Jones
lastN = IIf(InStr(1,[Model Full Name],",")=0,"",right([Model Full Name],len([Model Full Name] - (InStr(1,[Model Full Name],",")+2)))First Name Extract = John
firstN = IIf((InStr(1,[Model Full Name],",")+1)=InStrRev([Model Full Name]," "),Right([Model Full Name],Len([Model Full Name])-(InStr(1,[Model Full Name],",")+1)),Mid([Model Full Name],(InStr(1,[Model Full Name],",")+2),(InStrRev([Model Full Name]," ")-1)-(InStr(1,[Model Full Name],","))))

View 1 Replies View Related

Queries :: Extract Data From One Field To Another?

Jun 17, 2013

Full Name, Forename, Surname, Salutation. This data has been extracted from another database where the Surname field was not required but it is now. So i need to find a way to pull data from the salutation field into the surname field where salutation has data but surname is null. The next one when surname and salutation are null but full name has data i need to pull that across.

View 11 Replies View Related

Queries :: Run To Extract Some Values For Calculations

May 12, 2015

I have a query that I run to extract some values for calculations. Its run on an event based on a dropdown selection. At the moment, I run via:

Code:
DoCmd.OpenQuery "CallMidprice"

and then close it. this seems to take more time than is necessary when I only want to refresh the results.Open query without close, switches to the table but doesn't update until I press F5.When I try requery I get an error. "Runtime error '2109': There is no field named 'CallMidprice' in the current record.

Requery without 'Callprice' seems to requery my form.

View 5 Replies View Related

Queries :: Extract Text Between First And Second Character

Aug 30, 2013

i would like to make a query in ma access can extract text between first and second character "/" and when there are not "/" in field it returns null.now data in my table are like below

Quote:

No option1 1 100 2 145/Mechanical/0800 3 120/electrical/1620 4 131/mechanical/0200/dw-001

Now I like to make a query can extract text between first and second character "/" like below: No option1 discipline

Quote:

1 100 null 2 145/Mechanical/0800 Mechanical 3 120/electrical/1620 electrical 4 131/mechanical/0200/dw-001 mechanical

View 1 Replies View Related

Queries :: Extract Day From The Date And Return A Value

Feb 20, 2014

I have a list of dates on which tasks were performed.

I want to be able to see if there is a pattern, i.e. lots done on a Monday

Can I extract the Day from the date, and return a value Monday, Tuesday, Wednesday etc??

View 3 Replies View Related

Queries :: Extract Text Between Certain Characters

May 14, 2014

I have a field that contains the following data:

FirstName:Pedro LastName:Campos Restaurant:BI Strand Location:North Month:March

I need to extract the data into different fields like this:

Field1: Pedro
Field2: Campos
Field3: BI Strand
Field4: North

How to extract the information I want into different fields

View 1 Replies View Related

Queries :: Extract One Record Per Unique ID

Feb 19, 2015

I have a table like so:

Code:
PatientID VisitDate Complaint TestPos
1 4/5/2003 Coughing 1
1 1/2/2007 Sneezing
1 5/1/2008 Unknown 1
2 2/1/1988 Unknown
2 4/2/1988 Unknown 1

I'd like to extract just one TestPos record (TestPos = 1) per Patient ID. And I always want to select the record with the earliest date. So the result would be:

Code:
PatientID VisitDate Complaint TestPos
1 4/5/2003 Coughing 1
2 4/2/1988 Unknown 1

I can do this but it requires 3 queries: a SELECT query where TestPos = 1, a GROUP BY PatientID query and MIN(VisitDate) to get the desired record per patient, and then another query that links the two so I can get the additional variables (e.g., Complaint).

Is there a way to do this without using 3 queries? Seems inefficient.

View 3 Replies View Related

Queries :: Extract Data From Query

Sep 15, 2013

I am trying to extract data from a query. Part of code when form loads, my questions is bold:

'query criteria
vOrgCode = Me.tOrgCode
'sql statement
strSQL = "Select tblMembers.tOrgCode, tblMembers.tGender, tblMembers.nMonthlyIncome, "
strSQL = strSQL & "tblMembers.nFarmSize, tblMembers.tPrimeCrop, tblMembers.nNoHills "
strSQL = strSQL & "From tblMembers Where tOrgCode = '" & vOrgCode & "'"

[code]...

i cannot build a simple query and save it as reference since the number of organizations may be 25 at the minimum. this is why i want to build it on the fly, so to speak.

View 3 Replies View Related







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