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