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.
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 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.
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.
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.
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
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.
"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 ".
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
Here's my query.SELECTCheckFieldSheet.[Company Name] IIf([CheckFieldSheet].[Phone Number 1 *]=[tbl_clients].[Phone Number 1 *] Or [CheckFieldSheet].[Phone Number 1 *]=[tbl_clients].[Phone Number 2 *] Or [CheckFieldSheet].[Phone Number 1 *]=[tbl_clients].[Phone Number 3 *],1,(IIf(IsNull([CheckFieldSheet].[Phone Number 1 *]) And IsNull([tbl_clients].[Phone Number 1 *]),1,0))) AS Number1,CheckFieldSheet.[Phone Number 1 *], tbl_clients.[phone number 1 *], IIf([CheckFieldSheet].[Phone Number 2 *]=[tbl_clients].[Phone Number 1 *] Or [CheckFieldSheet].[Phone Number 2 *]=[tbl_clients].[Phone Number 2 *] Or [CheckFieldSheet].[Phone Number 2 *]=[tbl_clients].[Phone Number 3 *],1,(IIf(IsNull([CheckFieldSheet].[Phone Number 2 *]) And IsNull([tbl_clients].[Phone Number 2 *]),1,0))) AS Number2, CheckFieldSheet.[Phone Number 2 *], tbl_clients.[phone number 2 *], IIf([CheckFieldSheet].[Phone Number 3 *]=[tbl_clients].[Phone Number 1 *] Or [CheckFieldSheet].[Phone Number 3 *]=[tbl_clients].[Phone Number 2 *] Or [CheckFieldSheet].[Phone Number 3 *]=[tbl_clients].[Phone Number 3 *],1,(IIf(IsNull([CheckFieldSheet].[Phone Number 3 *]) And IsNull([tbl_clients].[Phone Number 3 *]),1,0))) AS Number3, CheckFieldSheet.[Phone Number 3 *], tbl_clients.[phone number 3 *], IIf([Number1]=1 And [Number2]=1 And [Number3]=1,1,0) AS RejectFROMtbl_clients INNER JOIN CheckFieldSheet ON (tbl_clients.[company name] = CheckFieldSheet.[Company Name]) AND (tbl_clients.[Region Name] = CheckFieldSheet.RegionName) AND (tbl_clients.[Site Name] = CheckFieldSheet.[Site Name]) AND(tbl_clients.[Plot Number/name] = CheckFieldSheet.[Plot Number/Name]) AND(tbl_clients.[Site Code] = CheckFieldSheet.[Site Code])WHERE(((CheckFieldSheet.Returns)="Wrong Number"));Three expressions (Number1, Number2 and Number3), make up 'Reject'If all three expressions are 1, then reject = 1.Any other combinations will make reject = 0.When i view the queery as it is up there, it works fine and shows everything absolutely perfectly. When I want to view all records where 'Reject' = 0, it asks me what the expressions are. (see attachment)I have no idea why this is happening. Any light on why, and how to resolve it would be sweet!Thanks.
I am trying to do some simple table operations. I have a field (Date) containing dates, and an empty field called Day.
I want to extract the day number from the Date field, and write it to the Day field.
I didn't get very far until I ran into trouble when setting my recordset. I get the error "Too few parameters, expected 1". Clicking "Debug", will highlight the code line "Set rs = db.OpenRecordset(sqlString, dbOpenDynaset)".
So far, my code looks as follows:
Code: Private Sub Command16_Click() Dim db As DAO.Database Dim rs As DAO.Recordset Dim sqlString As String Dim dataDay As Byte 'Open connection to current Access database Set db = CurrentDb()
[Code]...
I am not very familiar with the various types of recordset settings. I just want to be able to read data from the Date field, and write data to the Day field.
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 trying to build a database. And, I want a form that pulls in info from different tables. I build this form from scratch only to discover afterwards that this form would not show any records since fields are from more than one table.
So next, I played around and created the form the same way but with info from only table "POInfoDDDT1"; since the fields are filled, the form showed the data nicely. As soon as I include the fields projectName and ProjectType from "CCCInfoT1," form does not display any data. But, those tables are related! The design view of the form has option like "Add existing field" and within that there is a link "show all tables" or "related fields" but if Access does not allow to show all info together, then what is the point of this function? I don't want any subform visible.
So, basically POInfoDDDT1 and CCCInfoT1 will be prefilled which means the top two sections of form will be filled. The user will fill out the info on the bottom, which comes from two separate tables. All project info comes from "CCCInfoT1" and Service info comes from "POInfoUserT2." The user needs to be able to see those top info in order to fill the bottom section. So, when user puts in info, those info would be saved to these two different tables.
How to achieve this structure and functionality of the form?
I am trying to extract data from one field and then put it into another field. I have 2 fields, Code_A and Code_B. I am trying to take the Code * data from Code_A and put it in Code_B. Any help is appreciated.
While this is the gist of what I need - it is much more complex.
I am dealing with adresses.
This inherited database has 2 fields - ZIP, and ADDRESS. I need to extract the data within the ADDRESS field to populate other more detailed named fields.
I would like to ultimately have my fileds to be: STREETNUM, STPREDIR, STREETNAME, STREETSUFX, SUD, SNUM.
Example: (Current ADDRESS data)
512 N Manchester Dr Ste 201
(extract ADDRESS and import into)
STREETNUM = 512 STPREDIR = N STREETNAME = Manchester STREETSUFX = Dr SUD = Ste SNUM = 201
--
Now I have tried several queries to extract the first bit of data 'STREETNUM', using the example on the link I posted above. INSTR, RIGHT, MID, LEFT.
Problem is there are also P.O. Boxes formatted like: PO BOX 1234, so my STREETNUM column includes the PO, when it should include the 1234.
PO Box 1234 should translate to:
STREETNUM = 1234 STREETNAME = PO BOX
All the rest of the fields aren't needed.
As you can see, I'm in OVER my head.
I would greatly appreciate any assistance or direction in accomplishing this task.
I am not a beginner with Access, but I am far from being an expert. A breakdown of some complicated tasks may be necessary... :-)
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.
Hi folks - I saw a thread last week re how to extract the year from the date field possibly using date serial but can't find it can anyone help - it is probably very simple but i can't get it to work.
Hi folks - I saw a thread last week re how to extract the year from the date field possibly using date serial but can't find it can anyone help - it is probably very simple but i can't get it to work.
for example how can I extract the following number from the field ITEM 117145 5410076462162, 714774 117072 5410076462223 ,714768 117153 5410076462285,733289 998214 5410076462193 from this record. The field is memo. The common: the string "+" always is in front I tried this Expr2: InStr([ITEM],"+"), but it only give me first one.
The following is record BARCODE Retail Line Code NSL Code Prod Ean Name Size Facings Capacity Pos Comments Offer Description Offer Type Offer No 5ELKAHQ*agiaji+ 714766 117145 5410076462162 NS PRNGLS SUR CRM AND ON 190G 3F 15 BUY ONE GET ONE FREE MV 105574 5ELKAHQ*agiagh+ 714774 117072 5410076462223 PRNGLS ORIG 190G 3F 15 BUY ONE GET ONE FREE MV 105574 5ELKAHQ*agiddd+ 714768 117153 5410076462285 PRNGLS SLT/VNGR 190G 3F 15 BUY ONE GET ONE FREE MV 105574 5ELKAHQ*agibia+ 733289 998214 5410076462193 PRNGLS BBQ 190G 3F 15 BUY ONE GET ONE FREE MV 105574
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.
