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 Replies


ADVERTISEMENT

General :: Extract Part Of A Field From A Table

May 13, 2015

I want to take the first 15 characters from a field in my table and place those 15 characters into a new field in the same table , assuming i can do this with a query.

View 3 Replies View Related

General :: Extract Part Of Postcode Field

Feb 12, 2015

I have a table with about 29,000 postcode values

I want to extraxct the first part of the field (usually 3 or 4 characters)

How can I automate this?

View 2 Replies View Related

Can You Make Just One Part Of The Text In A Text Box Bold Or A Different Font Size?

Aug 20, 2004

Is there a way to make just part of the text in a text box bold, or to use different font sizes in the same text box?

Something like this:

---------------------------------
Heading in bold: description in regular (not bold)
(a smaller height line used as a line space)
Another "normal" line
a larger height line
a normal line....all in one text box!
---------------------------------

One thing I'd like to be able to do is to specify the line height of a blank line in a text box. I'm using carriage returns created with: Chr(13) & Chr(10). I could see defining the font size of a hidden character, but I'd need to know how to assign a font size to a piece of the text in a font box.

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

Contains Part Of Text Formula

Dec 12, 2005

Hi.

My name is Amanda, and recently I’ve started using Access again for work. I haven’t touched the program in about five years, so I am rusty. In addition, it turns out that I am the only one in my department who’s ever even opened Access. I’ve tried hunting down a manual around here, and it seems to be an ever ending scavenger hunt. I also have not been able to find the answer to my question via web, FAQ, and experimentation, although, I feel it’s a fairly basic question.

So here it goes. I want to return all fields that contain part of a text. For example, I want all fields that contain the word “lux”, regardless of what else the field contains to be returned. So if a field contains “lux. cars” it will be returned in my query.

I’ve tried goggling “Microsoft Access” with “text formulas,” however, all I receive are excel formulas.

Thank you.

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

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

Modules & VBA :: Extract Email Address From Text

Nov 4, 2014

I have a field which contains various text including an email address which i need to extract.

My research says that I need to use a regex code but dont know how to get that into Access.

I have attached an example of the file i'm importing into Access.

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

Modules & VBA :: Extract Specific Data From A Text File

Dec 4, 2013

I want to extract specific data from a text file and place it in my table.The following is an example of the data:

Display Author="All Saints" Title="On & On" Genre="Pop" Color="7693971" Tag="2"

I want to extract the following:

Where the word Author= appears to extract the data between the double quotes, so in the above case I want to extract "All Saints" excluding the double quotes.And then where Title= appears extract "On & On", again excluding the double quotes.So I would end up with data in my table looking something like this

strArtist [Author] strSong [Title] strGenre [Genre] strYear [Year]
All Saints On & On Pop
America Venture Highway Rock 1972
Amillionsons Misty Blue Pop 2002

my table is name tblMusicList, the fields are:

strArtist
strSong
strGenre
strYear

View 4 Replies View Related

Queries :: Extract Email Address From A Large Text File

Feb 18, 2014

I am trying to find a way to extract an email from a large text file that is an output from our email system. I would like to be able to extract the email address using a query or collection of queries. I have been able to extract all of the text that contains the @ symbol. From their I created a query expression:

Mid([field1],InStrRev([field1]," ")) that captures some but not everything I need.

View 6 Replies View Related

Fill 'empy' Part Of Text Control With Stars*****

Jan 25, 2005

I would like the remaining part of my text control to show stars
e.g control value is 12
it shows *************12
control value is 3567890
it shows *********3567890

My text control does not have a fixed width, this is set in code depending on various factors
Thanks

View 1 Replies View Related

Selecting Records Based On Part Of A Text Field

Oct 12, 2006

I'm working on a table which has a country field, but this field may contain a text string consisting of more than one country, eg "France, Belgium, Spain"

I want to run a query against the table to select records for any one country, but not sure how to do this.

Any suggestions please?

View 4 Replies View Related

Creating Relationship Based On Part Of A Text Field

Jan 27, 2014

Each each record in table1 has a unique four character (alpha-numeric) code to identify it. The first two numbers of this code represent the group it is in. (Ie. 15AB and 1502 are both grouped together) The second table stores values that apply to the entire group. I need to create a relationship between these two tables based on the first two characters in the ID field.

Things I've tried:
* Making a calculated field with left$() formula - Access doesn't allow relationships on calculated fields
* Create a new field for just the first two characters and create a data macro for after update and after insert to update that field with the expression - cannot edit the field the user is on

View 4 Replies View Related

Tricky Question - Form Part Data Entry, Part Not

Dec 16, 2004

I have what I think is a difficult problem to overcome...

I am designing a form to create an invoice. The user will select a workstream and a date range in form frmInvByHrs. Within this I want two sub-forms, one is frmInvByHrsTsht and the other is frmInvByHrsBill. I want the first one to display all the staff and their hours done, and the second one to be in data entry mode where you can enter the hours you want to bill. Each sub-form is based on a separate query.

Is it possible to do this? ie. to have one sub-form in data entry mode, and the other not? It seems to me that the data entry mode is controlled by the MAIN form regardless of the sub-form settings!

If this is not possible, do you know how I can acheive this?

Thanks

S

View 1 Replies View Related

Date Problem - Part Constant - Part Now()

Nov 3, 2006

Hi everyone,

I have refined my query from previous threads to involved a module function. This calculates more acurately no of working days between dates and takes into account a holidays table. (All credit to Arvin Meyer on the module:) )
However because the Leave Year starts at the 1 July and finishes 30 Jun I need to compose the date for any current year Year(Now())

Enclosed scrdmp shows my query design. I can easily get it to work as you see it, but obviously as each year rolls over, the year needs to change.

Have looked at many posts but can't find what I'm looking for. This one will get me over the hurdle.

Many thanks,

View 6 Replies View Related

Extract Data

Dec 6, 2005

Currently I have a remark field and want to just extract the amount. The data input is not in a structured manner therefore I could not use the mid function.

Example :

Remark
CASH PURCHASE $50K.DEBIT 3007484701
SUB $20,000 FIRST STATE DIVIDEND
AMT:$10,900(FS BRIDGE FD)NO DISC

View 3 Replies View Related

How To Extract Only The First Four Characters

Mar 20, 2007

Hi, Everyone,
I have an excel column that has building location and building name in one column, example 1245 Accounting, how can I copy the 1245 into it's own column, please help, i can do it either in access or excel. Please.

View 3 Replies View Related

Extract Substring

Mar 31, 2006

I have a table in which one field badly needs separating into two. Currently, it is laid out as a mixed case text string followed by an upper case text string, e.g. O little town of Bethlehem FOREST GREEN.

I need to move the upper case string (FOREST GREEN) to a new field (and then trim the original field accordingly). Since there is no delimiter between the two parts of the field, I have no idea how to proceed. Can anyone help?

Thanks.

View 2 Replies View Related

Extract Certain Data

Aug 15, 2006

I have a very simple question that I have not find the result or maybe I do not know what to look for!
I have a query containing a field named year and one named sales, what I need is to have a single result of total sales per year. example
year sales
2005 100
2005 100
2006 80
2006 70
2006 60

The result I would like to put then in a form is:
total 2005 200
total 2006 210

All this by simply running a query without indicating any parameters.

Thanks
M

View 6 Replies View Related







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