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))
I'm trying to use literal parentheses within a text calculation, like: Emergency Medicine ([doctors.doctor name]).
But they just won't show. I've tried to use quotation marks around all literal text, like: "Emergency Medicine ("[doctors.doctor name]")" to no avail.
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
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.
The following query works, I'm just wanting to add a date parameter (below)...When I add the data parameter it seems to ignore that criteria.
I just want to add this:
Event_Date Between [Enter Start Date mm/dd/yyyy] And [Enter End Date mm/dd/yyyy]
WHERE (((tblContacts_Speakers.Contact_Fname)=[FORMS]![frmFirstLast_Name]![txtFname]) AND ((tblContacts_Speakers.Contact_Lname)=[FORMS]![frmFirstLast_Name]![txtLname])) OR (((tblContacts_Speakers_1.Contact_Fname)=[FORMS]![frmFirstLast_Name]![txtFname]) AND ((tblContacts_Speakers_1.Contact_Lname)=[FORMS]![frmFirstLast_Name]![txtLname])) OR (((tblContacts_Speakers_2.Contact_Fname)=[FORMS]![frmFirstLast_Name]![txtFname]) AND ((tblContacts_Speakers_2.Contact_Lname)=[FORMS]![frmFirstLast_Name]![txtLname])) OR (((tblContacts_Speakers_3.Contact_Fname)=[FORMS]![frmFirstLast_Name]![txtFname]) AND ((tblContacts_Speakers_3.Contact_Lname)=[FORMS]![frmFirstLast_Name]![txtLname])) OR (((tblContacts_Speakers_4.Contact_Fname)=[FORMS]![frmFirstLast_Name]![txtFname]) AND ((tblContacts_Speakers_4.Contact_Lname)=[FORMS]![frmFirstLast_Name]![txtLname])) OR (((tblContacts_Speakers_5.Contact_Fname)=[FORMS]![frmFirstLast_Name]![txtFname]) AND ((tblContacts_Speakers_5.Contact_Lname)=[FORMS]![frmFirstLast_Name]![txtLname])) OR (((tblContacts_Speakers_6.Contact_Fname)=[FORMS]![frmFirstLast_Name]![txtFname]) AND ((tblContacts_Speakers_6.Contact_Lname)=[FORMS]![frmFirstLast_Name]![txtLname])) OR (((tblContacts_Speakers_7.Contact_Fname)=[FORMS]![frmFirstLast_Name]![txtFname]) AND ((tblContacts_Speakers_7.Contact_Lname)=[FORMS]![frmFirstLast_Name]![txtLname])) OR (((tblContacts_Speakers_8.Contact_Fname)=[FORMS]![frmFirstLast_Name]![txtFname]) AND ((tblContacts_Speakers_8.Contact_Lname)=[FORMS]![frmFirstLast_Name]![txtLname])) OR (((tblContacts_Speakers_9.Contact_Fname)=[FORMS]![frmFirstLast_Name]![txtFname]) AND ((tblContacts_Speakers_9.Contact_Lname)=[FORMS]![frmFirstLast_Name]![txtLname]));
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?
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
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
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.
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 /
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.
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.
"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 ".
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
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.
I am very new to access less than 1 week since i started trying to build an horse racing database, i am trying to link data from 2 different sources via the horses name however one source displays this with the horses country of origin in parenthesis foe example FRANKEL(GB) and the other source displays the name as just FRANKEL, to be honest i haven't yet tried the link but guess it will fail.
I am therefore looking to get rid of the parentheses and their contents from an access table field and create another field without them.
In Excel i use the formula B1:
=TRIM(REPLACE(A1,FIND("(",A1&"("),FIND(")",A1&")")-(FIND("(",A1&"(")-1),"")) and that works fine.
I guessed the solution for Access would be newname:
TRIM(REPLACE([frhorse.NAME],FIND("(",[frhorse.NAME]&"("),FIND(")",[frhorse.NAME]&")")-(FIND("(",[frhorse.NAME]&"(")-1),""))
But this doesn't work as in returns undefined function "FIND" in expression error.
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
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.
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.
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
OK, so i have finally got round to re-building my client's database as suggested several times by RV.
I have three main tables that I need to extract data from - tblMembers, tblModules, tblCompleteModules.
Basically its a database containing the training details of Scout Association leaders. Each Leader needs to complete 26 modules over a period of time.
tblCompleteModules contains the data for the modules that have been completed, MemberID, ModuleID Date Completed and CompletedID.
I know how I can extraxt the modules that members have completed, but how can i extract the data for the modules that have not been completed. For example I need to report on the number of people who haven't completed module number 20 for example.
Any ideas?
Hi,
If I have a date that is formatted like this 01/01/2006, however do I write a query that shows the date as follows Jan 2006.
Thanks
Regards
i have some problem regarding this.--
I hve two table- 1.Ordbook
2.Ownmas
in Table Ordbook there is two field ORD_NO and Location
in Table Ownmas There is also Two field Ord_no and Sch_val;
but i enter the order no in table Ordbook normally.
suppose 7825,5855
but i enter the order no in table Ownmas as 7825/5855
so when im going to join these two tables by linking ORD_No and Ord_no. i find there is no match. bt my requirement is to match these fields by ANY no and want to retrieve the sch_val from Ownmas .
Hi,
I have a table which gets data from an external text file. I imported this into an Access table (version 2003).
I have a field (PersonName) in the table (Employee_Credits) which has data in the following format.
FirstName LastName (EmployeeID)
It is always in the same format. Using an SQL Query in MS Access, how can I extract the contents within the parenthesis and insert into a column titled “EmployeeID”
Any ideas?
Thanks.
I have a word doc from which I want to import data in a Database.
The word doc has a table, something like this :
Projectnumber 10700004
LSname LSTK number one
POnumber 1170-LST-04
Contactname Mr. Karel van Straten
Vendorname Mothercompany name S.A.
Manufacturer Operating Company name
Otherdata Others
Mancity 46100 Hamburg
Mancountry Germany
Manorder 31-0111-009
Mancontact Mrs. Angela Duval
Mantel 0049 2323 209 151
Manmail email@testing.de
At this stage I copy the table data and paste it into a memo field in a Dbase Query and try to manipulate the text in that query to get some of the data segregated , i.e Projectnumber : 10700004 , Mancountry : Germany , etc.
However, this doesn’t work as I want.
e.g. I’m trying to get the Manmail text thru this qry :
Manmail: Mid([Memofield],Len([Memofield])-InStr([Memofield]," ")) , but the output is not consistent.
Would there be a better way to get the original ( doc-table) data in the same way in my database.
Note :
I could use an additional step : copy/paste to Excel and then import to Access, but that’s something I would like to avoid.
Any suggestion will be appreciated.
Cheers, Ron
I think I am on the right track, but I am stuck with importing the date from my excel filename. Currently the following code imports all Excel files from the directory into a table in my database. The excel files are saved like "ABC_BNG_GTR_04012008.XLS" The numbers represent the date. I need for the date to be extracted and place in table into the "callDate" field. When I run my code, I continue to get a runtime error 13 - type mismatch. The code stops at the Mid() statement. What am I doing incorrectly? Thanks in advance.
Option Compare Database
Private Sub btnImport_Click()
Dim strFile As String 'Filename
Dim strFileList() As String 'File Array
Dim intFile As Integer 'File Number
Dim filename As String
Dim path As String
Dim TheDate As Date
DoCmd.SetWarnings False
path = "C:UsersChinaboyDesktopData"
'Loop through the folder & build file list
strFile = Dir(path & "*.xls")
While strFile <> ""
'add files to the list
intFile = intFile + 1
ReDim Preserve strFileList(1 To intFile)
strFileList(intFile) = strFile
strFile = Dir()
Wend
'see if any files were found
If intFile = 0 Then
MsgBox "No files found"
Exit Sub
End If
'cycle through the list of files
For intFile = 1 To UBound(strFileList)
filename = path & strFileList(intFile)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "sheet2", filename, False
'Inserts date in date column based on file name
TheDate = Mid(strFile, 12, 8)
CurrentDb.Execute "UPDATE sheet2 SET callDate =" & "'" & TheDate & "' where callDate is null"
Next intFile
DoCmd.SetWarnings True
End Sub