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 Replies


ADVERTISEMENT

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 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

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

General :: Excel Numeric Data To Be Imported As Text In MS Access

Aug 1, 2015

Attached in the ZIPPED file is an Excel spreadsheet.

Columns A is all numeric, and needs to be represented in access as a text field.
Column B is a mixed format of dates entered and in some instances only plain numeric. I need to import this column as is into a text field in access.

I tried importing the excel sheet, but the data gets changed.I tried to linked the Excel sheet but it also had an influence on the data.In both cases the influence of change is NOT throughout. Hence my need to get this spreadsheet into access as is.

View 7 Replies View Related

Forms :: Sort Numeric In Alpha-numeric Text

Mar 11, 2014

I have a text field having data i-e HO-1, HO,2, ACW-25 and so on. The field name is nBadge_num and is Unique. The data in this field is sorted automatically like 1, 10, 11, 12, 13, 2, 3, 4, 5...because this is the text field.

The number on the form is automatically generated, when the user type HO- for example on field exit event. The last number will generate like HO-5.

Code for automatic number generation is:

Dim dbs As Database, rst As Recordset, Response
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT Max(Right([nBadge_Num],Len([nBadge_Num])-" & Len(Me.NBadge_Num) & ")) AS MaxNo " _

[Code]....

My problem is when the number is generated it give HO-5 instead of HO-14, How can I sort the numeric part of the field ?

View 2 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

General :: Populate A Text Box Based On String

Sep 12, 2014

I have a select query that holds anywhere between 1 to a couple hundred records, I need to take the Name field and populate a textbox which after verification I write to a text file that I later use in a powershell script. So far have tried using VBA to create a variable and populate the textbox based on the string but it only provides me the first item, I've tried dmax and this gives me a single result last Item, Dlookup only gives me the first item, changed it to a listbox which populates the box correctly but only writes the selected item in to my text file.

View 7 Replies View Related

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 13 Replies View Related

General :: Convert Text String To Date Format

Apr 10, 2013

I am connecting acces to oracle servers via ODBC .

In the table there is a date time stamp - format data type text:

20130225060621

I would like to convert so that it is recognised as a date so I can calculate against e.g. Now() or another standard date format.

this is what I am currently using (which is OK) BUT I need to calculate against the time also.

Current Check Point Date: CDate(Mid([DZ_ZPKT_AK],7,2) & "/" & Mid([DZ_ZPKT_AK],5,2) & "/" & Mid([DZ_ZPKT_AK],1,4))

I spoke to an IT guy who works in TOAD program and he gave me this SQL:

select to_date(dz_zpkt_ak,'DD/MM/YYYY HH24:MI:SS')from orders where ordnr='4411310';

this is the format I would like but cant make this work.

View 12 Replies View Related

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

String And Numeric Data Comparison

Apr 19, 2006

I need to make a query to compare equality of two pieces of data, one is a 6 character fixed-length text string (where the first character can be ignored) and the other is a 4 or 5 digit number (long integer, if 4 digit number can be assumed to be 5 with a leading 0).

I do not have the ability to change the way I receive the data, they are on linked tables of different customers.

I tried using various SQL functions to convert the data in the query but they don't seem to work in Access, so I am getting "Type incorrect in the expression" errors.

How do I do this in ACCESS? I would prefer a SQL query solution to a VBA solution if possible, the data is updated VERY often and the application is already doing too much processing on the form_timer() event.

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

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

General :: String Manipulation On A Table - Text Is Gibberish In Group By Query

Jun 22, 2015

While processing string manipulation on a table (140K records, 200-250 MB) the file has corrupted, and I lost all vba Modules, but the data and other DB objects seemed to be in tact.

I now have a query with a text field, when I make a simple join SELECT, the text comes in fine (and, of course, seems fine when presented in table), but when Group by - I get Gibarish: "CARVEDILOL 6.25MG, TABLETS"

Is presented in Group by as: "砅5"

I have recreated the file, importing queries, including this one, but then recreated it as a totally new query, but get that same results.

File Corruption? anything to do about it?

View 12 Replies View Related

Extract Text

Sep 13, 2006

Hi,

I am currently working on a report that i need to extract part of the data from the memoranda field. i have a problem to extract the details that i need.

Example as follow :

Field Name = Memo
Details in Field = ?********~SPGEO1398789159173897 *********?

what i have to do is to extract the data starting from "SPGEO1398789159173897". Also the length of the number can be varied.

i have tried to use the instr, Trim, LTrim but still couldn't get what i wanted.

Can someone here please give me a hand on this

Much appreciate for your help

Ronny

View 7 Replies View Related

Extract Text Between Parentheses

Sep 12, 2007

I have an expression below that I am trying to use to extract text from within parentheses in a field, but it is leaving the last ")" on the extracted text. Can anyone help me determine what I need to do to just return the text? Thanks.

The length of the field is 100.

Mid([SIDE_DRUG_DESC],InStr(1,[SIDE_DRUG_DESC],"(",1)+1,Len([SIDE_DRUG_DESC])-(InStr(1,[SIDE_DRUG_DESC],"(",1)+1))

View 3 Replies View Related

Extract Part Of A Text

Aug 24, 2006

Hello,

I have a table with a field named SSN. This field contains data with the following format:

123-45-6789L

As you can see my field has a text format and I need to create a make table query that will extract all the character that are places in position 12 and 13 (simply the letters after the ssn).

I have tried with Mid([SSN],12,13) but nothing happens.

Any help? Thanks.

View 5 Replies View Related

Extract Year From Text Field

Dec 7, 2006

I have the following data in the field MA-84

I created a query and added the following

Year: "20" & Right([TYPEFY],2)

when I run the query I get a new field named Year and the format looks like

2084

Now I just need a IIF statement that looks at 2084 and compares to to current year is the current year is less than 2084 leave alone else make it 1984

One last question if the Field TYPEFY IS BLANK how do I make the query do nothing to Newly created query field Year?

View 11 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 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

Search, Text VS Numeric.

Jun 21, 2005

I have made my Search Form that will search through my tables, some how I have a syntax error for the following: year, Section and ID, these fields are not text I use numeric values so I think the search is not working for that reason, I am not sure how can I fix it though do I have do add val() or sth.


Private Sub Command8_Click()

On Error GoTo Command8_ClickError

Dim db As Database
Dim rs As Recordset
Dim rs2 As Recordset
Dim strWhereCondition As String
Dim strSql As String

strWhereCondition = ""
strSql = "Select distinct Id From AcademicVideo Where True "

'================================'

If Not IsNull(ID) And Trim(ID) <> "" Then
strSql = strSql & " And [Id] = " & [ID]
End If

'================================'

If Not IsNull(Course) And Trim(Course) <> "" Then
If InStr(Course, "*") = 0 Then
strSql = strSql & " And [Course] = '" & [Course] & "'"
Else
strSql = strSql & " And [Course] like '" & [Course] & "'"
End If
End If

'================================'

If Not IsNull([Format]) And Trim([Format]) <> "" Then
If InStr([Format], "*") = 0 Then
strSql = strSql & " And [Format] = '" & [Format] & "'"
Else
strSql = strSql & " And [Format] like '" & [Format] & "'"
End If
End If

'================================'

If Not IsNull([Title]) And Trim([Title]) <> "" Then
If InStr([Title], "*") = 0 Then
strSql = strSql & " And [Title] = '" & [Title] & "'"
Else
strSql = strSql & " And [Title] like '" & [Title] & "'"
End If
End If

'================================'

If Not IsNull([Lecturer]) And Trim([Lecturer]) <> "" Then
If InStr([Lecturer], "*") = 0 Then
strSql = strSql & " And [Lecturer] = '" & [Lecturer] & "'"
Else
strSql = strSql & " And [Lecturer] like '" & [Lecturer] & "'"
End If
End If

'================================'

' If Not IsNull([Section]) And Trim([Section]) <> "" Then
' If InStr([Section], "*") = 0 Then
' strSql = strSql & " And [Section] = " & [Section] & "'"
' Else
' strSql = strSql & " And [Section] like " & [Section] & "'"
' End If
' End If

'================================'

If Not IsNull([Semester]) And Trim([Semester]) <> "" Then
If InStr([Semester], "*") = 0 Then
strSql = strSql & " And [Semester] = '" & [Semester] & "'"
Else
strSql = strSql & " And [Semester] like '" & [Semester] & "'"
End If
End If

'================================'

If Not IsNull([Year]) And Trim([Year]) <> "" Then
If InStr([Year], "*") = 0 Then
strSql = strSql & " And [Year] = " & [Year] & "'"
Else
strSql = strSql & " And [Year] like " & [Year] & "'"
End If
End If

'================================'


If Not IsNull([Description]) And Trim([Description]) <> "" Then
If InStr([Description], "*") = 0 Then
strSql = strSql & " And [Description] = '" & [Description] & "'"
Else
strSql = strSql & " And [Description] like '" & [Description] & "'"
End If
End If

'================================'


Set db = CurrentDb()
Set rs = CurrentDb.OpenRecordset(strSql, dbOpenSnapshot)
' If (rs2.RecordCount = Null) Then

' MsgBox "Enter A valid Record"

If (rs.RecordCount = 0) Then
MsgBox "Could Not found "

Else
strWhereCondition = "[Id] In (" & rs!ID
Do While Not rs.EOF
strWhereCondition = strWhereCondition & ", " & rs!ID
rs.MoveNext
Loop
strWhereCondition = strWhereCondition & ")"
End If
rs.Close

If strWhereCondition <> "" Then
DoCmd.OpenForm "ACVideo", acNormal, , strWhereCondition
DoCmd.Close acForm, "Search AcVideo"
End If

Exit Sub

Command8_ClickError:
MsgBox Err.Number & " " & Err.Description

Resume Next
End Sub

View 2 Replies View Related

Numeric To Text In Excel Help

Mar 4, 2008

Hello there,

In excel spreadsheet, when I eg type in a numeric column 10 1500 so on, I would column11 appear text such as one thounsand and five hundred. How can I convert this?

Would that be possible?

Thank you in advance

Cheers

View 1 Replies View Related

Convert Numeric Value To Text

Mar 11, 2008

Im trying to construct a query which returns a field that has 21 different types of values.

Is there a way to assign a text value to each numeric value when the results are displayed?

Eg: 21 = "Local Sales", 22 = "National Sales", 23 = "International Sales".. and so on.

Ive looked at the Choose and IIF functions but I cant seem to work it out

..............

Anouther little problem, which should be basic. I want to exclude certain records with values such as 31 and 41 and 42.

When i type <> 31 or <> 41 <> 42 in the criteria they are all still displayed. Although when I type one of those criteria by themselves they ARE EXCLUDED. Is there something dumb I am missing here??

thanks in advance.

View 6 Replies View Related

Extract Regular Expression From Text Field

Sep 5, 2007

I have a table with a text field (named Description), which may or may not contain an "ID Number", which is of the form [CHSV]#[A-Z][A-Z][A-Z]## - NB this is not necessarily at the beginning or the end, and will vary between records. For e.g.

"something C4CLE01"
"something H5STT02 Something else"

What I would like to do in a query is to extract just the ID number part and put that in a new field e.g.

"something C4CLE01" => "C4CLE01"
"something H5STT02 Something else" => "H5STT02"

So far I have considering LIKE, which is no good because it doesnt tell you where in the string the regular expression is, and INSTR doesnt work with regular expressions AFAIK.

Added bonus difficulty - I use the database as an external source for an Excel pivot table. I created a function (which I named JOBID) in VBA to do the above job easily enough i.e. I added a field "JOBID([Description])", but when I try to use the data in said pivot table, it says "Undefined function JOBID in expression". I have tried copying the function to the Excel workbook, but that doesn't work.

Im using Office 2003 if that makes any difference. Thanks in advance.

View 5 Replies View Related

Queries :: Return / Extract Just Text From Field

Aug 5, 2013

I have a field which contains "01501 PRIKLJ. VRHNIKA - LJ / Stevno mesto" .

1.) I would like to extract just text part from that field, how? *number is always on beginning but different 1-10000

2.) Optional, how could I delete in this new only text field part of text after / ...*some rows have some dont /

View 1 Replies View Related







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